Sort sql data using joined dictionary

Question

In SQL Server 2014 I have a table People with a column Country (int not null). I want to sort rows by Country, but country is a int, I want to sort by country name. I don't have Contries table in db.

var countryDict = new Dictionary<int, string>();
countryDict.Add((int)ECountry.AT, "Austria");
countryDict.Add((int)ECountry.IT, "Włochy");
.......

IQueryable<PeopleModel> result = _entity.People.Where(...)

Now I want to join my DbSet with dictionary with country id and country name

// there exception occurs
var resultWithCountryName = result
    .Join(
         countryDict,
         p => (int)p.Country,
         c => c.Key,
         (p, c) => new { p, CountryName = c.Value })
    .ToList();

result = resultWithCountryName
    .OrderByDescending(p => p.Kraj)
    .Select(p => p.p)
    .AsQueryable();

After all I need pagination so I use this code for it.

        var resultList = result
            .Include(p => p.OtherTable) // nevermind
            .Skip(searchCriteria.Offset)
            .Take(searchCriteria.RowsOnPage)
            .ToList();

When I execute join statement I get exception

Unable to create a constant value of type 'System.Collections.Generic.KeyValuePair`2[[System.Int32, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089],[System.String, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]]'. Only primitive types or enumeration types are supported in this context.

My question is

why do I get this error?
Maybe my solution of this problem is wrong?

Basically EF doesn't know how to turn a KeyValuePair into SQL code. You can use AsEnumerable before the Join, but that will run the SQL for result and the Join will be done in memory instead of on the DB. — juharr
Add a Countries table to your database and establish a foreign key relationship between your People table and the new Countries table - simplest and most reliable solution by far .... — marc_s

Ravindra Kumar Challagandla Ravindra Kumar Challagandla · Accepted Answer · 2015-08-25T14:16:50

Probably give .ToList()/AsEnumerable()/AsQueryable() to the end of People query.

IQueryable<PeopleModel> result = _entity.People.Where(...).ToList();

Sort sql data using joined dictionary

1 Answers