Use Take to including entity

I have two tables: categories and articles. One category can have many articles. I want to display overview of new articles separated by categories. But I want to display 开发者_高级运维only first five articles per each category. How should I write query to solve this?

My first idea was something like this:

var cat = from c in ctx.categories
          where c.IsPublic == true
          select c;

But this contains all articles and I'm not sure how to write something like c.articles.Take(5) to query. Is it possible?

Oh, and it is ASP.NET MVC 2 with Entity Framework.

---

This worked for me. Important here is to switch off lazy loading to avoid that all articles are loaded when you iterate through the collection:

ctx.ContextOptions.LazyLoadingEnabled = false; // assuming you are in EF 4.0

var query = ctx.categories
    .Where(c => c.IsPublic)
    .Select(c => new { 
        Category = c, 
        Articles = c.Articles.Take(5)
    }).ToList().Select(x => x.Category);

foreach (var category in query)
{
    foreach (var article in category.Articles)
    {
        // runs only 5 times through this loop
    }
}

Edit

If you don't dispose ctx and work with the context more after the code snippet above you probably better reenable lazy loading again ...

ctx.ContextOptions.LazyLoadingEnabled = true;

... before you get into trouble with other queries if your application mostly relies on lazy loading.

---

ctx.categories
    .Where(c => c.IsPublic)
    .Select(c => new 
    { 
        Category = c, 
        Articles = c.Articles.OrderBy(a => a.Whatever).Take(5)
    ).Select(c => c.Category);

Supposing that your class name is Category with an EntityCollection called Articles, this should work.