How To Count Associated Entities Without Fetching Them In Entity Framework

Question

Hello everyone,

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

I don't have a direct answer but can only point you to the following comparison between NHibernate and EF 4.0 which seems to suggest that even in EF 4.0 there is no out of the box support for getting counts of a related entity collection without retrieving the collection.

http://ayende.com/Blog/archive/2010/01/05/nhibernate-vs.-entity-framework-4.0.aspx

I've upvoted and starred your question. Hopefully someone will chime in with a workable solution.

Answer 2

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.