33

I came across a rather strange problem with linq-to-sql. In the following example,

var survey = (from s in dbContext.crmc_Surveys
                                   where (s.crmc_Retail_Trade_Id == tradeId) && (s.State_.Equals(state))
                                   select s).First();

If tradeId is null, it doesn't behave as if I had specified null specifically like this instead,

var survey = (from s in dbContext.crmc_Surveys
                                   where (s.crmc_Retail_Trade_Id == null) && (s.State_.Equals(state))
                                   select s).First();

Which is my desired behavior. In fact it doesn't return anything unless both values are non-null. I can't figure out how to accomplish this short of several different linq queries. Any ideas?

Improve this question

5 Answers 5

Reset to default
42

Change where (s.crmc_Retail_Trade_Id == tradeId) to 

where (s.crmc_Retail_Trade_Id == tradeId || 
      (tradeId == null && s.crmc_Retail_Trade_Id == null))

Edit - based on this post by Brant Lamborn, it looks like the following would do what you want:

where (object.Equals(s.crmc_Retail_Trade_Id, tradeId))

The Null Semantics (LINQ to SQL) MSDN page links to some interesting info:

LINQ to SQL does not impose C# null or Visual Basic nothing comparison semantics on SQL. Comparison operators are syntactically translated to their SQL equivalents. The semantics reflect SQL semantics as defined by server or connection settings. Two null values are considered unequal under default SQL Server settings (although you can change the settings to change the semantics). Regardless, LINQ to SQL does not consider server settings in query translation.

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

The value of null (nothing) in collation is defined by SQL Server; LINQ to SQL does not change the collation.

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Yes, I suppose that's the obvious answer. But inquiring minds want to know why a constant null is different than a variable pointing to null.
I suspect Joel Coehoorn is correct that the second case in your question gets translated to a sql query specifying null directly.
That seems like a very large flaw or even bug in Microsoft's linq parser to not handle null object references appropriately, but I'll take your words for it.
"Appropriately" is very subjective - I've added some text from MSDN that explains what LINQ does.
I just tried the object.Equals workaround, and it works with nullable integers, but not with strings. (Using LINQ to EF, not LINQ to SQL)
|
4

Another option to solve this, as I ran across this problem as well.

where (tradeId == null ? s.crmc_Retail_Trade_Id == null : s.crmc_Retail_Trade_Id == tradeId)
Improve this answer

Comments

2

Not sure on this one, but I suspect when linq-to-sql translates that to an sql query string you get a slightly different expression specifying null directly such that at some point you end up comparing NULL to itself, and NULL=NULL is defined to be false.

Improve this answer

1 Comment

Unless ANSI_NULLS is set to OFF. But that causes the universe to implode.
0

I am not familiar with Linq, however in general:

NULL represents a missing, unknown, or undefined value. Strictly speaking, a variable cannot equal NULL; low-lvel languages which provide this construct usually do so as a convenience because there is no easy alternative -- at a higher level it's usually better to rely on ISNULL, defined, or whatever features your language supplies.

One undefined variable is not equal to another undefined variable (and the same applies to NULL == NULL). Joe Celko has a good example of writing a query to find all people whose hair colour matches the colour of the vehicle they drive. Should this query match a bald man who walks everywhere?

Improve this answer

5 Comments

I think it's pretty defensible that a man's non-existent hair's color be considered a match to his non-existant car's paint.
In low level, or high level languages, null is the hex value 0x0 and null == null is true. Now I know this isn't true in SQL but C# is not SQL, we expect this to hold up. Linq-to-Sql is supposed to be an abstraction for C# users. If you ask me MS screwed the pooch, am I wrong?
Indeed. I was more trying to explain to the inquiring mind that wanted to know why a constant null was different from a variable pointing to one. In most cases null is equivalent to 0x0, a serious problem if you ever needed a pointer to the start of a memory segment (thankfully that's no longer an issue). I say equivalent because I think there is a subtle difference from equal. It's like saying that 1.999999999... is not equal to 2, it's just a mathematical inconvenience that they happen to be the same value.
I prefer to think they are different. In Perl, 0 is defined. In PHP 0 !== null. In SQL 0 != NULL. In XSLT an attribute set to 0 does not match an undefined attribute. One should keep in mind that even if your language allows it, it's still a test for undefinedness, not equality.
How it translates from C# to SQL I guess depends on whether it was implemented by a C# guy or an SQL guy. But if it's supposed to be an abstraction for C# programmers and it doesn't do what a C# programmer expects, then MS has indeed screwed the pooch. (Mind you, even if it did work as expected, I'd still suspect there was some pooch-screwing going on somewhere.)
0

It's better to make sp for this purpose because linq will perform iteration it takes while for your assistance if you are using linq.

var c = lstQ_Buffer.Where(q => (((semesterId == 0 || semesterId == null ? q.fkSemesterId == null : q.fkSemesterId == semesterId) && (subjectSpecialtyId == 0 || subjectSpecialtyId == null ? q.fkSubSpecialtyId == null : q.fkSubSpecialtyId == subSpecialtyId) && (subTopicId == 0 || subTopicId == null ? q.fkSubTopicId == null : q.fkSubTopicId == subTopicId)) && (q.fkProgramId == programId && q.fkYearId == yearId && q.fkCourse_ModuleId == courseModuleId && q.fkSubject_SpecialtyId == subjectSpecialtyId && q.fkTopicId == topicId && q.fkDifficultyLevelId == diffucultyLevelId))).ToList();
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.