CODEWITHSUNDEEP
asp.net-mvclinq
Panos
Panos

Reputation: 637

Linq nested select query on self join table

I'm working on an .Net MVC3 project. I have the following code which runs 2 queries on the same table. The second query is depended on the results of the first. How can i rewrite this to use a single query (with nested query) and assign the results to my viewmodel?

public ViewResult Category(string id)
{
    var viewModel = new ProductCategoryNavigation();

    viewModel.category = db.Category.Single(c=>c.NavigationId==id);
    viewModel.subCategories = db.Category.Where(i => i.ParentId == category.Id);

    return View(viewModel);
}

Upvotes: 2

Views: 2165

Answers (3)

Sergey Berezovskiy
Sergey Berezovskiy

Reputation: 236208

var query = (from c in db.Category.Where(x => x.NavigationId == id)
             join sc in db.Category on c.Id equals sc.ParentId into g
             select new {
                 Category = c,
                 SubCategories = g
             }).Single();


viewModel.category = query.Category;
viewModel.subCategories = query.SubCategories;

Upvotes: 3

Ankit
Ankit

Reputation: 1887

You can try something like this I think. 

viewModel.subCategories = from c in db.categories
                          join c1 in db.categories on c.Id = c1.ParentId 
                          where c.NavigationId = id
                          select c1

Upvotes: 0

AliRıza Adıyahşi
AliRıza Adıyahşi

Reputation: 15866

If there is relation mapping Category-to-Category, you can use something like this.

viewModel.subCategories = db.Category.Single(c=>c.NavigationId==id).Category;

And if there is a relation you see the 2 different realtion like this:

Category.Category1 > return Category (parent)
Category.Category2 > return Collection of Category (children)

Upvotes: 1

Related Questions