5

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.

Biswakalyan
  • 145
  • 1
  • 1
  • 9

4 Answers4

2

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();
Alex
  • 13,024
  • 33
  • 62
  • 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" – Pawel May 14 '15 at 04:07
  • @Pawel I agree, will leave it as is. – Alex May 14 '15 at 04:09
  • Thanks :) .. `mapResult.Any(mr => mr.ResultDE == r.ID)` worked – Biswakalyan May 17 '15 at 16:03
2

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

Pritam De
  • 151
  • 1
  • 4
0

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>();
Yu Yenkan
  • 745
  • 1
  • 9
  • 32
0

Use SqlFunctions.StringConvert instead of Convert.ToString.

A similar question was asked and answered here

Community
  • 1
  • 1
Gabriel Rainha
  • 1,713
  • 1
  • 21
  • 34