LINQ在SQL语句中生成额外的IS NULL条件 [英] LINQ is Generating Extra IS NULL Condition in SQL Statement

问题描述

我正在写一些LINQ来基于电子邮件获取记录，但是，生成的SQL包含一个附加的 IS NULL 条件，该条件不需要存在，因为我正在检查参数值在将条件添加到查询之前，在代码中输入null.

I'm writing some LINQ to fetch records based on an email, however, the generated SQL contains an additional IS NULL condition which doesn't need to be there because I am checking the parameter value for null in the code prior to adding the condition to the query.

我的LINQ代码是:

if (email != null)
{
    query = query.Where(r => r.Email == email);
}

由此产生的SQL条件是:

The SQL condition generated from this is:

(([Extent1].[Email] = @p__linq__0) OR (([Extent1].[Email] IS NULL) AND (@p__linq__0 IS NULL)))

The

(([Extent1].[Email] IS NULL) AND (@p__linq__0 IS NULL))

据我所见，

不需要在那里.

doesn't need to be there as far as I can see.

有什么办法让LINQ忽略它?

Is there any way to get LINQ to omit it?

推荐答案

如果 email 为null，它们就在那儿.

They're there in case email is null.

您可以通过设置

获取或设置一个值，该值指示是否为数据库空语义比较两个操作数时显示可为空.默认值为false.例如(operand1 ==操作数2)将翻译为:(operand1 =操作数2)如果UseDatabaseNullSemantics分别为true((((operand1 =操作数2)AND(NOT(operand1 IS NULL或操作数2 IS NULL)))OR(((operand1 ISNULL)AND(operand2 IS NULL)))如果UseDatabaseNullSemantics为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. For example (operand1 == operand2) will be translated as: (operand1 = operand2) if UseDatabaseNullSemantics is true, respectively (((operand1 = operand2) AND (NOT (operand1 IS NULL OR operand2 IS NULL))) OR ((operand1 IS NULL) AND (operand2 IS NULL))) if UseDatabaseNullSemantics is false.

有多种方法可以应用此方法.

There are various ways to apply this.

如果仅要将其应用于单个查询，则可以执行以下操作:

If you only want to apply this to a single query you can do something like this:

using(TheContext dbContext = new TheContext()) {
    dbContext.Configuration.UseDatabaseNullSemantics = true;

    ...

    if (email != null)
    {
        query = query.Where(r => r.Email == email);
    }
}

如果要将其应用于所有查询:

If you want to apply this to all queries:

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

您还可以将属性更改为 [必需] :

You can also change the property to be [Required]:

public class Model {
    [Required]
    public string Email { get; set; }
}

这篇关于LINQ在SQL语句中生成额外的IS NULL条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！