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

Exception

LINQ to Entites does not recognize the method 'System.String ToString(System.String)' method, and this method cannot be translated into a store expression

Explanation


By querying against a database using LINQ, what happens is that your LINQ to Entities queries will get translated to SQL queries. So In order to perform a correct LINQ to Entities query, you have to use LINQ methods that are also known by your DBMS (DataBase Management System) as SQL clauses. Otherwise, you will get the above exception.

Following is an example of an Article entity.

public class Article
{
    public int articleId { get; set; }

    public string title { get; set; }

    public string description { get; set; }

    public DateTime publicationDate { get; set; }
}

We would like to list all articles published on the second of october 2018.

List<Article> articles = ctx.articles.Where(a => a.publicationDate.ToString("d") == "10/02/2018").ToList();

ToString is not a SQL clause, so our DBMS will not recognize it and therefore we will get the error.

Solution


To deal with these kinds of exceptions, you have to use LINQ methods that are also known by your DBMS as SQL clauses or you can store the result set first in memory then apply LINQ operators.

/* Storing the result set in memory by using a first ToList() then performing the ToString() operator */
List<Article> articles = ctx.articles.ToList().Where(a => a.publicationDate.ToString("d") == "10/02/2018").ToList();