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
}
}
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))
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
}
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow