Entity Framework Query Help - Filtered Projection

I am trying to write a query that returns filtered associations along with the master entity and can't seem to find a neat way of doing so.

I have an Article entity and an ArticleResource entity such that one Article can contain many ArticleResources.

I want to query the top 10 recently uploaded articles, and also obtain the top 3 resources for each Article using Entity framework.

One way to do this would be to obtain the top 10 articles and then iterate and obtain the top 3 resources for each article. This approach works but isn't optimum

Can anyone suggest me the right way to do this in Ent开发者_JS百科ity Framework 4.1?

I am not sure below sample code will answer your question

class Program
    {
        static void Main(string[] args)
        {
            Database.SetInitializer<BlogContext>(new DropCreateDatabaseIfModelChanges<BlogContext>());

            BlogContext context = new BlogContext();

            for (int i = 1; i < 13; i++)
            {
                Article article = new Article() { Title = "article_" + i.ToString()};
                for (int j = 1; j < 5; j++)
                {
                    article.ArticleResources.Add( new ArticleResource { Name = "articleresource_" + i.ToString() + "_" + j.ToString() });
                }
                context.Articles.Add(article);
            }

            context.SaveChanges();

            var query = (from a in context.Articles.Include(a => a.ArticleResources)
                         orderby a.Id descending
                         select new ArticleViewModel { ArticleId= a.Id, ArticleTitle = a.Title, ArticleResources= (HashSet<ArticleResource>)a.ArticleResources.OrderByDescending(r=>r.Id).Take(3) })
                        .Take(10);

            Console.WriteLine(string.Format("article_id\tarticle_title\tarticle_resource"));

            foreach (var item in query.ToList())
            {
                Console.WriteLine(string.Format("{0}\t{1}", item.ArticleId, item.ArticleTitle));
                foreach (var ar in item.ArticleResources)
                {
                    Console.WriteLine("\t\t{0}\t{1}", ar.Id, ar.Name);
                }
            }

            Console.Read();
        }
    }

    public class ArticleViewModel
    {
        public int ArticleId { get; set; }
        public string ArticleTitle { get; set; }
        public HashSet<ArticleResource> ArticleResources { get; set; }
    }

    public class BlogContext : DbContext
    {
        public DbSet<Article> Articles { get; set; }
        public DbSet<ArticleResource> ArticleResources { get; set; }
    }

    public class Article
    {
        public Article()
        {
            ArticleResources = new HashSet<ArticleResource>();
        }
        public int Id { get; set; }
        public string Title { get; set; }
        public virtual ICollection<ArticleResource> ArticleResources { get; set; }
    }

    public class ArticleResource
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public int ArticleId { get; set; }
        public virtual Article Article { get; set; }
    }