Linq-to-Entities The totality of a column that is evaluated in an external function.

Question

I want to sum the computed column Red, which is computed in the IsRed () function, which returns an integer.

When I run the query, I get the following error: the 'Int32 IsRed (Int32)' method does not support SQL translation.

How do I rewrite this to make it work. Thank.

From xx In 
    (From l In L3s
    Join a In BLs On l.L3ID Equals a.L3ID
    Order By l.ID
    Select PID = l.ID,
    Red = IsRed(a.D1.Day- l.D2.Day))
    Group By Key = xx.PID Into G = Sum(xx.Red)
    Select Key, G

---

Function IsRed(ByVal dayx As Integer) As Integer
    If (dayx < -7) Then
          Return 1
       Else
          Return 0
       End If
End Function

Answer 1

This is because LINQ to Entities cannot figure out how to translate the IsRed function to SQL. You can create a custom SQL function and associate it with the IsRed function, but to be honest, the easiest way is to simply paste the code:

From xx In 
(From l In L3s
Join a In BLs On l.L3ID Equals a.L3ID
Order By l.ID
Select PID = l.ID,
Red = If(a.D1.Day - l.D2.Day < -7, 1, 0))
Group By Key = xx.PID Into G = Sum(xx.Red)
Select Key, G

Answer 2

, LINQ-to-Entities , SQL (, , -SQL, ).

. SQL ( , , SQL , ). , .