Linq to entities does not recognize the method system.string get_Item

Question

I am trying to have an orderByExpression to passed to a function which uses it on the dbContext object in the entity framework

> Dictionary<int, string> dict = new Dictionary<int, string>();
> Expression<Func<DbQuestion, string>> orderByExpression = r => dict[r.Id];

This throws the following error.

Exception=Exceptions.DBConnectionException: LINQ to Entities does not recognize the method 'System.String get_Item(Int32)' method, and this method cannot be translated into a store expression.

I understand that I cannot access the [ ] in Linq to Sql queries. Can you please suggest an alternative or point me in the right direction?

Edit:

Few more details about the problem I am trying to solve:

The dict have 15-20 items and keys remain constant and only values change dynamically

I am trying to order the questions based on the range of r.Id i.e if floor(r.Id)==14 then I return "a" if it's between floor(r.Id)==15 then I return "b" and if it's between floor(r.id)==13 I return "c"(as decided by the values in the dict) this helps in ordering the rows

Here's the actual expression:

List<int> cqh;
List<int> iqh;
Expression<Func<DbQuestion, string>> orderByExpression = r => cqh.Contains(r.QuestionID)? dict[(int)Math.Floor(r.SearchKey1)] +"2"+Guid.NewGuid() :  
                    iqh.Contains(r.QuestionID)? dict[(int)Math.Floor(r.SearchKey1)] + "1"+Guid.NewGuid() :  
                    dict[(int)Math.Floor(r.SearchKey1)] + "0"+Guid.NewGuid();

Thanks,

How many items are in the dictionary ? Potentially a lot or just a few ? — Titian Cernicova-Dragomir
Wow .. that is one big expression. I will update my answer, building it all manually is not a good option in this case, I will try to do it with a Visitor. — Titian Cernicova-Dragomir
No you can't call a C# function in SQL, at least not like this, when EF converts the query it will fail — Titian Cernicova-Dragomir

Titian Cernicova-Dragomir Titian Cernicova-Dragomir · Accepted Answer · 2017-12-06T09:31:19

I see two options, that stay with LINQ to Entities

Put the data in the dictionary in a separate table, save it, and then join the table. This solution can apply even if you need to have per query data.

Example:

public class SortKey
{
    [Key]
    public int SortKeyId { get; set; }
    public long SearchId { get; set; }
    public int EntityId { get; set; }
    public string SortId { get; set; }
}

using (var db = new Db())
{
    Dictionary<int, string> dict = new Dictionary<int, string>();
    long searchId = DateTime.Now.Ticks; // Simplfied, either use a guid or a FK to another table
    db.Keys.AddRange(dict.Select(kv => new SortKey { SearchId = searchId, EntityId = kv.Key, SortId = kv.Value }));
    db.SaveChanges();

    var query = from e in db.Entity
                join k in db.Keys.Where(k => k.SearchId == searchId) on (int)e.Id equals k.EntityId
                orderby k.SortId
                select e;
}

// Cleanup the sort key table

Build the condition dynamically. This can be achieved using Expression manipulation

Example:

Expression exp = Expression.Constant(""); //Default order key
var p = Expression.Parameter(typeof(Entity));
foreach (var kv in dict)
{
    exp = Expression.Condition(
        Expression.Equal(
            Expression.Convert(
                Expression.MakeMemberAccess(p, p.Type.GetProperty("Id")), typeof(int)
            ),
            Expression.Constant(kv.Key)
        ),
        Expression.Constant(kv.Value),
        exp
    );
}

var orderByExp = Expression.Lambda<Func<Entity, string>>(exp, p);

var query = db.Entity.OrderBy(orderByExp);

Which option you use depend on the amount of data in the dictionary. The condition built for the OrderBy might get very inefficient for a large amount of data

Edit

Based on the changed question, you can use an expression visitor to replace the dic[...] call with a conditional testing for each value in the dictionary. The advantage of this approach is that you can easily change the expression, the replace will work the same way

The class:

class DictionaryReplaceVisitor : ExpressionVisitor
{
    protected override Expression VisitMethodCall(MethodCallExpression node)
    {
        if(node.Object != null && node.Object.Type == typeof(Dictionary<int, string>) && node.Method.Name == "get_Item")
        {
            Expression exp = Expression.Constant(""); //Default order key
            // Compile the tahrget of the index and execute it to get the value
            // If you know there is a single dictionary you could replace this with a class property intead and set it from the Visit call site, 
            // but this is the more general appraoch
            var dict = Expression.Lambda<Func<Dictionary<int, string>>>(node.Object).Compile()();
            foreach (var kv in dict)
            {
                exp = Expression.Condition(
                    Expression.Equal(
                        node.Arguments.Single(),
                        Expression.Constant(kv.Key)
                    ),
                    Expression.Constant(kv.Value),
                    exp
                );
            }

            return exp;
        }
        return base.VisitMethodCall(node);
    }
}

Usage:

Expression<Func<Entity, string>> orderByExpression = r => cqh.Contains(r.QuestionID) ? dict[(int)Math.Floor(r.SearchKey1)] + "2" + Guid.NewGuid() :
        iqh.Contains(r.QuestionID) ? dict[(int)Math.Floor(r.SearchKey1)] + "1" + Guid.NewGuid() :
        dict[(int)Math.Floor(r.SearchKey1)] + "0" + Guid.NewGuid();
var replace = (Expression<Func<Entity, string>>)new DictionaryReplaceVisitor().Visit(orderByExpression);

var query = db.Entity.OrderBy(replace).ToString();

The resulting SQL will not be pretty but it should work.

Solution 3:

If the amount of data is not very big, you can do a ToList or AsEnumerable on the query and do the ordering in memory (by calling the OrderBy after one of the above methods). It might actually perform better in this case

Linq to entities does not recognize the method system.string get_Item

1 Answers