Entity Framework linq to sql conversion issue

Question

Fighting with EF6/linq to SQL to get a desired result. I'd rather not have to create a view in the database.

Any ideas on why EF is converting it this way or how to trick it otherwise?

My linq predicate:

.Where(x => 
   (x.AccountId == viewModel.AccountId || x.AccountId == null)
   && (x.CompanyId == viewModel.CompanyId || x.Company == null)
   && (x.FacilityId == viewModel.FacilityId || x.FacilityId == null)
)

The generated SQL:

WHERE 
   (([Extent1].[AccountId] = 1) 
    OR (([Extent1].[AccountId] IS NULL) AND (1 IS NULL)) 
    OR ([Extent1].[AccountId] IS NULL)
   ) 
   AND 
   (
    ([Extent1].[CompanyId] = 11) 
    OR (([Extent1].[CompanyId] IS NULL) AND (11 IS NULL)) 
    OR ([Extent2].[Id] IS NULL)
   ) 
   AND 
   (
    ([Extent1].[FacilityId] = 1) 
    OR (([Extent1].[FacilityId] IS NULL) AND (1 IS NULL)) 
    OR ([Extent1].[FacilityId] IS NULL)
   )  
   AND 
   (
    ([Extent1].[FacilityId] = 1) 
    OR (([Extent1].[FacilityId] IS NULL) AND (1 IS NULL))
   )

The SQL I thought I'd get, and does achieve the desired result:

WHERE 
(
    ([Extent1].[AccountId] = 1) 
    OR ([Extent1].[AccountId] IS NULL)
) 
AND 
(
    ([Extent1].[CompanyId] = 11) 
    OR ([Extent2].[Id] IS NULL)
) 
AND 
(
    ([Extent1].[FacilityId] = 1) 
    OR ([Extent1].[FacilityId] IS NULL)
)

What are the types of x.AccountId and viewModel.AccountId? are they nullable types? also, are those numbers constants (1, 11, etc.) being generated by EF? — yv989c
– yv989c, Commented Feb 1, 2019 at 5:00
@yv989c they are nullable int as are CompanyId and FacilityId. Not sure exactly what you mean on the second question. They come from the values in the viewModel...EF actually creates a parameterized query, so in the true EF output, those look like [Extent1].[AccountId] = @p__linq__0. I do see one issue || x.Company == null should be || x.CompanyId == null, but I don't think that will totally clear it up. — crichavin
– crichavin, Commented Feb 1, 2019 at 5:09

yv989c · Accepted Answer · 2019-02-01 15:19:31Z

2

Please try:

.Where(x => 
   (x.AccountId == (int)viewModel.AccountId || x.AccountId == null)
   && (x.CompanyId == (int)viewModel.CompanyId || x.Company == null)
   && (x.FacilityId == (int)viewModel.FacilityId || x.FacilityId == null)
)

Or:

var accountId = viewModel.AccountId.GetValueOrDefault();
var companyId = viewModel.CompanyId.GetValueOrDefault();
var facilityId = viewModel.FacilityId.GetValueOrDefault();
...
...
.Where(x => 
   (x.AccountId == accountId || x.AccountId == null)
   && (x.CompanyId == companyId || x.Company == null)
   && (x.FacilityId == facilityId || x.FacilityId == null)
)

Your original query was referencing nullable types as parameters, therefore, EF needed to generate a predicate that was able to work predictably when the value of your parameter is null, that's why you will see the extra ([Extent1].[AccountId] IS NULL) AND (@p__linq__0 IS NULL). By casting your parameters to the underline type in your query (in this case System.Int32), EF won't see the need of doing this because it "thinks" your parameter cannot be null.

All of this is needed because by default your SQL server connection will have the option ANSI_NULLS on, this means that any comparison with NULL will be false, and that's why EF needs to generate this extra logic (IS NULL operator), to ensure that you can get predictable results when the value of your parameter is null.

You can try this to see the effects of ANSI_NULLS in action:

SET ANSI_NULLS ON;
SELECT 1 WHERE NULL = NULL;

SET ANSI_NULLS OFF;
SELECT 1 WHERE NULL = NULL;

edited Feb 1, 2019 at 15:19

answered Feb 1, 2019 at 5:25

yv989c

1,5631 gold badge16 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

crichavin Over a year ago

Very close! I tried your first solution and got the correct sql except it also added this final clause AND (([Extent1].[FacilityId] = 1) OR (([Extent1].[FacilityId] IS NULL) AND (1 IS NULL))). So for clarity, the full clause returned was

WHERE  		([Extent1].[AccountId] = 1 OR [Extent1].[AccountId] IS NULL)  		AND ([Extent1].[CompanyId] = 11 OR [Extent1].[CompanyId] IS NULL)  		AND ([Extent1].[FacilityId] = 1 OR [Extent1].[FacilityId] IS NULL)  		AND (([Extent1].[FacilityId] = 1) OR (([Extent1].[FacilityId] IS NULL) AND (1 IS NULL)))

yv989c Over a year ago

Hehe I can't make sense of this.. but I just saw an inconsistency, please change x.Company == null to x.CompanyId == null and try again. Not sure if this has anything to do with that weird behavior.

crichavin Over a year ago

yeah that was a typo...I mentioned that in my comment above. It already is that way. But thanks. Actually, I checked the sql right after this linq statement, and it is correct now, thanks to you...the final sql executed against the database as that extra line in it...I'll have to investigate my code further down stream to see what is manipulating it further. Thanks for your help!

yv989c Over a year ago

The only thing I can think of is that you have another predicate on FacilityId that's being compared against a nullable type. No more predicates or direct references to viewModel.FacilityId in your query?

Collectives™ on Stack Overflow

Entity Framework linq to sql conversion issue

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related