collection has items with null - C# and linq

Question

I am getting the error on the subject of this question when the code passes the else if (sortColumn == "DateSubmitted")

This is the entire code I am having problems with.

        private object GetContacts(int Month, int Year, string sortColumn = "FacilityName", bool asc = true, bool byRegion = true, bool myContacts = false)
    {
        var userID = GetCurrentUserID();

        sharedmsdbEntity db = new sharedmsdbEntity();

        var user = (from e in db.smsEmployees where e.userID == userID select e).FirstOrDefault();

        //displaySelector.Visible = user.userJobTitle == "RM";

        IQueryable<smsCustomerContact> starter;

        var emptyCustomers = (from c in db.smsCustomers
                              where
                              !myContacts
                              && c.custActive
                              && !(from cc in c.smsCustomerContacts where !cc.isDeleted && cc.cntActive && (cc.cntIsMRI || cc.cntIsPETCT || cc.cntIsCT || cc.cntIsFFDM) select cc).Any()
                              select c);

        if ((user.userAdmin.HasValue && user.userAdmin.Value) || (user.userRegionID.HasValue && user.smsRegion.Name == "Corporate"))
        {
            starter = (from c in db.smsCustomerContacts where c.smsCustomer != null && !c.isDeleted && c.cntActive && ((c.cntIsMRI && c.smsCustomer.IsMRIBillingActive) || (c.cntIsPETCT && c.smsCustomer.IsPETBillingActive) || (c.cntIsCT && c.smsCustomer.ISCTBillingActive) || (c.cntIsFFDM && c.smsCustomer.ISFFDMBillingActive)) select c);
        }
        else
        {
            if (byRegion)
            {
                starter = (from c in db.smsCustomerContacts where c.smsCustomer != null && !c.isDeleted && c.cntActive && ((c.cntIsMRI && c.smsCustomer.IsMRIBillingActive) || (c.cntIsPETCT && c.smsCustomer.IsPETBillingActive) || (c.cntIsCT && c.smsCustomer.ISCTBillingActive) || (c.cntIsFFDM && c.smsCustomer.ISFFDMBillingActive)) && user.userRegionID.HasValue && c.smsCustomer.custRegionID.HasValue && c.smsCustomer.custRegionID == user.userRegionID select c);
                emptyCustomers = (from c in emptyCustomers where c.custRegionID.HasValue && user.userRegionID.HasValue && c.custRegionID == user.userRegionID select c);
            }
            else
            {
                starter = (from c in db.smsCustomerContacts where c.smsCustomer != null && !c.isDeleted && c.cntActive && ((c.cntIsMRI && c.smsCustomer.IsMRIBillingActive) || (c.cntIsPETCT && c.smsCustomer.IsPETBillingActive) || (c.cntIsCT && c.smsCustomer.ISCTBillingActive) || (c.cntIsFFDM && c.smsCustomer.ISFFDMBillingActive)) && c.cntCreatedBy == userID select c);
            }
        }
        if (myContacts)
        {
            starter = (from c in starter where (from m in c.smsEmployeeContacts where m.empID == userID select m).Any() select c);
        }

        if ((from c in starter where (from s in c.smsContactFormSents where s.completed orderby s.sentDateTime descending select s.ACPForm).FirstOrDefault().dateAccepted.Value.Year < Year select c).Any())
        {
            btnPreviousMonth.Style.Remove("display");
        }
        else
        {
            btnPreviousMonth.Style.Add("display", "none");
        }

        bool isCurrentYear = SiteFunctions.Now().Year == Year;

        starter = (from c in starter
                   where
                   (c.cntActive
                    || ((from s in c.smsContactFormSents orderby s.sentDateTime descending select s.ACPForm).FirstOrDefault().dateAccepted.HasValue && (from s in c.smsContactFormSents orderby s.sentDateTime descending select s.ACPForm).FirstOrDefault().dateAccepted.Value.Year == Year))
                   && ((isCurrentYear && c.smsCustomer.custActive
                        && ((c.smsCustomer.custType != 2 && c.cntIsPETCT && c.smsCustomer.IsPETBillingActive) || ((c.cntIsMRI && c.smsCustomer.IsMRIBillingActive) || (c.cntIsCT && c.smsCustomer.ISCTBillingActive) || (c.cntIsFFDM && c.smsCustomer.ISFFDMBillingActive)))) //Only PET/CT is not allowed with Retail
                   || (((from s in c.smsContactFormSents orderby s.sentDateTime descending select s.ACPForm).FirstOrDefault().dateAccepted.HasValue && (from s in c.smsContactFormSents orderby s.sentDateTime descending select s.ACPForm).FirstOrDefault().dateAccepted.Value.Year == Year)
                        || (from s in c.smsContactFormSents orderby s.sentDateTime descending select s).FirstOrDefault().sentDateTime.Year == Year)
                        && c.smsCustomer.custActive
                        && ((c.smsCustomer.custType != 2 && c.cntIsPETCT && c.smsCustomer.IsPETBillingActive) || ((c.cntIsMRI && c.smsCustomer.IsMRIBillingActive) || (c.cntIsCT && c.smsCustomer.ISCTBillingActive) || (c.cntIsFFDM && c.smsCustomer.ISFFDMBillingActive)))) //Only PET/CT is not allowed with Retail
                   select c);

        var data = (from c in starter
                    select new ACPListObject()
                    {
                        contact = c,
                        Year = Year,
                        LastSentDate = (from s in c.smsContactFormSents orderby s.sentDateTime descending select s.sentDateTime).FirstOrDefault(),
                        LastSubmit = (from s in c.smsContactFormSents orderby s.sentDateTime descending select s.ACPForm).FirstOrDefault(),
                        lastFormComplete = (from s in c.smsContactFormSents orderby s.sentDateTime descending select s).FirstOrDefault(),
                        isMyContact = (from m in c.smsEmployeeContacts where m.empID == userID select m).Any()
                    });

        if (isCurrentYear)
        {
            var custs = (from c in emptyCustomers.ToList()
                         where c.ISCTBillingActive || c.ISFFDMBillingActive || c.IsMRIBillingActive || (c.IsPETBillingActive && c.custType != 2)
                         select new ACPListObject()
                         {
                             contact = new smsCustomerContact() { cntID = -1, smsCustomer = c, cntActive = true },
                             Year = Year,
                             LastSentDate = new DateTime(),
                             LastSubmit = (ACPForm)null,
                             lastFormComplete = (smsContactFormSent)null,
                             isMyContact = false
                         });

            var combined = data.ToList();

            combined.AddRange(custs);

            data = combined.AsQueryable();                

        }

        if (sortColumn == "Contact")
        {
            if (asc)
            {
                data = data.OrderBy(o => o.contact.cntFirstName + " " + o.contact.cntLastName);                    
            }
            else
            {
                data = data.OrderByDescending(o => o.contact.cntFirstName + " " + o.contact.cntLastName);
            }
        }
        else if (sortColumn == "City")
        {
            if (asc)
            {
                data = data.OrderBy(o => o.contact.smsCustomer.custCity);
            }
            else
            {
                data = data.OrderByDescending(o => o.contact.smsCustomer.custCity);
            }
        }
        else if (sortColumn == "State")
        {
            if (asc)
            {
                data = data.OrderBy(o => o.contact.smsCustomer.custState);
            }
            else
            {
                data = data.OrderByDescending(o => o.contact.smsCustomer.custState);
            }
        }
        else if (sortColumn == "DateSent")
        {
            if (asc)
            {
                data = data.OrderBy(o => o.LastSentDate);
            }
            else
            {
                data = data.OrderByDescending(o => o.LastSentDate);
            }
        }
        else if (sortColumn == "DateSubmitted")
        {
            if (asc)
            {   
              data = data.OrderBy(o => o.lastFormComplete.ACPForm.dateAccepted);
            }
            else
            {
                data = data.OrderByDescending(o => o.lastFormComplete.ACPForm.dateAccepted);                    
            }
        }
        else if (sortColumn == "FacilityName")
        {
            if (asc)
            {
                data = data.OrderBy(o => o.contact.smsCustomer.custFacilityName);
            }
            else
            {
                data = data.OrderByDescending(o => o.contact.smsCustomer.custFacilityName);
            }
        }
        else if (sortColumn == "BillingCode")
        {
            if (asc)
            {
                return data.ToList().OrderBy(o => SiteFunctions.GetBillingCodes(o.contact, false));
            }
            else
            {
                return data.ToList().OrderByDescending(o => SiteFunctions.GetBillingCodes(o.contact, false));
            }
        }
        else if (sortColumn == "MyContact")
        {
            if (asc)
            {
                data = data.OrderBy(o => o.isMyContact);
            }
            else
            {
                data = data.OrderByDescending(o => o.isMyContact);
            }
        }

        return data.ToList();           
    }

Any help is kindly appreciated.

Thank you

`lastFormComplete` seems to be null. How you are populating that? — Rahul Singh, Nov 21 '14 at 18:53
Acutally Jon Skeet there's nothing on that page that shows you can't parse content of an IQuerabyable in the manner shown. — JWP, Nov 21 '14 at 19:03
I updated the code to include more visibility to what happens before the else if statement. Thank you. — watermelon_heart, Nov 21 '14 at 19:10
to Rahul Singh: lastFormComplete is populated like this: public class ACPListObject { public smsContactFormSent lastFormComplete { get; set; } — watermelon_heart, Nov 21 '14 at 20:14

JWP · Answer 1 · 2014-11-21T19:48:27.323

-1

IQueryables by default have no data in them, you have to enumerate it before you attempt to access the data. Here's how you can do that.

var results = combined.ToList();

edited Nov 21 '14 at 19:48

answered Nov 21 '14 at 18:55

JWP

5,969
3
39
65

That would also give a different exception. – BradleyDotNET Nov 21 '14 at 18:56
It isn't that the `Count` is zero, it's that the collection has items with null `lastFormComplete` properties. – Mike Christensen Nov 21 '14 at 18:56
Ok fair enough updated. code. – JWP Nov 21 '14 at 18:58
Hi, is there a chance that someone can explain me a bit why the error is happening? I have not used linq in the past. many thanks – watermelon_heart Nov 21 '14 at 19:29
Ya, the reason is that IQueryable only contains the query and not the content. You have to execute the content to get it to work. Enumeration causes the expression tree associated with an IQueryable object to be executed. To enumarate it simply append the ToList() method or put it into a ForEach or some other thing that enumerates the list. Linq only executes the query when enumeration happens. – JWP Nov 21 '14 at 19:43
Why it works for other columns? that is confusing to me. – watermelon_heart Nov 21 '14 at 20:26
If I click the "Contact" column or any other column, then I do not get the error. – watermelon_heart Nov 21 '14 at 20:33
I am really at a lost. I do not understand the part of appending the ToList() method to the data variable. If you see in the code I pasted, that is being done in the return. return data.ToList() – watermelon_heart Nov 21 '14 at 20:37
I added the ToList() method, and it gives me 416 enumerated results: else if (sortColumn == "DateSubmitted") { if (asc) { data.ToList(); data = data.OrderBy(o => o.lastFormComplete.ACPForm.dateAccepted); } else { data.ToList(); data = data.OrderByDescending(o => o.lastFormComplete.ACPForm.dateAccepted); } } but before leaving the function, this part: return data.ToList(); -- gives the error of null object – watermelon_heart Nov 21 '14 at 20:59
The reason you are getting the exception is because the variable data must be out of scope from where you've set it's value... in fact I copied all the code you've shown put it into a new class and indeed I found the last "}" before the return statement not to have a matching parent, I can't see all of your code but I recommend you single step through the code to find out why and how it has data, then it disappears as you've indicated. – JWP Nov 21 '14 at 21:42
I have tried to add a where not null on the linq query but that does not help. I posted the code for the entire routine to the first comment I made on this question, if you don't mind, and have the opportunity, please check it out. It is not difficult, I know this, but not being familiar with linq makes me take longer to understand. Thank you for your help. – watermelon_heart Nov 21 '14 at 22:14
before this line: data = data.OrderBy(o => o.lastFormComplete.ACPForm.dateAccepted); data contains 416 records, once it passes the orderby dateAccepted, is when I receive d†he error:Object reference not set to an instance of an object. – watermelon_heart Nov 21 '14 at 23:06
this is driving me insane – watermelon_heart Nov 21 '14 at 23:41

collection has items with null - C# and linq

1 Answers1