Entity Framework linq to sql conversion issue

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

Question

EF6/linq to SQL battles to get the required outcome. I'd want to avoid having to build a database view.

Any suggestions as to why EF is converting it in this manner or how to fool it differently?

Indicator of my linq:

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

The SQL produced:

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 expected to get did provide the intended outcome:

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

ZZZ_tmp
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