考虑这样的简单实体:
public class Person
{
public int Id { get; set; }
public string MainEmail { get; set; }
}
public class Person_Email
{
public int Id { get; set; }
public int Id_Person { get; set; }
public string Email { get; set; }
}
这个查询:
db.Person.Where(p => p.Person_Email.Any(c => c.Email == "myemail@email.com") || p.MainEmail == "myemail@email.com").FirstOrDefault();
被翻译成:
SELECT TOP(1) [p].[Id]
FROM [Person] AS [p]
WHERE (EXISTS (
SELECT 1
FROM [Person_Email] AS [p1]
WHERE ([p].[Id] = [p1].[Id_Person]) AND ([p1].[Email] = N'myemail@email.com'))
OR ([p].[MainEmail] = N'myemail@email.com')
)
这个很慢,在我的数据库中分析器显示有4347316次读取,用时为2568秒。
我会把SQL写成:
SELECT TOP(1) [p].[Id]
FROM [Person] AS [p]
join [Person_Email] as pe on p.Id = pe.Id_Person
where p.MainEmail = N'myemail@email.com' or pe.Email = N'myemail@email.com'
在这种情况下,分析器显示读取次数为17448,持续时间为300。
我想知道是否有一种不同的方式来优化编写LINQ查询的方法,或者我们只能等待ef core团队对其进行改进(我尝试过ef core 5.0预览版8,但没有任何改变)。