EF 4.1 code-first: How to select an entity with it's collection-property's count

Question

I have an entity named Tag with a navigation property (collection) named Articles. The Tag has a ignored-property named ArticleCount that used to save tha associated Article s count (just in run-time and to use in views- ignored against the DB). See:

public class Tag{

    public int Id { get; set; }

    public int Title { get; set; }

    public virtual ICollection<Article> Articles { get; set; }

    [NotMapped]
    public int ArticleCount { get; set; }

}

How can I select all Tag s (or one) with it's ArticleCount in ONE query against the dataBase -in entity framework 4.1 code-first, with lambda expressions please?

Answer 1

var tagsAndCounts =  from tag in context.Tags
                     where ...
                     select new { tag, count = tag.Articles.Count() }
var tagsWithCount = tagsAndCounts.ToList()
                                 .Select(x => 
                                         {
                                             x.tag.ArticleCount = x.count;
                                             return x.tag;
                                         };

(I would design this differently - ArticleCount should not be part of the model, or it should be a projection on Articles, which you could eager-load using Include(). But this does what you want)

---

Update: my design.

public class Tag
{
   ...
   public int ArticleCount { get { return Articles.Count; } }
}

var tagsWithEagerLoadedArticles = context.Tags.Include(x => x.Articles)
                                         .Where(...).Etc();

Of course, whether this performs well or not depends on the the expected article count per tag. If it's a few dozen, this will work reasonably, while being cleaner than the other approach. If it's hundreds, the other one is better.

Now, if that's the case, you should use an anonymous or named presentation type instead of reusing the entity.