Object reference not set to an instance of an object on querying from different lists

Question

I have the linq below. Since I am querying from 2 datacontexts, I've brokendown the tables into var list. But then, I have the error "Object reference not set to an instance of an object". This happens because edr is null.

var meetingsQuery = (from s in this.ModelContext.Meetings select s).ToList(); 
var deliverablesQuery = (from s in this.ModelContext.Deliverables select s).ToList(); 
var deliverableDatesQuery = (from s in this.ModelContext.DeliverableDates select s).ToList();
var refDateTypesQuery = (from s in this.ModelContext.RefDateTypes select s).ToList(); 
var refDeliverablesQuery = (from s in this.ModelContext.RefDeliverables select s).ToList();
var updatesQuery = (from s in this.ArenaUpdateBASEModelContext.Updates select s).ToList(); 
var updateQCsQuery = (from s in this.ArenaUpdateBASEModelContext.UpdateQCs select s).ToList();

var submissionUpdates = (from e in meetingsQuery 
                         from edr in deliverablesQuery.Where(dr => dr.MeetingId == e.MeetingId && !dr.DeletedFlag).DefaultIfEmpty()
                                 from ed in deliverableDatesQuery.Where(d => d.DeliverableId == edr.DeliverableId && !d.DeletedFlag && d.RefDateTypeId == 1).DefaultIfEmpty()
                                 from ed2 in deliverableDatesQuery.Where(d2 => d2.DeliverableId == edr.DeliverableId && !d2.DeletedFlag && d2.RefDateTypeId == 2).DefaultIfEmpty()

                                 join ret in refDateTypesQuery on ed.RefDateTypeId equals ret.RefDateTypeId
                                 join rdt in refDeliverablesQuery on edr.RefDeliverableId equals rdt.RefDeliverableId
                                 join upd in updatesQuery on edr.RefDsgnSubmissionTypeId equals upd.UpdateId
                                 join uqc in updateQCsQuery on upd.UpdateId equals uqc.UpdateId

                                 where
                                        !e.DeletedFlag && !ret.DeletedFlag && !rdt.DeletedFlag && !upd.DeletedFlag && !uqc.DeletedFlag && e.ProjectId == arenaPiD// && rdt. .ObjectIdLink == "Update_UpdateId"
                                        && uqc.RefQCId == 6  // Distributed
                                        && uqc.RefQCStatusId == 2 // Complete

                                 orderby e.ScheduledDT descending

                                 select new
                                 {
                                     e.MeetingId,
                                     e.ScheduledDT,
                                     edr.DeliverableId,
                                     edr.RefDeliverableId,
                                     rdt.DeliverableAbbrv,
                                     UpdateId = edr.RefDsgnSubmissionTypeId != null ? edr.RefDsgnSubmissionTypeId : 0,
                                     RefRecommendationId = upd.RefRecommendationId != null ? upd.RefRecommendationId : 0,
                                     uqc.RefQCId,
                                     uqc.RefQCStatusId,
                                     DeadlineDate = ed != null ? ed.DeliverableDateValue.ToString() : "",
                                     ActualDate = ed2 != null ? ed2.DeliverableDateValue.ToString() : ""
                                 }).ToList();

thanks, I looked on that thread but i'm still in awe. this linq is working fine when we are using single datacontext but since we need to use 2 datacontext at once, we broke it down to lists. — user3579974, Apr 28 '14 at 05:27
You'll have to [debug](http://msdn.microsoft.com/en-us/library/sc65sadd.aspx) to find the issue. — Sriram Sakthivel, Apr 28 '14 at 05:31
It seems that this error occurs whenever the line of query does not have a match on the previous line, (ex: when there is no results for edr, the error occurs) — user3579974, Apr 28 '14 at 05:34

score 0 · Accepted Answer · answered Apr 28 '14 at 06:33

There's a big difference to how DefaultIfEmpty works for Linq To Objects vs Linq To SQL, which is tied into the difference between how nulls are handled between the two.

In SQL an empty record from a LEFT OUTER JOIN is populated with null values. Since your SQL never refer to the record itself this is not a problem. When you write edr.DeliverableId for an unmatched edr, the result is null.

Linq to SQL is different. When you try to reference any field or property of an unmatched edr the resultant error is exactly what you have seen. Every reference to edr after the DefaultIfEmpty call needs to be checked first to see if edr is valid.

Beyond the immediate error however...

You've mixed a couple of join forms - inner and outer - in that query, and the result is convoluted and unexpected. The problems you're taking on with DefaulIfEmpty for an outer join are negated with a subsequent inner join that depends on the outers. Which means that you are going through all the pain and suffering without any of the pay-off.

This block of joins:

    join ret in refDateTypesQuery on ed.RefDateTypeId equals ret.RefDateTypeId
    join rdt in refDeliverablesQuery on edr.RefDeliverableId equals rdt.RefDeliverableId
    join upd in updatesQuery on edr.RefDsgnSubmissionTypeId equals upd.UpdateId
    join uqc in updateQCsQuery on upd.UpdateId equals uqc.UpdateId

Every one of those depends ultimately on the outer join for edr, resulting in an output that - even if you put the time in to get the outer join side effects figured out - will negate the effects of DefaultIfEmpty.

You need to re-think your logic.

I suggest breaking this thing down into a series of intermediate queries. Build the query up one step at a time, joining the results in the final stage.

For instance, you have ed as an outer join which is then subjected to an inner join with rdt and further filtered by the properties of rdt in your where clause. Scrape all that out and put it in an intermediate, then join against it later. Do the same with edt: create an intermediate that join the parts. Flatten out the results for use in the final query.

Incidentally, you don't necessarily need to bring all of that data into memory. Even if your data is on different physical servers you can often still get Linq to SQL to talk to them. When you're targeting SQL Server for example you can specify a 3- or 4-part name for the Table attribute to access data in other databases on the same server or databases on linked servers. Might be useful.

Thanks for the well-explained answer. :D – user3579974 Apr 28 '14 at 07:39 — user3579974, Apr 28 '14 at 07:39

Object reference not set to an instance of an object on querying from different lists

1 Answers1