How to use Any() instead of foreach?
I need to run a stored procedure on SQL Server, using LINQ, and throw an exception if it returns any rows. The following code works, although the foreach
loop does not communicate my intent well enough:
foreach (var badData in context.BadDataForDay(today))
{
throw new Exception("Bad data found");
}
I wanted to write something like IF EXISTS()
in SQL, but with LINQ:
var badData = from p in context.BadDataForDay(today) select p;
if (badData.Any())
{
throw new Exception("Bad data found");
}
This code compiles but Any()
blows up with the following message:
Specified cast is not valid
What am I missing? Is Any()
the right method to use in this case?
Edit: I debugged, and the code blows up before the stored procedure is called, at the following generated line:
return ((ISingleResult<BadDataResult>)(result.ReturnValue));
To get it to work, I did the following:
I added another stored procedure and dragged it to my context:
CREATE PROCEDURE dbo.BadDataExists
@AsOfDate DATETIME
AS
BEGIN ;
SET NOCOUNT ON ;
SELECT COUNT(*) AS Cnt
FROM SomeTable
WHERE SOME conditions ;
END ;
I used this code:
foreach (var badDataCount in
context.BadDataExists(asO开发者_运维百科fDate).Where(badDataCount => badDataCount.Cnt > 0))
{
Although it works now, I would really like to understand what I was missing.
I assume your foreach
code would also blow up, if context.BadDataForDay(today)
would return any data...
The code that translates the objects from the database into C# objects and returns them as an enumerable inside BadDataForDay
seems to be performing a cast that compiles fine but that blows up at runtime, because the object that should be casted has the wrong type.
The reason why Any
and foreach
only blow up when data is returned is simple: Without data, no cast will be performed.
This should work:
var badData = context.BadDataForDay(today);
if (badData != null && badData.Any())
{
throw new Exception("Bad data found");
}
What exception is throwing?
Any is used with a condition like this for example (I am not sure this is what you need):
var res = (from c in context.Cusotmer
where c => c.Orders.Any(o => o.year == 2010)
select c);
in your case might be:
var badData = (from p in context.BadDataForDay
where p => p.Days.Any(d => d == today) select p);
精彩评论