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

Try a direct cast, ie `let poid = (string)po.PurchaseOrderID`. Linq to entities will honor certain method call, `ToString` is not one of them. A direct cast is logicaly equival to a `CAST(X as Type)` in SQL. — Simon Belanger, Jan 21 '14 at 11:52
I have answered similar question here. Check it out. http://stackoverflow.com/questions/21233023/how-to-convert-following-into-linq-query-or-object-query/ — samar, Jan 21 '14 at 11:56
The solution to your problem is here: http://stackoverflow.com/questions/1066760/problem-with-converting-int-to-string-in-linq-to-entities — StevieB, Jan 21 '14 at 11:59

score 15 · Answer 1 · edited Oct 28 '14 at 14:56

15

Use this:

id = SqlFunctions.StringConvert((double)poid)

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

edited Oct 28 '14 at 14:56

p.s.w.g

146,324
30
291
331

answered Jan 21 '14 at 11:55

Bappi Datta

1,360
8
14

4

This will work. But it introduce a dependency between the code and the backend database (SqlFunctions will work if the EF provider is Sql Server) which may or may not be an issue. – Simon Belanger Jan 21 '14 at 11:57
Bappi, do you mean let poid = SqlFunctions.StringConvert((double)po.PurchaseOrderID) if so i get an error LINQ to Entities does not recognize the method 'System.String StringConvert(System.Nullable`1[System.Double])' method, and this method cannot be translated into a store expression. – Gordon Copestake Jan 21 '14 at 12:07

score 5 · Accepted Answer · answered Jan 21 '14 at 12:40

5

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.

answered Jan 21 '14 at 12:40

Gordon Copestake

1,616
4
21
37

2

I'd been using SqlFunctions.StringConvert all this time, but I really didn't like to create dependencies. Thank you for this solution! – Ivo Coumans Dec 18 '14 at 08:59
1

The ToList will order and get all purchase orders from the database and put them in memory. If you have a lot of records, you will run in to performance issues. You should filter your records on the database side before retrieving them. Use Convert.ToString(po.PurchaseOrderID).Contains(term) as @Alberto Solano describes. – Mar 22 '17 at 10:55

Alberto Solano · Answer 3 · 2014-01-21T21:01:22.540

2

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

edited Jan 21 '14 at 21:01

answered Jan 21 '14 at 12:07

Alberto Solano

7,972
3
38
61

I get: LINQ to Entities does not recognize the method 'System.String StringConvert(System.Nullable`1[System.Double])' method, and this method cannot be translated into a store expression. – Gordon Copestake Jan 21 '14 at 12:23
Which provider are you using in EF? I updated my answer. – Alberto Solano Jan 21 '14 at 13:11
I'm using System.Data.SqlClient – Gordon Copestake Jan 21 '14 at 16:21

score 2 · Answer 4 · edited May 23 '17 at 10:29

2

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

edited May 23 '17 at 10:29

Community

1
1

answered Dec 05 '14 at 18:54

ejhost

171
12

score 0 · Answer 5 · edited May 23 '17 at 12:26

0

Linq dont know what ToString() Method is, however you can define your custom method. If you use this link, you get your problem solved.

edited May 23 '17 at 12:26

Community

1
1

answered Jan 21 '14 at 11:54

SMI

303
1
6
22

score 0 · Answer 6 · answered Jan 21 '14 at 11:58

0

try this

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();

answered Jan 21 '14 at 11:58

Sid M

4,354
4
30
50

1

That gives me: LINQ to Entities does not recognize the method 'System.String StringConvert(System.Nullable`1[System.Double])' method, and this method cannot be translated into a store expression. – Gordon Copestake Jan 21 '14 at 12:09

convert int to string in linq for searching

6 Answers6

Linked