LINQ to Entities Does Not Recognize System.String ToString (Int32) Method

Question

Hi I am using a linq query that throws a LINQ to Entities error, it does not recognize the "System.String ToString (Int32)" method, and this method cannot be translated into a storage expression.

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

The error is the following 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 correct way to write this request.

Answer 1

You cannot use these conversion functions in the LINQ to Entities statement, they cannot be translated into SQL, you need to do the in-memory transformations. But I don’t think you need to do this at all.

If you used resultMap to get the resultList filtered by Results from which Id present in mapResult , follow these steps:

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

If mapResult is a collection in memory and not an IQueryable that is bound 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(); 

Answer 2

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

Answer 3

if your item.ResultDE and result.ID are Int32 variable types, 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>(); 

Answer 4

Use SqlFunctions.StringConvert instead of Convert.ToString.

A similar question was asked and answered here.