Flash_Back Flash_Back - 1 year ago 90
C# Question

Sort linq query by static dictionary value

For my application I would like to sort my

entities by a static dictionary value depending on current
No matter what I try I just can not make it work, always getting an error saying:

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

Here is my repository method trying to sort the

private IOrderedQueryable<Item> Sort(IQueryable<Item> items)
return items.OrderBy(i => i.Type)
.ThenBy(i =>
Item.MyDic.Keys.Any(key => key == i.Type)
? Item.MyDic[i.Type].Item2
: -1);

And the

public partial class Item : CavalenaEntity, ICavalenaEntity
public enum ItemTypeE
Type1 = 0,
Type2 = 1,

public static Dictionary<ItemTypeE, Tuple<int, int>> MyDic = new Dictionary<ItemTypeE, Tuple<int, int>>()
{ ItemTypeE.Type1, new Tuple<int, int>(0, 1) },
{ ItemTypeE.Type2, new Tuple<int, int>(1, 100) },

Item.MyDic.Keys.Any(key => key == i.Type)
is working fine but I could not find a way to achieve something like
I tried selecting
into a new anonymous object, also tried using
but I still can not make it work.

If anyone could show me a way of achieving such a query I would be really grateful.
Thanks a lot.

Answer Source

The problem is that the provider is trying to translate the query into SQL and it is failing to do that because you're using a local in-memory dictionary in your sorting logic. The solution for that is to drag the data and sort it in-memory. Since there is no filtering here, this should be fine from the performance side:

private IEnumerable<Item> Sort(IQueryable<Item> items)
    return items.AsEnumerable().OrderBy(i => i.Type)
                                .ThenBy(i => Item.MyDic.Keys.Any(key => key == i.Type)
                                            ? Item.MyDic[i.Type].Item2: -1);

Note for other cases: avoid using AsEnumerable() when there is a filtering logic after it, because that way you'll not be filtering on the server-side.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download