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

Created on 19 Dec 2016 · 6Comments · Source: dotnet/efcore

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):

``` C#
query.Where(p => ((p.Code == "100000") Or p.Code.EndsWith("200")));


``` C# 
query.Where(p => (p.year == "2015"))}

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

``` C#
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:

``` SQL
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

closed-fixed type-bug

Source