It just occured to me that when I go to create a new SQL query using VS's query builder, it pre-fills in a query based on the datatable I am building the query with. Normally I just delete the standard query (unless it's sufficient) and write my own, but this morning I was creating a new query to delete a row from a table. Nothing all that complex, and nothing new, I've done it many times, but the pre-filled query is so ridiculously (in my opinion) complex that I thought I might be missing something important.

This is the query that it originally offered up this morning:

DELETE FROM AccountLocations
WHERE        (ID = @Original_ID) AND (@IsNull_AccountID = 1 AND AccountID IS NULL OR
             AccountID = @Original_AccountID) AND (@IsNull_LocationKey = 1 AND LocationKey IS NULL OR
             LocationKey = @Original_LocationKey) AND (@IsNull_LocationName = 1 AND LocationName IS NULL OR
             LocationName = @Original_LocationName) AND (@IsNull_FileAs = 1 AND FileAs IS NULL OR
             FileAs = @Original_FileAs) AND (@IsNull_SyncAddresses = 1 AND SyncAddresses IS NULL OR
             SyncAddresses = @Original_SyncAddresses) AND (@IsNull_PhysicalAddress = 1 AND PhysicalAddress IS NULL OR
             PhysicalAddress = @Original_PhysicalAddress) AND (@IsNull_PhysicalCity = 1 AND PhysicalCity IS NULL OR
             PhysicalCity = @Original_PhysicalCity) AND (@IsNull_PhysicalState = 1 AND PhysicalState IS NULL OR
             PhysicalState = @Original_PhysicalState) AND (@IsNull_PhysicalPostalCode = 1 AND PhysicalPostalCode IS NULL OR
             PhysicalPostalCode = @Original_PhysicalPostalCode) AND (@IsNull_PhysicalCountry = 1 AND PhysicalCountry IS NULL OR
             PhysicalCountry = @Original_PhysicalCountry) AND (@IsNull_MailingAddress = 1 AND MailingAddress IS NULL OR
             MailingAddress = @Original_MailingAddress) AND (@IsNull_MailingCity = 1 AND MailingCity IS NULL OR
             MailingCity = @Original_MailingCity) AND (@IsNull_MailingState = 1 AND MailingState IS NULL OR
             MailingState = @Original_MailingState) AND (@IsNull_MailingPostalCode = 1 AND MailingPostalCode IS NULL OR
             MailingPostalCode = @Original_MailingPostalCode) AND (@IsNull_MailingCountry = 1 AND MailingCountry IS NULL OR
             MailingCountry = @Original_MailingCountry) AND (@IsNull_IsHQ = 1 AND IsHQ IS NULL OR
             IsHQ = @Original_IsHQ) AND (@IsNull_Created = 1 AND Created IS NULL OR
             Created = @Original_Created) AND (@IsNull_Updated = 1 AND Updated IS NULL OR
             Updated = @Original_Updated)

I mean I am all for making sure the correct record is removed, but come on! This is ridiculous! 20 lines of SQL to delete one row?

Is it wrong of me to think DELETE FROM AccountLocations WHERE (ID = @DeleteThis) is totally sufficient and requires about 3 brain cells to understand since "ID" is the PK? Am I missing something here?

All you really need in the WHERE clause is the part that makes the record unique (1 record) if you are intending on deleting ONE record.
Everything else is overkill.

Do you have a unique index created on the table?
Does your ORM read the unique index to make the where clause?


Thats what I thought!

Yes the ID column is a identity,PK index for the table. It appears that VS knows this, but I guess it feels the need to make it complex. Here is the property grid for the ID column of that table as it appears in VS:


Oh well, I don't get it. It always seems to do that, I just never paid much attention.