LINQ to Entities does not recognize the method 'Boolean Contains[Decimal]

Question

Hi, I am new to LINQ, so I am pretty confused here. I have a database and try to run 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)))

On the if statement I get an error I don't understand, nor do I 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? Thanks in advance

Answer 1

Using Linq-to-Objects IEnumerable will let you use Contains(Decimal) on the 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 could do the following:

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

This will trigger the sql query, put them in objects and do the contains locally.

Another solution would be to put the conf.umisteni.Budova.Id in the Where clauses (with an equals, not a contains) and then add .any

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

This would only be a good idea if you don't need any of the keys afterwards off course.