为什么EF生成带有不必要的空检查的SQL查询？ [英] Why is EF generating SQL queries with unnecessary null-checks?

问题描述

我在搜索字符串字段时遇到了EF创建可怕查询的问题。它以惰性程序员的方式生成了一个查询，其中包含空检查，这将强制扫描整个索引。

I came across an issue with EF creating terrible queries when searching on a string field. Its produced a query in the style of lazy programmers to encompass null checking which forces the whole index to be scanned.

请考虑以下查询。

1. 查询1

1. Query 1

var p1 = "x";
var r1 = ctx.Set<E>().FirstOrDefault(
                        subject =>
                            p1.Equals(subject.StringField));

查询2

Query 2

const string p2 = "x";
var r2 = ctx.Set<E>().FirstOrDefault(
                        subject =>
                            p2.Equals(subject.StringField));

查询1产生

WHERE (('x' = [Extent2].[StringField]) OR (('x' IS NULL) AND ([Extent2].[StringField] IS NULL))) 

并在4秒内执行

查询2产生

WHERE (N'x' = [Extent2].[StringField]) 

并在2毫秒内执行

有人知道周围有什么工作吗？ （没有参数不能为const，因为它是用户输入所输入的，但不能为null。）

Does anyone know of any work arounds? (no the parameter cant be a const as it is entered by user input but cannot be null.)

N.B在进行概要分析时，两个查询均由EF使用sp_executesql进行准备；因此，如果只是执行它们，查询优化器将否定OR'x'IS NULL检查。

N.B When profiled, both queries are prepared with sp_executesql by EF; as of-cause if they were just executed the query optimiser would negate the OR 'x' IS NULL check.

推荐答案

设置 UseDatabaseNullSemantics = true ;

• 当 UseDatabaseNullSemantics == true ，（operand1 ==操作数2）将被翻译为：

WHERE operand1 = operand2

当 UseDatabaseNullSemantics == false ，（operand1 ==操作数2）时将被翻译为：

WHERE
    (
        (operand1 = operand2)
        AND
        (NOT (operand1 IS NULL OR operand2 IS NULL))
    )
    OR
    (
        (operand1 IS NULL)
        AND
        (operand2 IS NULL)
    )

这由Microsoft记录：

获取或设置一个指示在比较两个操作数（两个操作数都可能为空）时是否显示数据库空语义的值。默认值为false。

Gets or sets a value indicating whether database null semantics are exhibited when comparing two operands, both of which are potentially nullable. The default value is false.

您可以在 DbContext 子类中进行设置。构造函数，如下所示：

You can set it in your DbContext subclass constructor, like so:

public class MyContext : DbContext
{
    public MyContext()
    {
        this.Configuration.UseDatabaseNullSemantics = true;
    }
}

或者您也可以将此设置设置为<$从我的角度来看，像下面的代码示例一样，从外部c $ c> dbContext 实例（请参阅@GertArnold注释），此方法会更好，因为它不会更改默认的数据库行为或配置）：

Or you can also set this setting to your dbContext instance from the outside like the code example below, from my point of view (see @GertArnold comment), this apporach will be better, because it will not change the default database behaviour or configuration):

myDbContext.Configuration.UseDatabaseNullSemantics = true;

这篇关于为什么EF生成带有不必要的空检查的SQL查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！