LINQ to Entities does not recognize the method 'System.String ToString(Int32)'

Question

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.

Solution 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();

Solution 2

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

Comments

[Pawel]

I edited your answer so that it now says Linq to Entities. You can update your comment if you think adding Linq To Sql to the mix is worth it. IMHO it was confusing and no one uses the term "Linq to DB"

[Alex]

@Pawel I agree, will leave it as is.

[Biswakalyan]

Thanks :) .. mapResult.Any(mr => mr.ResultDE == r.ID) worked