Simple Linq expression producing bloated where clause that doesn't make sense to me

Question

I am trying to use this Linq expression

Dim ex2 As Expression(Of Func(Of Advertisement, Boolean)) =
    Function(a) a.Address.CountryCode = ISO AndAlso a.Address.Region = EmptyString

in this manner

Dim ltest = (From a In db.Advertisements.AsExpandable().Where(ex2)).ToList

but it produces these crazy where conditions

DECLARE @p__linq__0 NVARCHAR (MAX) = N'US';
DECLARE @p__linq__1 NVARCHAR (MAX) = N'';
SELECT [Extent1].[AdvertisementID] AS [AdvertisementID],
       [Extent1].[URL] AS [URL],
       [Extent1].[Address_AddressID] AS [Address_AddressID],
       [Extent1].[Business_BusinessID] AS [Business_BusinessID]
FROM [dbo].[Advertisements] AS [Extent1]
LEFT OUTER JOIN [dbo].[Addresses] AS [Extent2]
             ON [Extent1].[Address_AddressID] = [Extent2].[AddressID]
WHERE (([Extent2].[CountryCode] = @p__linq__0)
       OR (([Extent2].[CountryCode] IS NULL)
           AND (@p__linq__0 IS NULL)))
  AND (([Extent2].[Region] = @p__linq__1)
       OR (([Extent2].[Region] IS NULL)
           AND (@p__linq__1 IS NULL)))

I don't know why it produces @p__linq__1 IS NULL when my parameters are strings and not nullables.

I expect it to produce something like

WHERE [Extent2].[CountryCode] = @p__linq__0 AND [Extent2].[Region] = @p__linq__1

I tried the same thing using LinqKit predicates instead of expressions but got the same results. I actually have a much more complex query with additional where clauses but when it didn't work i broke it down to just this simple query and found the where clause was nothing like what I intended.

So the question is what is the proper way to produce a simpler where clause similar to what I entended using LinqKit expressions or predicates?

Is the field Advertisements.CountryCode nullable in the database? — Ross Presser, Dec 01 '15 at 02:59
Advertisements.Address_AddressID could be null although Addresses.CountryCode is not null. But if my string @p__linq__0 cannot be null then why would it include that in the query when it can never be true? Why not just omit these repeating pieces OR (([Extent2].[CountryCode] IS NULL) AND (@p__linq__0 IS NULL)) — masteroleary, Dec 01 '15 at 03:11
"my parameters are strings and not nullables" - but strings are reference types - they're inherently nullable. — Damien_The_Unbeliever, Dec 01 '15 at 11:44
Is there something similar to a string that I can use that cant be null that might remove the additional sql? When debugging the SQL I have to first remove the extra items before I can understand whats going on. Anything you could suggest to help simplify it. — masteroleary, Dec 01 '15 at 17:28
Possible duplicate of [Why does Entity Framework 6 generate complex SQL queries for simple lookups?](http://stackoverflow.com/questions/20107016/why-does-entity-framework-6-generate-complex-sql-queries-for-simple-lookups) — Gert Arnold, Dec 01 '15 at 22:17

D Stanley · Answer 1 · 2015-12-01T22:35:09.730

Is there something similar to a string that I can use that cant be null that might remove the additional sql?

You could try adding a requirement to the expression that guarantees that the string is not null:

a.Address.CountryCode = ISO _
    AndAlso Not EmptyString Is Nothing _
    AndAlso a.Address.Region = EmptyString

However, the SQL generated by EF is meant to exactly represent the expressions passed in (including the differences in null semantics) and not necessarily to be fully optimized or human readable. Meaning that the SQL generator may not generate the exact sql that you expect by adding that requirement.

Or, ifEmptyString is a "constant" value that is literally an empty string, you could try using String.Empty instead. The interpreter may use the constant value in the query instead of using a parameter.

Simple Linq expression producing bloated where clause that doesn't make sense to me

1 Answers1