Entity Framework Optimize Count Child Entities

Question

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();

This produces the following SQL

Generated 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.

Optimized query

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

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

Answer 1

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;

Answer 2

this query return all Parents that at least one child:

var result = Context.tChild.Select(child => child.tParent)
    .Distinct().ToList();