Why does LINQ generated SQL include multiple "IS NULL" conditions for the same column

Question

The following query against a SQL Server 2012 database, using Entity Framework Core 3.1:

        var tows = await _context.DataEntryTow
            .Where(t => _context.DataEntrySample
                        .Any(s => s.TowId==t.TowId && (s.MicroscopeId != "0" || s.MicroscopeId == null)))
            .Select (t => new { text = t.TowId, value = t.TowId });

generates this SQL:

SELECT d.tow_id AS text
FROM data_entry_tow AS d
WHERE EXISTS (
    SELECT 1
    FROM data_entry_sample AS d0
    WHERE (d0.tow_id = d.tow_id) AND (((d0.microscope_id <> '0') OR (d0.microscope_id IS NULL)) OR (d0.microscope_id IS NULL)))

I don't think I've done anything wrong, and I'm fairly sure the query optimizer will eliminate the second (d0.microscope_id IS NULL), but it still seems like an error in the LINQ code.

MicroscopeId is defined:

    public string MicroscopeId { get; set; }

user_10646316 · Answer

Field MicroscopeId declared as nullable. So, to mimic LINQ to Objects behaviour when null != "0" is true but in SQL null <> '0' is false, EF Core generates additional OR condition.

To disable this geneeration you have to specify that when building DbContextOptions:

optionsBuilder.UseSqlServer(constr, b => b.UseRelationalNulls(true) );

Additional info: https://docs.microsoft.com/en-us/ef/core/querying/null-comparisons#using-relational-null-semantics

Why does LINQ generated SQL include multiple "IS NULL" conditions for the same column

1 Answers