Translation of Linq queries into SQL fades to zero parameter values

Question

Here is a simple LINQ query:

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

This translates to an SQL query:

 select * from [Object] where Field1 = @p1 

The problem is that val can also be zero. And SQL does not like to compare zeros; he insists on the syntax ... where Field1 is null .

Is there a way to do this carefully if not using the operation ?? / isnull ?

Answer 1

This is again EF's weakness in supporting LINQ. The good old LINQ to SQL correctly translated it 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 explicitly, the SQL Server query optimizer will actually pick up this template and optimize it before checking for "equals-with-nulls". You can even search for indexes using this code template, it just works. In query plans, this is displayed as IS as opposed to EQ .

Answer 2

How about .HasValue ?

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