CODEWITHSUNDEEP
c#sql-serverlinqentity-framework-core
data
data

Reputation: 93

Conditional sum using linq for Or condition

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

Upvotes: 1

Views: 895

Answers (3)

Ivan Stoev
Ivan Stoev

Reputation: 205619

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.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269773

This is too long for a comment, but does not address the linq part of the question.

I would expect the where clause to look like this:

where (code = '100000' or p.code like '%200') and (p.year = '2015')

All that bit manipulation that is generated is jarring. Note that the above code is ANSI-standard SQL as well.

Upvotes: 0

Eldaniz Ismayilov
Eldaniz Ismayilov

Reputation: 856

You can use || instead of or

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

Upvotes: 1

Related Questions