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

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

Question

A conditional statement I have assesses a.Any() LINQ querying my database.

When casting or converting a string to a long value, an error is being thrown.

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

The mistake looks like this:

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

Am I doing this incorrectly? How do I accomplish 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

As per your code, you whenm.Reference The condition should fail if is an invalid number; this can be accomplished bySqlFunctions.IsNumeric() .

When comparing numbers, usestring.Compare and simulate padding left for numerical comparison0 It is feasible withSqlFunctions.Replicate() ).

Although not very attractive, it should work:

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

You can always change to Language to Objects to check this particular area, though:

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
    }
}

Only add.ToList() . Even though the client will receive your whole list back, you can still use the custom operators you want to use.

According to the comments, here is another option that involves retrieving a subset from the database before performing 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