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
}
}
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 0
s (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))
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
}