CODEWITHSUNDEEP
c#linqentity-frameworklinq-to-sql
Auk
Auk

Reputation: 117

Linq To Sql, Complex Select

I'm building a portal(it's my hobby project) and for my index (home) page i need to write specific query.

I want to get categories in the specified order. For each category i want to load N most recent articles that are attached to it. So basically my home page will looks like

Category 1            Category 2
Article title 1       Article title 1
Article title 2       Article title 2
....                  ....
Article title N       Article title N


Category 3            Category 4
Article title 1       Article title 1
Article title 2       Article title 2
....                  ....
Article title N       Article title N

My simplified domain model looks like this:

public class CategoryInfo
{
    public virtual Int64 ParentId? { get; set; }

    public virtual String Name { get; set; }

    public virtual Int32 DisplayOrder { get; set; }

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

public class ArticleInfo
{
    public virtual String Title { get; set; }

    public virtual DateTime Published { get; set; }

    public virtual Int64? CategoryId { get; set; }

    public virtual CategoryInfo { get; set; }
}

Category can have many articles related to it. Article can be uncategorized.

So i attempt to do it this way:

var query = _categoryService.GetCategories().Where(x => !x.ParentId.HasValue) // Getting only top level categories
                                            .OrderBy(x => x.DisplayOrder)     // Sorting
                                            .Select(x => x.Articles.OrderByDesc(a => a.Published).Take(N))

This doesn't work. Any ideas ?

Upvotes: 1

Views: 695

Answers (2)

Sebastian Piu
Sebastian Piu

Reputation: 8008

You can do it by using SelectMany like this:

categories.Where(x => !x.ParentId.HasValue) // Getting only top level categories
            .OrderBy(x => x.DisplayOrder) // Sorting
            .SelectMany(x => x.Articles.OrderByDescending(a => a.Published).Take(10))
            .GroupBy(x => x.Category);

you can iterate it like this:

        foreach (var group in query)
        {
            Console.Write(group.Key); //the category
            //group is an enumerable of articles
        }

Upvotes: 2

Matteo Mosca
Matteo Mosca

Reputation: 7448

I think that the correct approach would be:

var query = _categoryService.GetCategories().Where(x => !x.ParentId.HasValue) // Getting only top level categories
                                            .OrderBy(x => x.DisplayOrder)     // Sorting
                                            .Select(x => new {
Category = x,
Articles = x.Articles.OrderByDesc(a => a.Published).Take(N))
});

The result will be a collection of anonymous objects. Each object will have 1 Category object and a collection of related Articles objects. This can become your display model.

Upvotes: 0

Related Questions