How to use ToString() method to convert an integer to string inside LINQ

Question

When I try to use ToString() inside the below LINQ Lambda expression, I get 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."

query = query.Where(q => q.date.ToString().Contains(filtertext)
                          || q.invoicenum.ToString().Contains(filtertext)
                          || q.trans_type.ToString().Contains(filtertext)
                          || q.charge.Contains(filtertext));

I am using Linq to entites. And the Database used is MySQL and not the SQL Server. Immediate help would be highly appreciated.

Answer 1

I resolved this issue by directly writing MySQl query inside C# as below -

string queryTemplate = 
 @"select inv.* from invoices as inv where userID = '123' and date like '%abc%'";
List<invoice> totalSearch = 
 context.ExecuteStoreQuery<invoice>(queryTemplate).ToList();

Answer 2

Harshal, the issue is the SqlFunctions.StringConvert is for MS SQL, not for MySQL. You can try convert the results to Enumerable and then query on it. Example:

using (DatabaseEntities db = new DatabaseEntities())
        {
            var list = from l in db.Customers.AsEnumerable()
                       orderby l.CompanyName
                       select new SelectListItem { Value = l.CustomerID.ToString(), Text = l.CompanyName };

            return list.ToList();
        }

Note the line:

l in db.Customers.AsEnumerable()

is converting the results to Enumerable, then you can use .toString() on it. You can adapt this to your needs.

Answer 3

In EF 4 you can use SqlFunctions.StringConvert