EF查询确实会生成大量查询[重复]

Question

我有以下数据模型：

AuthorizationObject [1 : n] Scopes

我想执行以下选择：

    var result = await _context.AuthorizationObject
        .Select(x => new
        {
            x.RoleId,
            x.DefinitionId,
            Scopes = x.Scopes.Select(s => s.Name).ToList()
        })
        .ToListAsync();

因为我必须考虑性能，所以我想在一个选择中执行它，比如（伪）：

select a.RoleId a.DefinitionId s.Name from AuthorizationObject As a
inner join Scope As s ...

不幸的是，它执行了这样的操作：

SELECT [x].[RoleId], [x].[DefinitionId], [x].[Id]
FROM [AuthorizationObject] AS [x]

SELECT [s].[ScopeDefinitionId]
FROM [Scope] AS [s]
WHERE @_outer_Id = [s].[AuthorizationObjectId]

SELECT [s].[ScopeDefinitionId]
FROM [Scope] AS [s]
WHERE @_outer_Id = [s].[AuthorizationObjectId]

SELECT [s].[ScopeDefinitionId]
FROM [Scope] AS [s]
WHERE @_outer_Id = [s].[AuthorizationObjectId]

SELECT [s].[ScopeDefinitionId]
FROM [Scope] AS [s]
WHERE @_outer_Id = [s].[AuthorizationObjectId]

SELECT [s].[ScopeDefinitionId]
FROM [Scope] AS [s]
WHERE @_outer_Id = [s].[AuthorizationObjectId]

...

更新：SamiAl90建议的解决方案：此查询

    var result = await _context.AuthorizationObject
        .Include(x => x.Scopes)
        .ToListAsync();

产生以下sql：

SELECT [x].[Id], [x].[DefinitionId], [x].[Metadata], [x].[RoleId]
FROM [AuthorizationObject] AS [x]
ORDER BY [x].[Id]

SELECT [x.Scopes].[AuthorizationObjectId], [x.Scopes].[ScopeDefinitionId], [x.Scopes].[ValueString]
FROM [Scope] AS [x.Scopes]
INNER JOIN (
    SELECT [x0].[Id]
    FROM [AuthorizationObject] AS [x0]
) AS [t] ON [x.Scopes].[AuthorizationObjectId] = [t].[Id]
ORDER BY [t].[Id]

Answer 1

使用包括。

var result = await context.Set<AuthorizationObject>()
   .AsQueryable()
   .Include(a => a.Scopes)
   .ToListAsync();

这将创建一个具有内部连接但作为子选择的查询。不过还有一个问题。

现在，如果你真的需要一个伪对象，你仍然可以创建一个，但在我看来，这是不必要的。

编辑：添加了一个示例项目，证明这不会导致两个查询。

这就是创建的查询

SELECT [p.Children].[Id], [p.Children].[ParentId]
FROM [ChildClass] AS [p.Children]
INNER JOIN (
    SELECT [p0].[Id]
    FROM [ParentClass] AS [p0]
) AS [t] ON [p.Children].[ParentId] = [t].[Id]
ORDER BY [t].[Id]