Nested selects in LINQ expression, how to?

Question

I don't know how to work with nested selects in LINQ. How could I convert this SQl expression to LINQ?

Select i.ID, i.Impression, 
(Select COUNT(ImpressionsId) 
    from DiaryImpressions 
    where DiaryPostsId = '2' AND ImpressionsId = i.ID) as Num from Impressions i

things such as this made me look for better solutions. Joining in linq just makes you cry... You may consider looking at this simple but wonderful tool (dapper) http://code.google.com/p/dapper-dot-net/ — John Sobolewski, Dec 02 '11 at 20:26
I believe this can also be rewritten as a JOIN, with an equivalent RA model. The plans might be a little different though. — , Dec 02 '11 at 20:30

score 2 · Answer 1 · answered Dec 02 '11 at 20:27

Seriously? DiaryPostsId is a string? Oh well...

from i in context.Impressions
select new {
    i.ID,
    i.Impressions,
    Num = (from d in context.DiaryImpressions
           where d.DiaryPostsId == "2"
           && d.ImpressionsId == i.ID
           select d).Count()
}

score 0 · Accepted Answer · answered Dec 02 '11 at 20:26

from ...
select new { 
    i.Id, 
    i.Impression, 
    Count = context.DiaryImpressions.Count(d => d.DiaryPostsId == 2 && d.ImpressionsId == i.Id)
 }

If you map your objects properly, you can use child relations directly:

Count = i.DiaryImpressions.Count(d => d.DiaryPostsId == 2)

Nested selects in LINQ expression, how to?

2 Answers2