Exception LINQ to Entities does not recognize the method ToString

Introduction

This is most common exception occurs while we 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.”

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 different solution.
Solution 1:  All you have to do is move the ToString call to a separate line.

String userStatus=status.ToString();
var result=dc.Tests.Where(i=>i.status== userStatus).FirstOrDefault();

Solution 2: Convert IQueryable result to IEnumrable before convert.

var result=dc.Tests.AsEnumerable().Where(i=>i.status==status.ToString())

.FirstOrDefault();

Note: This is only feasible for small entity collection as the above method first fetch all database result to in memory using AsEnumerable() method and then filtering result. Not feasible for large Db result.
Solution 3:  Use entity framework extension method SqlFunctions Class or DbFunctions Class

var result=dc.Tests.Where(i=>i.status==status.ToString()).FirstOrDefault();

Here SqlFunctions.StringConvert method converts Linq entities query to SQL query.

Note: Good when the solution with temporary variables is not desirable for whatever reasons.

Conclusion

In this article we learn different way to solve most common exception while working with Entity Framework.