EF4 adding is null clause onto where clause

Question

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
            ).DefaultIfEmpty()
    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
    {
        doc.ID,
        JobSheetId = jobSheets.DocumentID,
        doc.Name,
        doc.DateCreated,
        doc.StoreID,
        StoreName = doc.Store.Name,
        DocumentType = doc.DocumentType.Name,
        doc.DocumentTypeID,
        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.

Answer 1

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.