Why does replacing the lambda in a IQueryable.Sum

问题:

I wanted to abstract a lambda function I have which sums up records in my DB based on their status. The problem is, when I run the generated lambda against LINQ to Entities, I get "Internal .NET Framework Data Provider error 1025." (FYI, it works fine against LINQ to Objects).

Here's the code:

    <Extension()> _
    Public Function Summarize(ByVal auditsToSummarize As IQueryable(Of HvacAudit)) As IQueryable(Of HvacAuditSummary)
        Return From audit In auditsToSummarize
               Group By month = audit.DateCreated.Value.Month Into g = Group
               Select New HvacAuditSummary With {
                    .GroupingKey = month,
                    .Pending = g.Sum(Function(audit) If(audit.AuditStatusInt = CType(AuditStatus.Pending, Integer), 1, 0)),
                    .Issued = g.Sum(Function(audit) If(audit.AuditStatusInt = CType(AuditStatus.Issued, Integer), 1, 0)),
                    .Closed = g.Sum(Function(audit) If(audit.AuditStatusInt = CType(AuditStatus.Closed, Integer), 1, 0)),
                    .Cancelled = g.Sum(Function(audit) If(audit.AuditStatusInt = CType(AuditStatus.Cancelled, Integer), 1, 0))
                }
    End Function

And here's an example of how I try to use the lambda generator:

    Select New HvacAuditSummary() With {
        .Cancelled = g.Sum(AuditsWithStatus(AuditStatus.Cancelled))
    }

    Private Function AuditsWithStatus(auditStatus As AuditStatus) As Func(Of HvacAudit, Integer)
        Return Function(audit) If(audit.AuditStatusInt = CType(auditStatus, Integer), 1, 0)
    End Function

NOTE: I have looked through other questions with this error, they all seem to focus on using the wrong LINQ because a lambda (Func) was used in place of an expression. In the sum method, all the candidates seem to take a Func, so I don't know what else I can change.

回答1:

The Entity Framework query provider does not know your function AuditsWithStatus. EF tries to translate everything in your linq statement into sql, and it can only handle a restricted set of .Net functions of which it knows the sql equivalent. For the rest everything in the linq statement must be an expression (Expression<Func<...>>).

Which means: you can use your function but only after using ToList() or ToArray(), which turns it into linq-to-objects. Or you must turn your function into an expression.

回答2:

Check out your anonymous function.

.Sum is expecting what in C# we'd call a Func<bool>. You're returning 1 or 0, which are implicity either integers or doubles (I forget which, but it's unimportant in the context of your question.)

Long story short - true or false, not 1 or 0!