Linq进行效率非常低的实体框架查询

本文关键字：实体 框架 查询 非常低 效率 Linq | 更新日期: 2023-09-27 18:27:41

Entity Framework为以下LINQ查询生成性能非常差的SQL：

var query = _context.Sessions
                    .Where(s => s.OrganizationId == orgId && s.Device != null && s.Device.User != null)
                    .Select(s => s.Device.User)
                    .Distinct();

生成此SQL:

exec sp_executesql N'SELECT
[Distinct1].[Id] AS [Id], 
[Distinct1].[Email] AS [Email], 
[Distinct1].[Sex] AS [Sex], 
[Distinct1].[Age] AS [Age]
FROM ( SELECT DISTINCT 
    [Extent4].[Id] AS [Id], 
    [Extent4].[Email] AS [Email], 
    [Extent4].[Sex] AS [Sex], 
    [Extent4].[Age] AS [Age]
    FROM   (SELECT [Extent1].[OrganizationId] AS [OrganizationId], [Extent3].[UserId] AS [UserId1]
        FROM   [dbo].[Sessions] AS [Extent1]
        INNER JOIN [dbo].[Devices] AS [Extent2] ON [Extent1].[DeviceId] = [Extent2].[Id]
        LEFT OUTER JOIN [dbo].[Devices] AS [Extent3] ON [Extent1].[DeviceId] = [Extent3].[Id]
        WHERE [Extent2].[UserId] IS NOT NULL ) AS [Filter1]
    LEFT OUTER JOIN [dbo].[Users] AS [Extent4] ON [Filter1].[UserId1] = [Extent4].[Id]
    WHERE [Filter1].[OrganizationId] = @p__linq__0
)  AS [Distinct1]',N'@p__linq__0 int',@p__linq__0=2

我实际想要执行的SQL如下，运行速度极快：

select distinct u.*
from Sessions s
inner join Devices d on s.DeviceId = d.Id
inner join Users u on d.UserId = u.Id
where OrganizationId = 2

如何使实体框架生成的SQL尽可能接近此查询？

Linq进行效率非常低的实体框架查询

如果您只想要电子邮件，为什么要选择整个User实体？

试试这个：

var query = _context.Sessions
                    .Where(s => s.OrganizationId == orgId && s.Device != null && s.Device.User != null)
                    .Select(s => s.Device.User.Email)
                    .Distinct();

尝试从users表开始：

var query = (
    from u in _context.Users
    where u.Devices.Any(d => d.Sessions
        .Any(s => s.OrganisationId == orgId)
    )
    select u
);

它不会执行您指定的查询，但它所返回的内容可能具有相同的良好性能。

你可以做得很简单：

_context.Sessions
    .Where(s => s.OrganizationId == 2)
    .Select(s => s.Device.User)
    .Distinct();

您不需要检查null，因为它将为您执行INNER JOIN。

我不喜欢使用DISTINCT，如果查询包含它，那么该查询就是错误的。

其他方法

var query = _context.Sessions.Include("Device.User.Email")
                    .Where(s => s.OrganizationId == orgId);
相关文章：