Conditional sum using linq for Or condition

c# entity-framework-core linq sql-server

Question

When I use two conditions joined by an OR, the result is not correct for SQL Server.

How can I fix it?

This is my LINQ code and result in SQL (that reflection created for me):

  query.Where(p => ((p.Code == "100000") Or p.Code.EndsWith("200")))
  query.Where(p => (p.year == "2015"))}

I added this where clause at runtime, now I add another extension method and it's not working:

query.sum(p => p.value)

Exception:

An exception of type 'System.Data.SqlClient.SqlException' occurred in Microsoft.EntityFrameworkCore.dll but was not handled in user code

Additional information: An expression of non-boolean type specified in a context where a condition is expected, near 'AND'.

SQL translated:

SELECT SUM([e].[Value])
FROM [acc].[Data161] AS [e]
WHERE (CASE
          WHEN RIGHT([e].[Code], LEN(N'201')) = N'201'
             THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT)
       END | 
       CASE
          WHEN RIGHT([e].[Code], LEN(N'199')) = N'199'
             THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT)
       END) 
  AND ([e].[SetadCode] = N'161')

The correct SQL should have = 1 before the AND.

But without sum its works fine and add a = 1 to SQL command

1
1
12/18/2016 2:35:13 PM

Accepted Answer

First off, the Or is not a valid C# operator

.Where(p => ((p.Code == "100000") Or p.Code.EndsWith("200")))

If you change it to ||, the query translates correctly and executes w/o issue.

Looking at the generated SQL, I'm pretty sure you have used the bitwise or operator (|) instead, in which case I get the same error. While this could be a EF Core translator bug, you shouldn't be using it anyway - use the logical or || operator and the generated SQL will not have all that CASE WHEN expressions, but a typical simple WHERE conditions.

1
12/18/2016 2:13:55 PM

Popular Answer

You can use || instead of or

query.Where(p => ((p.Code == "100000") || p.Code.EndsWith("200")))


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow