LINQ to Entities does not recognize the method 

问题:

Hi I am using a linq query which is throwing the error LINQ to Entities does not recognize the method 'System.String ToString(Int32)' method, and this method cannot be translated into a store expression.

        List<string> resultMap = (from item in mapResult
                                  select Convert.ToString(item.ResultDE)).ToList();

Error is throwing in this below statement

        List<Result_DE> resultList = (from result in db.Result_DE
                                      where result.IsActive == "1"
                                      && resultMap.Contains(Convert.ToString(Convert.ToInt32(result.ID)))
                                      select result).ToList();

please tell me the proper way of writing this query.

回答1:

You cannot use these conversion functions in a LINQ to Entities statement, they cannot be translated to SQL, you need to do the conversions in memory. But I don't think you need to do that at all.

If you were just using the resultMap to get your resultList, filtered by Results of which the Id is present in mapResult, do the following:

var resultList = db.Result_DE
    .Where(r => r.IsActive == "1" && mapResult.Any(mr => mr.ResultDE == r.ID));
    .ToList();

If mapResult is an in-memory collection, instead of an IQueryable that is attached to the db context, you need to do the following:

var resultIds = mapResult.Select(mr => mr.ResultDE).ToList();
var resultList = db.Result_DE
    .Where(r => r.IsActive == "1" && resultIds.Contains(r.ID));
    .ToList();

回答2:

Before you call any method (e.g. ToString()), you need to convert LINQ to Object using AsEnumerable().

回答3:

if your item.ResultDE and result.ID is variable type of Int32, why don directly create a List<Int32> ?

List<Int32> resultMap = (from item in mapResult
                              select item.ResultDE).ToList<Int32>();

List<Result_DE> resultList = (from result in db.Result_DE
                                  where result.IsActive == "1"
                                  && resultMap.Contains(result.ID)
                                  select result).ToList<Result_DE>();

回答4:

Use SqlFunctions.StringConvert instead of Convert.ToString.

A similar question was asked and answered here