Convert 'string' to 'int' in an .Any() LINQ query

c# entity-framework entity-framework-6 linq sql-server-2012

Question

I have a conditional statement that evaluates an .Any() LINQ query against my DB.

It is throwing an error when casting/converting a string to long value.

(long.TryParse(m.Reference,out t)? t : long.MaxValue)

The error is something like:

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

Am I doing something wrong here? How can I achieve this?

using (var ctx = new DatabaseEntities())
{
    long t;
    if(!ctx.CustomerInboxes.Any(m=>m.CustomerId == customerId
        && m.Reference == item.ShoppingCartWebId.ToString() 
        && m.SubjectId == HerdbookConstants.PendingCartMessage 
        && item.ShoppingCartWebId > (long.TryParse(m.Reference,out t)? t : long.MaxValue)))
    )){
        // do something special
    }
}
1
1
8/8/2019 9:55:04 PM

Accepted Answer

According to your code you when m.Reference is not a valid number the condition should fail, this can be done using SqlFunctions.IsNumeric().

To compare numbers you can use string.Compare and simulate numeric comparison padding left with 0s (it's possible with SqlFunctions.Replicate()).

It's not too pretty, but should works:

var itemId = item.ShoppingCartWebId.ToString();

ctx.CustomerInboxes.Any(m => ...
                          && SqlFunctions.IsNumeric(m.Reference) != 0 
                          && string.Compare(SqlFunctions.Replicate("0", m.Reference.Length > itemId.Length ? m.Reference.Length - itemId.Length : 0) + itemId, m.Reference) > 0);

However you always can switch to Linq to Objects to check this specific part:

ctx.CustomerInboxes.Where(m => m.CustomerId == customerId &&
                               m.Reference == item.ShoppingCartWebId.ToString()  &&
                               m.SubjectId == HerdbookConstants.PendingCartMessage)
                    .AsEnumerable()
                    .Any(c => item.ShoppingCartWebId > (long.TryParse(c.Reference, out t) ? t : long.MaxValue))
1
4/28/2016 7:28:39 PM

Popular Answer

using (var ctx = new DatabaseEntities())
{
    long t;
    if(!ctx.CustomerInboxes.ToList().Any(m=>m.CustomerId == customerId
        && m.Reference == item.ShoppingCartWebId.ToString() 
        && m.SubjectId == HerdbookConstants.PendingCartMessage 
        && item.ShoppingCartWebId > (long.TryParse(m.Reference,out t)? t : long.MaxValue)))
    ))
    {
        // do something special
    }
}

Just add .ToList(). While this will bring back your entire list to the client it will allow you to do the custom operators you want to do.

Based on the comments here is another alternative of bringing back a subset from the db then doing the custom parsing operator.

long t;
var initialQuery = ctx.CustomerInboxes.Where(x => m.CustomerId == customerId
    && m.Reference == item.ShoppingCartWebId.ToString()
    && m.SubjectId == HerdbookConstants.PendingCartMessage).ToList();

if (!initialQuery.Any(m => item.ShoppingCartWebId > (long.TryParse(m.Reference, out t) ? t : long.MaxValue)))
{
    // do something special
}


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow