Oleg Sh Oleg Sh - 3 months ago 8
C# Question

LINQ - can't create a request

Hello I have the following structure:

Table Countries:
- CountryID
- CountryName

Table Records:
- RecordID
- RecordName
- CountryID


This LINQ returns list of all countries "as is":

(from i in db.Countries
select new SelectListItem()
{
Text = i.CountryName,
Value = i.CountryID.ToString()
}).ToList();


I want to sort this list of countries such way: The most popular countries in "Records" table to the top list. How to do it?

Answer

You can try something like this

(from i in db.Countries 
 join r in db.Records on i.CountryID equals r.CountryID into rds
 orderby rds.Count() descending
 select new SelectListItem() 
 { 
     Text = i.CountryName, 
     Value = i.CountryID.ToString() 
 }).ToList();