Introduction

This is most common exception occurs while we are working with entity framework and converting data inside IQueryable result for filtering.

Description

Mainly full exception message as “LINQ to Entities does not recognize the method 'System.String ToString()' method, and this method cannot be translated into a store expression.”.

  1. var result=dc.Tests.Where(i=>i.status==status.ToString()).FirstOrDefault();
This exception mainly occurs when we query SQL database using entity framework. The problem is that you are calling ToString in a LINQ to Entities query (IQueryable to IEnumrable). That means the parser is trying to convert the ToString call into its equivalent SQL (which isn't possible...hence the exception).

To solve this problem there is a different solution.

Solution 1: All you have to do is move the ToString call to a separate line.
  1. String userStatus=status.ToString();
  2. var result=dc.Tests.Where(i=>i.status== userStatus).FirstOrDefault();
Solution 2: Convert IQueryable result to IEnumrable before convert.
  1. var result=dc.Tests.AsEnumerable().Where(i=>i.status==status.ToString()).FirstOrDefault();
Note:

This is only feasible for small entity collections, as in the above method first fetch all database results in memory using AsEnumerable() method and then filtering result. This is not feasible for large Db result.

Solution 3:

Use entity framework extension method SqlFunctions Class or DbFunctions Class,
  1. var result=dc.Tests.Where(i=>i.status== SqlFunctions.StringConvert
  2. tatus)).FirstOrDefault();
Here SqlFunctions.StringConvert method converts Linq entities query to SQL query.

Note: It's good when the solution with temporary variables is not desirable for whatever reason.

Conclusion

In this article we learned different ways to solve the most common exceptions while working with Entity Framework.