4

Here's a simple LINQ query:

var objs = db.Objects.Where(o => o.Field1 == val);

This translates to SQL query:

select * from [Object] where Field1 = @p1

Trouble is, the value of val can also legitimately be null. And SQL doesn't like comparing nulls; it insists on the syntax ... where Field1 is null.

Is there any way of doing this neatly, short of using a ?? / isnull operation?

Shaul Behr
  • 36,951
  • 69
  • 249
  • 387

2 Answers2

4

This, again, is an EF weakness in LINQ support. Good old LINQ to SQL translated this properly depending on the runtime value of val.

I suggest you go with this:

var objs = db.Objects.Where(
   o => (o.Field1 == val) || (o.Field1 == null && val == null));

If EF translates this litterally, the SQL Server query optimizer will actually pick up this pattern and optimize it to an "equals-with-nulls" check. You can even seek indexes using this code pattern, it just works. In the query plans this shows up as IS in contrast to EQ.

usr
  • 168,620
  • 35
  • 240
  • 369
  • 1
    +1 Thanks for the tip. I think `(val == null ? o.Field1 == null : o.Field1 == val)` will compile to neater SQL, won't it? – Shaul Behr Jan 27 '13 at 15:02
  • @Shaul I think that would compile to a `CASE` which I do not trust the optimizer with. One can check the plan, but I came to standardize on the pattern that I posted. In T-SQL at least it is the best one can get. – usr Jan 27 '13 at 15:05
  • Yeah, this is a really nasty deficiency in EF. IIRC there are a few more such nasty bits, such as that the sum of an empty sequence is null, rather than 0. While the queries look like C# due to linq, you unfortunately cannot assume the semantics are. – Eamon Nerbonne Jan 27 '13 at 23:53
  • @ShaulBehr - In my case it translates into: 'WHERE (([t0].[Field1] = @p1) OR ([t0].[Field1] IS NULL))'' – NicVerAZ Sep 21 '16 at 21:21
  • @NicVerAZ what EF version is this? They added optimizations in recent versions. I have no recent EF experience and this answer is 3.5 years old. – usr Sep 22 '16 at 07:19
  • My version is a bit old, indeed. – NicVerAZ Sep 22 '16 at 16:47
0

How about .HasValue?

var objs = db.Objects.Where(o => !o.Field1.HasValue && o.Field1 == val);
spajce
  • 7,044
  • 5
  • 29
  • 44
  • Check your answer, I think there must be a typo... `o.HasValue` could not possibly work. – Shaul Behr Jan 27 '13 at 16:17
  • OK, that will compile now, but it doesn't do what I want - if `val` is null and `o.Field1` is null, it should satisfy the condition. – Shaul Behr Jan 27 '13 at 17:35
  • @Shaul sorry for a long wait reply, i tested in `EF5` the `.Where(o => o.Field1 == null);` and everything is fine, what `EF` you are using?, by the way, i already modified my answer – spajce Jan 27 '13 at 19:19
  • @Shaul, and then i tested in EF4.0 everything is fine again and i have used the MS-SQL2012 Express. – spajce – spajce Jan 27 '13 at 20:05
  • Don't worry about it, the question is already answered. Thanks anyway for the effort. – Shaul Behr Jan 28 '13 at 07:52