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

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?

---

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.

---

Example for all tags:

var result = context.Tags
    .Select(t => new
    {
        Tag = t,
        ArticleCount = t.Articles.Count()
    });

foreach (var a in result)
    a.Tag.ArticleCount = a.ArticleCount;

var tags = result.Select(a => a.Tag).ToList();

This is only one query and the copy happens in memory. I believe that there is no other way than copying the ArticleCount from the anonymous result objects into the tags because you cannot project directly into an entity, so you can't use Select(t => new Tag { ... });. You could use another named type instead of the anonymous type but not an entity type.