convert int to string in linq for searching

Question

I would like my users to be able to search for purchase orders (which are INT's) via partial strings, i.e. if a purchase order is 123456 typing in 456 gives 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 the INT PurchaseOrderID to a string to enable me to search for fragments of it?

Thanks

Answer 1

Use this:

id = SqlFunctions.StringConvert((double)poid)

For more: http://msdn.microsoft.com/en-us/library/dd487127.aspx

Answer 2

For future reference, have fixed this by using:

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

It's the intermediary list that makes it work.

Answer 3

You're getting that error because your LINQ to SQL doesn't recognize the ToString() method.

If you want to convert an int to a string and you're using SQL Server with EF, use the function SqlFunctions.StringConvert in this way:

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

or this, as alternative:

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

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

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

Be careful!

In the case you're not using SQL Server as provider, use an explicit cast in 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 will work with Entity Framework and LINQ to entities via Lambda expression.

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

which was borrowed from the solution here --> https://stackoverflow.com/a/21234785/2395030