Exception

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();