实体框架优化子实体计数 [英] Entity Framework Optimize Count Child Entities

问题描述

我正在尝试优化使用实体框架Linq生成的查询以进行SQL查询.下面是我的查询的简化版本.

I am trying to optimize a query generated with Entity Framework Linq to SQL Query. Below is a massively simplified version of my query.

C#

List<bool> isUsed = Context.tParent.Select(parent => 
    parent.tChild.Any()
).ToList();

这将产生以下SQL

生成的SQL

SELECT 
    CASE WHEN (( EXISTS (SELECT 
        1 AS [C1]
        FROM [dbo].[tChild] AS [Extent2]
        WHERE [Extent1].[Id] = [Extent2].[ParentId]
    ))
    ) THEN cast(1 as bit) ELSE cast(0 as bit) END AS [C1]
    FROM [dbo].[tParent] AS [Extent1]

不幸的是，这样做的效果很差(我的真实查询检查了许多链接表的计数)，如果我按照以下方式重写查询，则速度会大大提高.

Unfortunately, this performs poorly (my real query checks the count on many linked tables) and if I rewrite the query as follows the speed is greatly increased.

优化查询

SELECT CASE WHEN (
    COUNT(tChild.Id) > 0
) THEN 1 ELSE 0 END
FROM tParent
    LEFT JOIN tChild ON tParent.Id = tChild.ParentId
GROUP BY tParent.Id

如何使用Linq to SQL查询重新编写C#以生成优化查询?

How can I re-write my C# to generate my optimized query using a Linq to SQL query?

推荐答案

下面的LINQ to Entities查询有效地产生与 Optimized查询相同的SQL.它基本上是从SQL到LINQ的一对一转换，但是IMO描述查询目标的方式不是很直观.无论如何，这里是:

Well, the following LINQ to Entities query produces effectively the same SQL as your Optimized query. It's basically one to one SQL to LINQ translation, but IMO not very intuitive way of describing the query goal. Anyway, here it is:

var query =
    from parent in Context.tParent
    from child in parent.tChild.DefaultIfEmpty()
    group child by parent.Id into g
    select g.Sum(child => child != null ? 1 : 0) > 0 ? true : false;

这篇关于实体框架优化子实体计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！