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

Question

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 lt entity gt  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

User · Answer

Just turn the LINQ to Entity query into a LINQ to Objects query  e g  call ToArray  anytime you need to use a method call in your LINQ query

User · Answer

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

User · Answer

Upgrading to Entity Framework Version 6 2 0 worked for me   I was previously on Version 6 0 0   Hope this helps

User · Answer

Change it like this and it should work   var key   item Key ToString    IQueryable lt entity gt  pages   from p in context pages                            where  p Serial    key                            select p    The reason why the exception is not thrown in the line the LINQ query is declared but in the line of the foreach is the deferred execution feature  i e  the LINQ query is not executed until you try to access the result  And this happens in the foreach and not earlier

User · Answer

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

User · Answer

Had a similar problem  Solved it by calling ToList   on the entity collection and querying the list  If the collection is small this is an option   IQueryable lt entity gt  pages   context pages ToList   Where p  gt p serial    item Key ToString      Hope this helps

User · Answer

I got the same error in this case   var result   Db SystemLog  Where log   gt      eventTypeValues Contains log EventType       amp  amp            search Contains log Id ToString               log Message Contains search             log PayLoad Contains search             log Timestamp ToString CultureInfo CurrentUICulture  Contains search           OrderByDescending log   gt  log Id   Select r   gt  r     After spending way too much time debugging  I figured out that error appeared in the logic expression    The first line search Contains log Id ToString    does work fine  but the last line that deals with a DateTime object made it fail miserably       log Timestamp ToString CultureInfo CurrentUICulture  Contains search    Remove the problematic line and problem solved   I do not fully understand why  but it seems as ToString   is a LINQ expression for strings  but not for Entities  LINQ for Entities deals with database queries like SQL  and SQL has no notion of ToString    As such  we can not throw ToString   into a  Where   clause    But how then does the first line work  Instead of ToString    SQL have CAST and CONVERT  so my best guess so far is that linq for entities uses that in some simple cases  DateTime objects are not always found to be so simple

User · Answer

Cast table to Enumerable  then you call LINQ methods with using ToString   method inside       var example   contex table name AsEnumerable    Select x   gt  new  Date   x date ToString  M d yyyy         But be careful  when you calling AsEnumerable or ToList methods because you will request all data from all entity before this method  In my case above I read all table name rows by one request

User · Answer

Just save the string to a temp variable and then use that in your expression   var strItem   item Key ToString     IQueryable lt entity gt  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

User · Answer

If you really want to type ToString inside your query  you could write an expression tree visitor that rewrites the call to ToString with a call to the appropriate StringConvert function   using System Linq  using System Data Entity SqlServer  using System Linq Expressions  using static System Linq Expressions Expression  using System   namespace ToStringRewriting       class ToStringRewriter   ExpressionVisitor           static MethodInfo stringConvertMethodInfo   typeof SqlFunctions  GetMethods                     Single x   gt  x Name     StringConvert   amp  amp  x GetParameters   0  ParameterType    typeof decimal              protected override Expression VisitMethodCall MethodCallExpression node                var method   node Method              if  method Name   ToString                     if  node Object GetType      typeof string     return node Object                    node   Call stringConvertMethodInfo  Convert node Object  typeof decimal                               return base VisitMethodCall node                       class Person           string Name   get  set            long SocialSecurityNumber   get  set              class Program           void Main                 Expression lt Func lt Person  Boolean gt  gt  expr   x   gt  x ToString   Length  gt  1              var rewriter   new ToStringRewriter                var finalExpression   rewriter Visit expr               var dcx   new MyDataContext                var query   dcx Persons Where finalExpression

User · Answer

In MVC  assume you are searching record s  based on your requirement or information   It is working properly    HttpPost   ActionName  Index    public ActionResult SearchRecord FormCollection formcollection               EmployeeContext employeeContext   new EmployeeContext         string searchby formcollection  SearchBy        string value formcollection  Value         if  formcollection  SearchBy       Gender                 List lt MvcApplication1 Models Employee gt  emplist   employeeContext Employees Where x   gt  x Gender    value  ToList            return View  Index   emplist             else               List lt MvcApplication1 Models Employee gt  emplist   employeeContext Employees Where x   gt  x Name    value  ToList            return View  Index   emplist

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

Examples related to c#

Examples related to mysql

Examples related to sql

Examples related to linq