Linq to Entities count subquery?

Question

I'm trying to do this query in linq to entities (EF4)

select Header.Id, 
 (select count(*) 
  from Detail 
  where Header.Id = Detail.headerId) detailcount
from Header

This won't work because it's not allowed in EF:
(Header and Detail are EntityObjects)

from h in context.Header
select new Header
    {
        Id = h.Id,
        DetailCount = (from d in context.Detail 
                       where d.headerId = p.Id select d).Count()
    }

DetailCount is a new property I added on the Detail Entity (partial class)

The above Linq query doesn't work because I cannot project onto a mapped entity:
The entity cannot be constructed in a LINQ to Entities query

Is there an other way of doing this?

is hearder and deatil has any relation like one to many ? – Pranay Rana Aug 24 '11 at 12:04 — Pranay Rana, Aug 24 '11 at 12:04
yes, one to many (one header, multiple details) – roeland Aug 24 '11 at 12:05 — roeland, Aug 24 '11 at 12:05
@Roeland Ever figure this out? – Giovanni B Aug 20 '12 at 17:05 — Giovanni B, Aug 20 '12 at 17:05

score 3 · Answer 1 · answered Aug 24 '11 at 12:14

3

below will do you task because both are related entities

from h in context.Header
select new Header
    {
        Id = h.Id,
        detailCount = h.Detail.Count()
    }

answered Aug 24 '11 at 12:14

Pranay Rana

175,020
35
237
263

1

This is indeed a shorter version of the linq query, but the problem is that I cannot project onto a mapped entity. – roeland Aug 24 '11 at 12:25

score 0 · Accepted Answer · answered Jan 06 '13 at 14:12

0

I worked around this by using annonymous types.

answered Jan 06 '13 at 14:12

roeland

6,058
7
50
67

Linq to Entities count subquery?

2 Answers2