Query: Using Or instead of ElseOr, etc. can result in unexpected SQL

[datasecurity]

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

[tuespetre]

This turned out not be because of the Sum but because of the interaction between BinaryExpressions and ConditionalExpressions.

[divega]

Assigning issue and PR to @smitpatel.

[liamdawson]

Does someone have a workaround for this issue?

[smitpatel]

Use OrElse instead of Or (Or is bitwise operator, OrElse is logical operator)

[liamdawson]

Would AndAlso also be the appropriate change for And expressions?

[smitpatel]

Yes.