CODEWITHSUNDEEP
c#linq.net-core
Arno Smulders
Arno Smulders

Reputation: 117

LINQ using conditional where generates wrong query

For a C# .NET Core application I need to adjust an existing LINQ statement to include a WHERE clause condition based on a boolean property. And most of all, I want the query to handle the filtering so I will not pull in to much data into the application.

This is an example of the LINQ statement

var SalesPrice = 25;
var myQuery = from tl in db.TransactionLines
            where (
                tl.SalesPrice > SalesPrice &&
                tl.TemplateType != 'SomeType' &&
                tl.VoucherNumber == null
            )
            select tl;

And that will result in the following clean and simple SQL

SELECT *
FROM   [rbo].[TransactionLines] AS [tl]
WHERE  (([tl].[SalesPrice] > 25) 
AND    (([tl].[TemplateType] <> 'SomeType') OR [tl].[TemplateType] IS NULL)) 
AND    [tl].[VoucherNumber] IS NULL

If I add the condition to alter the WHERE based on a property, the LINQ will look like this

var SalesPrice = 25;
var SomeCondition = true;
var myQuery = from tl in db.TransactionLines
            where (
                tl.SalesPrice > SalesPrice &&
                tl.TemplateType != 'SomeType' &&
                SomeCondition ?
                    (tl.VoucherNumber == null) :
                    (tl.VoucherNumber != null || tl.VoucherNumber == null)
            )
            select tl;

This will generate the following query which is not correct

SELECT *
FROM [rbo].[TransactionLines] AS [tl]
WHERE CASE
    WHEN ([tl].[SalesPrice] > 25) AND (([tl].[TemplateType] <> 'SomeType') OR [tl].[TemplateType] IS NULL)
    THEN CASE
        WHEN [tl].[VoucherNumber] IS NULL
        THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT)
    END ELSE CASE
        WHEN [tl].[VoucherNumber] IS NOT NULL OR [tl].[VoucherNumber] IS NULL
        THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT)
    END
END = 1

Because when I execute this query, it will return all records and not those that where previously filtered out by SalesPrice > 25 . What is the correct way? Or are there alternatives?

Upvotes: 0

Views: 118

Answers (1)

Selim Yildiz
Selim Yildiz

Reputation: 5370

Be careful when you use inline condition. It seems you forgot to add parenthesis ( , ):

where (
    tl.SalesPrice > SalesPrice &&
    tl.TemplateType != 'SomeType' &&
    (SomeCondition ?
        (tl.VoucherNumber == null) :
        (tl.VoucherNumber != null || tl.VoucherNumber == null))
)

Upvotes: 1

Related Questions