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)
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'.
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
But without sum its works fine and add a
= 1 to SQL command
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
You can use
|| instead of
query.Where(p => ((p.Code == "100000") || p.Code.EndsWith("200")))