c# - Entity Framework Optimize Count Child Entities -

i trying optimize query generated entity framework linq sql query. below massively simplified version of query.

c#

list<bool> isused = context.tparent.select(parent =>      parent.tchild.any() ).tolist(); 

this produces following sql

generated sql

select      case when (( exists (select          1 [c1]         [dbo].[tchild] [extent2]         [extent1].[id] = [extent2].[parentid]     ))     ) cast(1 bit) else cast(0 bit) end [c1]     [dbo].[tparent] [extent1] 

unfortunately, performs poorly (my real query checks count on many linked tables) , if rewrite query follows speed increased.

optimized query

select case when (     count(tchild.id) > 0 ) 1 else 0 end tparent     left join tchild on tparent.id = tchild.parentid group tparent.id 

how can re-write c# generate optimized query using linq sql query?

well, following linq entities query produces same sql optimized query. it's 1 one sql linq translation, imo not intuitive way of describing query goal. anyway, here is:

var query =     parent in context.tparent     child in parent.tchild.defaultifempty()     group child parent.id g     select g.sum(child => child != null ? 1 : 0) > 0 ? true : false;