How To Count Associated Entities Without Fetching Them In Entity Framework

Question

I've been wondering about this one for a while now, so I thought it would be worth using my first Stack Overflow post to ask about it.

Imagine I have a discussion with an associated list of messages:

DiscussionCategory discussionCategory = _repository.GetDiscussionCategory(id); 

discussionCategory.Discussions is a list of Discussion entities which is not currently loaded.

What I want is to be able to iterate through the discussions in a discussionCategory and say how many messages are in each discussion without fetching the message data.

When I have tried this before I have had to load the Discussions and the Messages so that I could do something like this:

discussionCategory.Discussions.Attach(Model.Discussions.CreateSourceQuery().Include("Messages").AsEnumerable());  foreach(Discussion discussion in discussionCategory.Discussions) {  int messageCount = discussion.Messages.Count;  Console.WriteLine(messageCount);  } 

This seems rather inefficient to me as I am fetching potentially hundreds of message bodies from the database and holding them in memory when all I wish to do is count their number for presentational purposes.

I have seen some questions which touch on this subject but they did not seem to address it directly.

Thanks in advance for any thoughts you may have on this subject.

Update - Some more code as requested:

public ActionResult Details(int id)     {           Project project = _repository.GetProject(id);         return View(project);     } 

Then in the view (just to test it out):

Model.Discussions.Load(); var items = from d in Model.Discussions select new { Id = d.Id, Name = d.Name, MessageCount = d.Messages.Count() };  foreach (var item in items) { //etc 

I hope that makes my problem a bit clearer. Let me know if you need any more code details.

Answer 1

Easy; just project onto a POCO (or anonymous) type:

var q = from d in Model.Discussions         select new DiscussionPresentation         {             Subject = d.Subject,             MessageCount = d.Messages.Count(),         }; 

When you look at the generated SQL, you'll see that the Count() is done by the DB server.

Note that this works in both EF 1 and EF 4.