LINQ - can't create a request

Question

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?

use join http://stackoverflow.com/questions/2767709/c-sharp-joins-where-with-linq-and-lambda — ray, Apr 22 '14 at 11:16
You need a column that quantifies popularity so you can sort by that column. — flipdoubt, Apr 22 '14 at 11:16
flipdoubt, I don't want to have one more column, I want to sort it depends on Records table — Oleg Sh, Apr 22 '14 at 11:24
If you are saying the number of times CountyId appears in the Records table quantifies a country's popularity, you should state that more clearly. The structure provides no reason to make that assumption. — flipdoubt, Apr 22 '14 at 11:58

Harikant · Answer 1 · 2014-04-22T12:04:22.580

2

  var  recordsItem=db.Records.GroupBy(x=> x.CountryID).Select( gr => new{ CID=gr.Key, Count=gr.Count()}).ToList();

  var result= (from c in db.Countries
              join  r in  recordsItem on c.CountryID equals r.CID
              order by r.Count descending
              select  new  SelectListItem() 
               {
                  Text = c.CountryName, 
                  Value =   c.CountryID.ToString()
               }).ToList();

edited Apr 22 '14 at 12:04

answered Apr 22 '14 at 11:29

Harikant

269
1
6

1

Thank you, but: Additional information: Unable to create a constant value of type 'Anonymous type'. Only primitive types or enumeration types are supported in this context. – Oleg Sh Apr 22 '14 at 11:39
On which query you got this error? Please use latest code, I have modified it just now – Harikant Apr 22 '14 at 12:03
The same. On second query – Oleg Sh Apr 22 '14 at 19:26

score 2 · Accepted Answer · answered Apr 22 '14 at 11:43

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();

LINQ - can't create a request

2 Answers2