CODEWITHSUNDEEP
asp.net-mvclinq
sjohn285
sjohn285

Reputation: 405

Using LINQ in an MVC project, how can I orderby a joined table's value?

I have two classes that I join together:

Item

(a bunch of properties)

(Foreign Key) int Category

Category

(Primary Key) int ID (this is referenced in Item)

string Name

Using LINQ in my MVC 5 project, I need to get and sort by the Name value from the category table using the ID in the Item table. With the code below, it is sorting in the order of the integer (1,2,3,4) instead of by the Name property in the Category table. How can I sort by the category table?

Controller code:

var items = from s in db.Items 
            join c in db.Categories on s.Category equals c.ID
            select s;

switch (sortOrder)
        {
            case "Category_Desc":
                //This is where it is selecting the integer ID instead of the string Name
                items = items.OrderByDescending(s => s.Category);
                break;
            default:
                items = items.OrderBy(s => s.Category);
                break;
        }

Upvotes: 1

Views: 519

Answers (2)

akerra
akerra

Reputation: 1200

The Category property of your Item class is an int, so it is going to order those as it would any other integer. Right now your join serves no purpose since you are selecting only the items at the end of your query. Select an anonymous object instead which contains both the category and item, then you will have access to the properties of your Category class and can sort by the name and select only the Item.

var query= from s in db.Items 
        join c in db.Categories on s.Category equals c.ID
        select new { item = s, cat = c };
switch (sortOrder)
{
    case "Category_Desc":
        //This is where it is selecting the integer ID instead of the string Name
        items = items.OrderByDescending(s => s.cat.Name);
        break;
    default:
        items = items.OrderBy(s => s.cat.Name);
        break;
}
var items = query.Select(s => s.item);

Upvotes: 2

Anu Viswan
Anu Viswan

Reputation: 18155

You can try following

You could try following.

var result = db.Items
            .Join(db.Categories,i=> i.Category, c=>c.Id,(i,c)=>new {item =i, category=c})
            .OrderBy(x=>x.category.Name).Select(x=>x.item);

The crux is creating an interim structure, which comprises of Category.Name.

Upvotes: 1

Related Questions