Linq to Entity and Dictionary

[daveoggy]

I have the following code that compiles but throws an error on execution:

Code: Select all

            Dictionary> Lookup = new Dictionary>();

            //Used in my commented out Linq Query
            List Status = new List() { 0, 1, 2, 4 };

            Lookup.Add(200, new List() { 0, 1, 2 });
            Lookup.Add(330, new List() { 0, 1, 4 });

            using (Entities ent = new Entities())
            {

                try
                {
                    //var result = from d in ent.TM_FNET
                    //             where Lookup.Keys.Contains(d.ACTION.Value)
                    //             && Status.Contains(d.STATUS.Value)
                    //             select d;

                    var result = from d in ent.TM_FNET
                                 where Lookup.Keys.Contains(d.ACTION.Value)
                                 && Lookup[d.ACTION.Value].Contains(d.STATUS.Value)
                                 select d;

                    foreach (var f in result)
                    {
                        Console.WriteLine("{0} -- {1} -- {2}", f.ACTION, f.ID, f.STATUS);
                    }

                    Console.WriteLine("Finished");
                    Console.ReadKey();
                }
                catch
                {

                }
            }

The linq query that is commented out works perfectly but the active one fails with the following error:

LINQ to Entities does not recognize the method 'System.Collections.Generic.List`1[System.Decimal] get_Item(System.Decimal)' method, and this method cannot be translated into a store expression.

What I'm expecting is linq to translate the query into something like the following:

Code: Select all

SELECT
        *
FROM
        tm_fnet
WHERE
        (ACTION = 200 AND STATUS IN (0,1,2))
        OR
        (ACTION = 330 AND STATUS IN (0,1,4));

Is this a bug, a limitation, or my idiocy?

Thanks!

[AndreyR]

I have reproduced the situation.
Thank you for your help, I will post here about the results of our investigation.

[AndreyR]

Getting value by key from Dictionary does not have a corresponding translation to SQL in Entity Framework, this scenario is not supported.

[daveoggy]

Thanks AndreyR I can accept that.

What I'm doing doesn't seem to be too unusual a situation though. How would you suggest I get the entity frame work to handle this situation?

[AndreyR]

Here is the solution I came up with. Noting unusal, but rather clumsy:

Code: Select all

        var temp = from d in entities.TM_FNET
              where Lookup.Keys.Contains(d.ACTION.Value)
              select d;

        ParameterExpression tm_fnet = Expression.Parameter(typeof(TM_FNET), "tm_fnet");
        MemberExpression action = Expression.PropertyOrField(tm_fnet, "ACTION");
        MemberExpression status = Expression.PropertyOrField(tm_fnet, "STATUS");
        BinaryExpression or = null;
        BinaryExpression innerContains = null;

        foreach (decimal tempKey in Lookup.Keys) {
          ConstantExpression tempKeyAsConstant = Expression.Constant(tempKey);
          BinaryExpression actionInLookupKeys = Expression.Equal(action, tempKeyAsConstant);
          List list = Lookup[tempKey];

          innerContains = null;
          
          foreach (decimal element in list) {
            ConstantExpression elementAsConstant = Expression.Constant(element.ToString());
            if (innerContains == null)
              innerContains = Expression.Equal(status, elementAsConstant);
            else
              innerContains = Expression.Or(innerContains, Expression.Equal(status, elementAsConstant));
          }
          innerContains = Expression.And(actionInLookupKeys, innerContains);

          if (or == null)
            or = innerContains;
          else
            or = BinaryExpression.Or(or, innerContains);
        }
        temp.Where(Expression.Lambda>(or, tm_fnet));