LINQ to Entities Does Not Recognize 'Boolean Method Contains [Decimal]

Question

I'm new to LINQ, so I'm pretty confused here. I have a database and am trying to run the following code.

IQueryable<decimal> location_ids = (from m in _db.Admins
                                    where m.UserId.Equals("c5d3dc0e-81e6-4d6b-a9c3-faa802e10b7d")
                                    select m.LocationId);
if (!location_ids.Contains(new Decimal(conf.umisteni.Budova.ID)))

In the if statement, I get an error that I don’t understand and don’t know how to solve it:

System.NotSupportedException: LINQ to Entities does not recognize the method 'Boolean Contains[Decimal](System.Linq.IQueryable`1[System.Decimal], System.Decimal)' method, and this method cannot be translated into a store expression.
  at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.DefaultTranslator.Translate(ExpressionConverter parent, MethodCallExpression call)

Any ideas?

Answer 1

Using Linq-to-Objects IEnumerable will allow you to use Contains (Decimal) as a result of the query.

IEnumerable<decimal> location_ids = (from m in _db.Admins
                                    where m.UserId.Equals("c5d3dc0e-81e6-4d6b-a9c3-faa802e10b7d")
                                    select m.LocationId);

However, why not just expand the where clause:

IEnumerable<decimal> location_ids = (from m in _db.Admins
                                    where m.UserId.Equals("c5d3dc0e-81e6-4d6b-a9c3-faa802e10b7d") && (m.LocationId == conf.umisteni.Budova.ID)
                                    select m.LocationId);
if (!location_ids.Any())

Answer 2

Linq 2 sql cannot translate the ids.Contains () method to sql. You can do the following:

if(!location_ids.ToList().Contains(new Decimal(conf.umisteni.Budova.ID)))

This will invoke the sql query, put it in objects and save locally.

conf.umisteni.Budova.Id Where ( , ), .any

if((from m in _db.Admins
    where m.UserId.Equals(conf.umisteni.Budova.ID.ToString())
    select m.LocationId).Any())

, .

Answer 3

, .Contains() Linq to Entities

public static class LinqToEntitiesUtil
{
    /// <summary>
    /// Extension method that enables .Contains(obj) like functionality for Linq to Entities.
    /// 
    /// Source: http://www.velocityreviews.com/forums/t645784-linq-where-clause.html
    /// </summary>
    /// <typeparam name="TElement">The element being evaluated by the Where clause</typeparam>
    /// <typeparam name="TValue">The value to match</typeparam>
    /// <param name="valueSelector">Lamda for selecting matching values</param>
    /// <param name="values">IEnumerable of the values</param>
    /// <returns>Expression consumable by Linq to Entities that reflects semantics of .Contains(value)</returns>
    /// <remarks>
    /// Usage:
    /// 
    /// Replace expression like 
    /// 
    /// where ChildrenIDs.Contains(items.CategoryID)
    /// 
    /// with
    /// 
    /// .Where((BuildContainsExpression<Item, int>(item => item.CategoryID, ChildrenIDs))
    /// 
    /// NOTE: If the item collection is large, the SQL query will be as well.
    /// </remarks>
    static public Expression<Func<TElement, bool>> BuildContainsExpression<TElement, TValue>(Expression<Func<TElement, TValue>> valueSelector, IEnumerable<TValue> values)
    {
        if (null == valueSelector)
        {
            throw new ArgumentNullException("valueSelector");
        }
        if (null == values) { throw new ArgumentNullException("values"); }

        ParameterExpression p = valueSelector.Parameters.Single();
        if (!values.Any())
        {
            return e => false;
        }

        var equals = values.Select(value => (Expression)Expression.Equal(valueSelector.Body, Expression.Constant(value, typeof(TValue))));
        var body = equals.Aggregate<Expression>((accumulate, equal) => Expression.Or(accumulate, equal));
        return Expression.Lambda<Func<TElement, bool>>(body, p);
    }
}

Answer 4

I just got something like that. Your version of System.Data.dll may not be the same. The method was supported on my dev machine, but then I got runtime errors when I deployed to the test machine. The version of System.Data.dll was older. Good solutions are backward compatible methods. But I still want to track the discrepancy, some kind of patch should be applied, but not in other environments. Who knows what other problems this will cause along the way if they are not stopped.