Entity Framework linq to sql conversion issue

c# entity-framework entity-framework-6 linq-to-sql

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)
) 
1
2
2/1/2019 5:19:57 AM

Accepted Answer

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;
2
2/1/2019 3:19:31 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow