为什么LINQ生成的SQL包含多个“IS NULL"?同一列的条件

2022-01-05 00:00:00 sql-server entity-framework-core linq linq-to-sql

以下针对 SQL Server 2012 数据库的查询，使用 Entity Framework Core 3.1:

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 });

生成此 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)))

我不认为我做错了什么，我相当确定查询优化器会消除第二个(d0.microscope_id IS NULL)，但它似乎仍然是 LINQ 代码中的错误.

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 已定义:

    public string MicroscopeId { get; set; }

推荐答案

Field MicroscopeId 声明为可为空.因此，当 null != "0" 为 true 但在 SQL null <> 中时，要模仿 LINQ to Objects 的行为.'0' 为假，EF Core 生成额外的 OR 条件.

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.

要禁用此生成，您必须在构建 DbContextOptions 时指定:

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

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

附加信息:https://docs.microsoft.com/en-us/ef/core/querying/null-comparisons#using-relational-null-semantics

相关文章