How check for duplicate records based on nullable properties?

superkuton posted on Thursday, July 16, 2009

I have a Student BO which has LastName, FirstName, MiddleName and SuffixName properties.
SuffixName is nullable.

I am implementing an ExistsCommand to check whether a duplicate name exists.

My problem is, how should I query for the duplicate name?

Thanks.

rsbaker0 replied on Thursday, July 16, 2009

Are you just asking how to check for a NULL value of a field in the database?

If you are generating the SQL yourself, you typically have to include the condition IS NULL in your WHERE clause. You can't use "=" (a common oops). Nothing ever "=" NULL, not even NULL.

superkuton replied on Friday, July 17, 2009

I have this query in my DP_Execute in my Exists command:

_exists = (From p In ctx.DataContext.Students Where p.LastName & p.FirstName & p.MiddleName = _namestring).Count > 0

I did not include the check on SuffixName because it is nullable. When the query encounters a null value, of course, the whole query will result to null.

How can I include the check for a SuffixName in my query? I am afraid that if I query first for the value of the SuffixName before I query for the whole name that I will be creating two queries.

How should I query for the duplicate name considering the nullable SuffixName, using only a single query if it is possible?

If there is a better strategy for checking if a duplicate name exists, please tell me.

Thanks.

rsbaker0 replied on Friday, July 17, 2009

Interesting.

I haven't waded into LINQ to SQL yet, but evidently == to an explict null will translate properly:

Null semantics

LINQ to SQL does not impose null comparison semantics on SQL. Comparison operators are syntactically translated to their SQL equivalents. For this reason, the semantics reflect SQL semantics that are defined by server or connection settings. For example, two null values are considered unequal under default SQL Server settings, but you can change the settings to change the semantics. LINQ to SQL does not consider server settings when it translates queries.

A comparison with the literal null is translated to the appropriate SQL version (is null or is not null).

http://msdn.microsoft.com/en-us/library/bb399342.aspx

---------------

So, I would think you could use this to check for your SuffixName in your exists command. With my knowledge of LINQ syntax, the easiest way would be to have two different statements. If SuffixName.HasValue, then include the explicit comparison in the first statement, otherwise have a second statement that includes SuffixName == null.