Convert int to string in linq for search

Question

I would like my users to be able to search for purchase orders (which are INT) via partial lines, that is, if the purchase order is 123456, typing in 456, they give me 123456 in the results.

I thought this would work:

var pop = (from po in ctx.PurchaseOrders let poid = po.PurchaseOrderID.ToString() where poid.Contains(term) select new SearchItem { id = poid, label = po.SupplierID, category = "purchaseorder" }).ToList(); 

But I get an error

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

How can I convert an INT PurchaseOrderID to a string to allow me to search for fragments of it?

thanks

Answer 1

For future reference, fix this using:

  var pop = ctx .PurchaseOrders .OrderBy(x => x.PurchaseOrderID) .ToList() .Select(x => new SearchItem() { id = x.PurchaseOrderID.ToString(), label = x.SupplierID, category = "purchaseorder" }); 

This is a list of intermediaries that makes it work.

Answer 2

Use this:

 id = SqlFunctions.StringConvert((double)poid) 

More details: http://msdn.microsoft.com/en-us/library/dd487127.aspx

Answer 3

You get this error because your LINQ to SQL does not recognize the ToString() method.

If you want to convert int to string , and you are using SQL Server with EF, use the SqlFunctions.StringConvert function this way:

 let poid = SqlFunctions.StringConvert((double)po.PurchaseOrderID); 

or this, as an alternative:

 where Convert.ToString(po.PurchaseOrderID).Contains(term) 

The problem is that we don’t know what provider you are using with EF. In the case of SQL Server, this will work, but if the provider is different, for example, MySQL using this expression, the application will throw an exception with this message:

The specified System.String StringConvert method of type 'System.Data.Objects.SqlClient.SqlFunctions' cannot be translated into a LINQ to Entities storage expression.

Be careful!

In case you are not using SQL Server as a provider, use an explicit cast to your query, as suggested by @SimonBelanger:

 let poid = (string)po.PurchaseOrderID 

Answer 4

I stumbled upon this question and wanted to post a solution that would work with Entity Framework and LINQ with objects through a Lambda expression.

  db.PurchaseOrders.AsEnumerable() .Where(x => x.PurchaseOrderID.ToString().Contains(term)) .ToList(); 

which was borrowed from the solution here → fooobar.com/questions/34737 / ...

Answer 5

Linq does not know what the ToString () method is, but you can define your own method. If you use this link , your problem will be solved.

Answer 6

try it

 var pop = (from po in ctx.PurchaseOrders let poid = SqlFunctions.StringConvert((double)po.PurchaseOrderID) where poid.Contains(term) select new SearchItem { id = poid, label = po.SupplierID, category = "purchaseorder" }).ToList();