背景:我正在开发一个系统,用于清理内部客户列表并找出联系人的电子邮件地址,其中我们已经拥有该公司其他人的电子邮件地址。为了做到这一点,我有(简化的)3 个表:
Contacts:
ID
CompanyId
Email
Domain
电子邮件域名:
ID
Domain
EmailFormat
EmailFormatConfirmed
我有一个手动例程,即给定公司,找到我下一个联系人,我们有他们的域名但没有他们的电子邮件地址:
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 时:
-- 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 代码(目前这是我必须依靠的),那就不会了。我错过了一些明显的事情吗?删除这个使得查询运行得非常快(正如预期的那样 - 有很多空域在这里有效地交叉连接)