dagda1 dagda1 - 10 months ago 44
C# Question

EF4 adding is null clause onto where clause

I have the following linq query:

var fileDocuments = (
from doc in fileUploads
from invoice in
from inv in _dbContext.SupplierInvoiceHeaders
where inv.InvoiceDocumentId == doc.ID || inv.JobSheetInvoiceId == doc.ID
select inv
join pos in _dbContext.PurchaseOrders on invoice.PurchaseOrder.PurchaseOrderId equals pos.PurchaseOrderId into poss
from po in poss.DefaultIfEmpty()
join hdf in _dbContext.HelpDeskFaults on po.HelpdeskFaultId equals hdf.ID into hdfpo
from hs in hdfpo.DefaultIfEmpty()
join store1 in _dbContext.Stores on hs.StoreID equals store1.ID into hsf
from hdfStore in hsf.DefaultIfEmpty()
join js in _dbContext.JobSheets on invoice.SupplierInvoiceHeaderId equals js.SupplierInvoiceHeaderID into jss
from jobSheets in jss.DefaultIfEmpty()
join ch in _dbContext.ChildProjects on po.ChildProjectId equals ch.ID into chs
from childProjects in chs.DefaultIfEmpty()
join ph in _dbContext.ProjectHeaders on childProjects.ProjectHeaderID equals ph.ID into phs
from projectHeaders in phs.DefaultIfEmpty()
join ppmsl in _dbContext.PpmScheduleLines on projectHeaders.PPMScheduleRef equals ppmsl.ID into ppsmsls
from ppmScheduleLines in ppsmsls.DefaultIfEmpty()
join ss2 in _dbContext.Stores on ppmScheduleLines.StoreID equals ss2.ID into ssts
from store2 in ssts.DefaultIfEmpty()
select new
JobSheetId = jobSheets.DocumentID,
StoreName = doc.Store.Name,
DocumentType = doc.DocumentType.Name,
HelpDeskFaultStoreName = hs.Store.Name,
DocStoreName = doc.Store.Name,
PPMScheduleLinesStoreName = ppmScheduleLines.Store.Name,
PIR = invoice.PurchaseInvoiceReference

fileDocuments = fileDocuments.Where(x => x.PIR == jobSearchParams.PIR);

The where clause that is generated looks like this:

WHERE ([Extent2].[fld_str_PIR] = @p__linq__0) OR (([Extent2].[fld_str_PIR] IS NULL) AND (@p__linq__0 IS NULL))

I do not understand why it is adding the IS NULL clauses.

Evk Evk
Answer Source

It adds them because your jobSearchParams.PIR might be null. In case it is null, EF will assume you want to return rows where fld_str_PIR is null. But in sql, your cannot compare with null using "=" operator:

WHERE ([Extent2].[fld_str_PIR] = @p__linq__0) -- doesn't work if @p__linq__0 is null

That is why it handles this case for you and generates correct query for both cases - whether jobSearchParams.PIR is null or not.