为什么Linq Contains会生成此SQL? [英] Why is Linq Contains generating this SQL?

问题描述

背景:我正在使用一个系统来清理内部客户列表并找出联系人的电子邮件地址，而我们拥有该公司中其他人的电子邮件地址.为此，我(简化了)3张桌子:

Background: I am working on a system to clean up an internal customer list and figure out email addresses of contacts where we have the email address of someone else already at that company. In order to do this I have (simplified) 3 tables:

联系人:

ID
CompanyId
Email
Domain

电子邮件域:

ID
Domain
EmailFormat
EmailFormatConfirmed

我有一个手动程序，说，给定公司，找到我的下一个联系人，我们拥有他们的域名，但没有他们的电子邮件地址:

I have a manual routine which is saying, given the company, find me the next contact where we have their domain name but not their email address:

int companyId = 53;

var emails = Contacts.Where(p => p.companyId == companyId
                                            && p.Email == null
                                            && !string.IsNullOrEmpty(p.Domain)).Select(p => p.Domain);
var domain =
    EmailDomains.FirstOrDefault(
        d => !d.EmailFormatConfirmed 
            && !string.IsNullOrEmpty(d.Domain)
            && emails.Contains(d.Domain));

此查询运行非常缓慢，并检查了生成的Sql:

This query runs very slowly and on checking out the Sql generated:

    -- Region Parameters
DECLARE @p__linq__0 Int = 53
-- EndRegion
SELECT TOP (1) 
    [Extent1].[Id] AS [Id], 
    [Extent1].[Domain] AS [Domain], 
    [Extent1].[EmailFormat] AS [EmailFormat], 
    [Extent1].[EmailFormatConfirmed] AS [EmailFormatConfirmed], 
    FROM [dbo].[EmailDomain] AS [Extent1]
    WHERE ([Extent1].[EmailFormatConfirmed] <> 1) 
        AND ( NOT (([Extent1].[Domain] IS NULL) OR ((LEN([Extent1].[Domain])) = 0))) 
        AND ( EXISTS (
            SELECT 1 AS [C1]
            FROM [dbo].[Contacts] AS [Extent2]
            WHERE ([Extent2].[CompanyId] = @p__linq__0) 
                AND ([Extent2].[Email] IS NULL) 
                    AND ( NOT (([Extent2].[Domain] IS NULL) OR ((LEN([Extent2].[Domain])) = 0))) 
                    AND (([Extent2].[Domain] = [Extent1].[Domain]) OR (([Extent2].[Domain] IS NULL) AND ([Extent1].[Domain] IS NULL)))
    ))

我可以看到，存在子句的末尾有问题的部分是OR (([Extent2].[Domain] IS NULL) AND ([Extent1].[Domain] IS NULL)).为什么会出现在这里?我不明白它的有效性，如果我手动编码Sql(目前这是我必须回退的内容)，那不是事实.我是否缺少明显的东西?删除此选项可使查询运行非常快(可以预料-这里有许多空域有效地交叉连接)

I can see that the offending part is OR (([Extent2].[Domain] IS NULL) AND ([Extent1].[Domain] IS NULL)) at the end of the exists clause. Why would this even be there? I can't understand how it is valid, and if I were hand coding the Sql (which currently is what I am going to have to fallback on) It wouldn't be. Am I missing something obvious? Removing this makes the query run very fast (as would be expected - there are a lot of null domains which effectively are being cross joined here)

推荐答案

在EF6中，此行为由DbContextConfiguration UseDatabaseNullSemantics 属性(默认情况下为 false ):

In EF6 this behavior is controlled by the DbContextConfiguration UseDatabaseNullSemantics property (by default false):

获取或设置一个值，该值指示在比较两个操作数(两个操作数都可能为空)时是否显示数据库空语义.默认值为false.例如(operand1 ==操作数2)将分别翻译为:(operand1 =操作数2)如果UseDatabaseNullSemantics分别为true((((operand1 =操作数2)AND(否(operand1 IS NULL或操作数2 IS NULL))))OR(((operand1 IS NULL)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.

因此只需将其转到true即可解决问题.

So just turn it to true and the problem will be solved.

您可以在DbContext构造函数中完成此操作:

You can do that inside your DbContext constructor:

this.Configuration.UseDatabaseNullSemantics = true;

或仅针对特定上下文实例执行命令之前:

or just for a specific context instances before executing commands:

var db = new YourDbContext();
db.Configuration.UseDatabaseNullSemantics = true;
var emails = db.Contacts.Where(...)
...

但是请注意，如果您在查询中比较可为空的字段并且不包含显式的null检查，则打开该选项可能会导致某些意外结果，因此请谨慎使用.

But please note that turning the option on might cause some unexpected results if you compare nullable fields in the queries and don't include explicit null checks, so use it with care.

这篇关于为什么Linq Contains会生成此SQL?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！