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

I'm migrating some stuff from one mysql server to a sql server but i can't figure out how to make this code work:

using (var context = new Context())
{
    ...

    foreach (var item in collection)
    {
        IQueryable<entity> pages = from p in context.pages
                                   where  p.Serial == item.Key.ToString()
                                   select p;
        foreach (var page in pages)
        {
            DataManager.AddPageToDocument(page, item.Value);
        }
    }

    Console.WriteLine("Done!");
    Console.Read();
}

When it enters into the second foreach (var page in pages) it throws an exception saying:

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

Anyone know why this happens?

Solution 1:

Just save the string to a temp variable and then use that in your expression:

var strItem = item.Key.ToString();

IQueryable<entity> pages = from p in context.pages
                           where  p.Serial == strItem
                           select p;

The problem arises because ToString() isn't really executed, it is turned into a MethodGroup and then parsed and translated to SQL. Since there is no ToString() equivalent, the expression fails.

Note:

Make sure you also check out Alex's answer regarding the SqlFunctions helper class that was added later. In many cases it can eliminate the need for the temporary variable.

Solution 2:

As others have answered, this breaks because .ToString fails to translate to relevant SQL on the way into the database.

However, Microsoft provides the SqlFunctions class that is a collection of methods that can be used in situations like this.

For this case, what you are looking for here is SqlFunctions.StringConvert:

from p in context.pages
where  p.Serial == SqlFunctions.StringConvert((double)item.Key.Id)
select p;

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

Similar to SqlFunctions you also have the EntityFunctions (with EF6 obsoleted by DbFunctions) that provides a different set of functions that also are data source agnostic (not limited to e.g. SQL).

Solution 3:

The problem is that you are calling ToString in a LINQ to Entities query. That means the parser is trying to convert the ToString call into its equivalent SQL (which isn't possible...hence the exception).

All you have to do is move the ToString call to a separate line:

var keyString = item.Key.ToString();

var pages = from p in context.entities
            where p.Serial == keyString
            select p;