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?
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.
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.