Volatil3
Volatil3

Reputation: 15008

Can't use ToString() in LINQ to Entities query

So I have the following code:

string searchQuery = collection["query"];    
var srmas = (
    from SRMAs in db.SRMAs
    join SRMAStatus in db.SRMAStatus on SRMAs.Id equals SRMAStatus.Id
    join PurchaseOrders in db.PurchaseOrders on SRMAs.PONumber equals PurchaseOrders.PONumber
    join Suppliers in db.Suppliers on PurchaseOrders.SupplierID equals Suppliers.SupplierID
    join SRMADetails in db.SRMADetails on SRMAs.Id equals SRMADetails.SRMAId
    where ids.Contains(SRMAs.Status) 
    && 
    (
        searchQuery.Contains(PurchaseOrders.suppliersOrderNumber)
        ||
        searchQuery.Contains(SRMAs.PONumber.ToString())
    )
    select new
    {
        SRMAs.Id,
        SRMAs.PONumber,
        SRMAs.CreatedOn,
        Suppliers.SupplierName,
        SRMAStatus.StatusName,
        PurchaseOrders.PODate, PurchaseOrders.suppliersOrderNumber
    }
).ToList();

Where searchQuery is a string variable.

I have to actually use IN clause ofr PONumber and for that purpose I am using Contains which gives error mentioned in title. How do I check non String values?

Upvotes: 0

Views: 2329

Answers (3)

Yusuf Demirag
Yusuf Demirag

Reputation: 763

EF 4 does not support ToString() Method on queries. Either you need to update it to EF6 or you can use SqlFunctions.StringConvert function as follows.

string searchQuery = collection["query"];    
var srmas = (
    from SRMAs in db.SRMAs
    join SRMAStatus in db.SRMAStatus on SRMAs.Id equals SRMAStatus.Id
    join PurchaseOrders in db.PurchaseOrders on SRMAs.PONumber equals PurchaseOrders.PONumber
    join Suppliers in db.Suppliers on PurchaseOrders.SupplierID equals Suppliers.SupplierID
    join SRMADetails in db.SRMADetails on SRMAs.Id equals SRMADetails.SRMAId
    where ids.Contains(SRMAs.Status) 
    && 
    (
        searchQuery.Contains(PurchaseOrders.suppliersOrderNumber)
        ||
        searchQuery.Contains(SqlFunctions.StringConvert((double)SRMAs.PONumber))
    )
    select new
    {
        SRMAs.Id,
        SRMAs.PONumber,
        SRMAs.CreatedOn,
        Suppliers.SupplierName,
        SRMAStatus.StatusName,
        PurchaseOrders.PODate, PurchaseOrders.suppliersOrderNumber
    }
).ToList();

Upvotes: 0

Miniver Cheevy
Miniver Cheevy

Reputation: 1677

you could give SqlFunctions.StringConvert a shot, it'll marry you to sql server and requires .Net 4+

searchQuery.Contains(SqlFunctions.StringConvert((decimal)SRMAs.PONumber))

the function seems a little twitchy, when I was spinning up a sample I had to convert my int to a decimal to avoid a Ambigious Invoication build error.

Upvotes: 2

Pleun
Pleun

Reputation: 8920

One apporach would be to convert searchQuery to the numeric datatype that the PONumber is, and you are all set.

Upvotes: 0

Related Questions