Linq to Entities with Subselect in select part

We are starting with a MVC project using EF. We will need write a lot of queries in LINQ using subselect and don't have figured out yet how this could be done.

The most simple case of these is in this form:

select p.Id, 
       p.Title,
       (select count(*) 
          from Comments c
         where c.PostId = p.Id
       ) as CommentCount
  from Post p
 where p.UserId = 'John';

Reading the "101 page" of examples from Microsoft and Stack Overflow I couldn't find a example like this. I开发者_开发知识库 found examples using join and group, but in some cases the are already a group in the query.

Can you help me with this query, please? Thanks.

You will need a Navigation property called Comments on Post (EF automatically creates it if you have foreign keys specified) then you can use query as under.

from p in Context.Posts
where p.UserId == "John"
select new 
{
  Id = p.Id,
  Title = p.Title,
  Count = p.Comments.Count()
}