What does EntityCollection return if FetchXML (AGG

Question

I am running a FetchXML query (aggregate='true' for the fetch and aggregate='sum' for all attributes) and passing it to RetreiveMultiple in my C# plugin code. What if the Fetchxml didn't find anything, what does service.RetrieveMultiple retrieve in this case? In the debugger, there is a NullReference exception.

FetchXML Builder returns this:

I need to handle a case where one of the meals in the contract lines is missing... In my plugin I have three Fetchxml expressions that Retrieve data from the contract lines entity (One for each meal of the day, AM SNACKS/LUNCH/PM SNACKS). If one of the FetchXMl entities doesn't have data to retrieve, the plugin does not produce anything at all... even though one of the FetchXML queries returned some results.. I simply want to place zeros whenever there is no data returned from anyone of the three FETCHXML Queries... PLEASE NOTE THAT IF I INCLUDE ALL THREE MEALS IN THE CONTRACT LINES, THE PLUGIN WORKS FINE!! Here is my code:

try
                {
                    //Get Contract StartDate                   
                    DateTime startDate = (DateTime)PreImage["activeon"];

                    //Get Contract EndDate
                     DateTime endDate = (DateTime)PreImage["expireson"];

                    //Create an instance of the range class
                    Eachday range = new Eachday();

                    //use Weekday method of range class to get a weekdays list
                    var weekdays = range.WeekDay(startDate, endDate);                               

                    //Get Contract Number
                    string contractNumber = (string)PreImage["contractnumber"];

                    //Get Unit Order Lookup Id
                    EntityReference unitOrder = (EntityReference)PreImage.Attributes["new_unitorderid"];
                    var unitOrderId = unitOrder.Id;


                    //Query and aggregate each Weekday's order for the 3 different meal times...

                    //AM SNACK FetchXML Query.. string is passed to service.RetrieveMultiple();
                    string unitsum_am = @"  <fetch aggregate='true' distinct='false' >
                        <entity name='contract' >
                            <link-entity name='contractdetail' from = 'contractid' to = 'contractid' >
                                <attribute name='new_mondayunits' alias='new_mondayunits_amsum' aggregate='sum' />
                                <attribute name='new_tuesdayunits' alias='new_tuesdayunits_amsum' aggregate='sum' />
                                <attribute name='new_unitswednesday' alias='new_unitswednesday_amsum' aggregate='sum' />                       
                                <attribute name='new_unitsthursday' alias='new_unitsthursday_amsum' aggregate='sum' />                            
                                <attribute name='new_unitsfriday' alias='new_unitsfriday_amsum' aggregate='sum' />
                                <filter type='and' >
                                    <condition value='100000001' attribute='new_servingtime' operator= 'eq' />                                       
                                    <condition value='0' attribute='statecode' operator= 'eq' />
                                    <condition value='" + targetId + @"' attribute='contractid' operator= 'eq' />
                                </filter >

                            </link-entity> 
                         </entity >
                     </fetch>";


                    //Retrieve a collection of records
                    EntityCollection unitsum_am_result =
                        service.RetrieveMultiple(new FetchExpression(unitsum_am));

                    //AM SNACKS list for the week
                    var am_list = new List<int>();

                    //iterate through all records and get the sum for each day
                    //place the sums into the am_list
                    if (unitsum_am_result.Entities.Count == 0)
                    {
                        am_list.AddRange(Enumerable.Repeat(0, 5));
                    }
                    else
                    {
                        foreach (var unit in unitsum_am_result.Entities)
                        {
                            var mondaysum = ((int)((AliasedValue)unit["new_mondayunits_amsum"]).Value);
                            am_list.Add(mondaysum);
                            var tuesdaysum = ((int)((AliasedValue)unit["new_tuesdayunits_amsum"]).Value);
                            am_list.Add(tuesdaysum);
                            var wednesdaysum = ((int)((AliasedValue)unit["new_unitswednesday_amsum"]).Value);
                            am_list.Add(wednesdaysum);
                            var thursdaysum = ((int)((AliasedValue)unit["new_unitsthursday_amsum"]).Value);
                            am_list.Add(thursdaysum);
                            var fridaysum = ((int)((AliasedValue)unit["new_unitsfriday_amsum"]).Value);
                            am_list.Add(fridaysum);
                        }
                    }





                    //LUNCH FetchXML Query.. string is passed to service.RetrieveMultiple();
                    string unitsum_lunch = @"   <fetch aggregate='true' distinct='false' >
                        <entity name='contract' >
                            <link-entity name='contractdetail' from = 'contractid' to = 'contractid' >
                                <attribute name='new_mondayunits' alias='new_mondayunits_lunchsum' aggregate='sum' />
                                <attribute name='new_tuesdayunits' alias='new_tuesdayunits_lunchsum' aggregate='sum' />
                                <attribute name='new_unitswednesday' alias='new_unitswednesday_lunchsum' aggregate='sum' />                       
                                <attribute name='new_unitsthursday' alias='new_unitsthursday_lunchsum' aggregate='sum' />                            
                                <attribute name='new_unitsfriday' alias='new_unitsfriday_lunchsum' aggregate='sum' />
                                <filter type='and' >
                                    <condition value='100000002' attribute='new_servingtime' operator= 'eq' />                                       
                                    <condition value='0' attribute='statecode' operator= 'eq' />
                                    <condition value='" + targetId + @"' attribute='contractid' operator= 'eq' />
                                </filter >                                          
                            </link-entity> 
                         </entity >
                     </fetch>";


                    //Retrieve a collection of records
                    EntityCollection unitsum_lunch_result =
                        service.RetrieveMultiple(new FetchExpression(unitsum_lunch));

                    //Lunch meals list for the week
                    var lunch_list = new List<int>();

                    //iterate through all records and get the sum for each day
                    //place the sums into the lunch_list
                    if (unitsum_lunch_result.Entities.Count == 0)
                    {
                        lunch_list.AddRange(Enumerable.Repeat(0, 5));
                    }
                    else
                    {
                        foreach (var unit in unitsum_lunch_result.Entities)
                        {
                            var mondaysum = ((int)((AliasedValue)unit["new_mondayunits_lunchsum"]).Value);
                            lunch_list.Add(mondaysum);
                            var tuesdaysum = ((int)((AliasedValue)unit["new_tuesdayunits_lunchsum"]).Value);
                            lunch_list.Add(tuesdaysum);
                            var wednesdaysum = ((int)((AliasedValue)unit["new_unitswednesday_lunchsum"]).Value);
                            lunch_list.Add(wednesdaysum);
                            var thursdaysum = ((int)((AliasedValue)unit["new_unitsthursday_lunchsum"]).Value);
                            lunch_list.Add(thursdaysum);
                            var fridaysum = ((int)((AliasedValue)unit["new_unitsfriday_lunchsum"]).Value);
                            lunch_list.Add(fridaysum);
                        }
                    }



                    //PM SNACK FetchXML Query.. string is passed to service.RetrieveMultiple();
                    string unitsum_pm = @"  <fetch aggregate='true' distinct='false' >
                        <entity name='contract' >
                            <link-entity name='contractdetail' from = 'contractid' to = 'contractid' >
                                <attribute name='new_mondayunits' alias='new_mondayunits_pmsum' aggregate='sum' />
                                <attribute name='new_tuesdayunits' alias='new_tuesdayunits_pmsum' aggregate='sum' />
                                <attribute name='new_unitswednesday' alias='new_unitswednesday_pmsum' aggregate='sum' />                       
                                <attribute name='new_unitsthursday' alias='new_unitsthursday_pmsum' aggregate='sum' />                            
                                <attribute name='new_unitsfriday' alias='new_unitsfriday_pmsum' aggregate='sum' />
                                <filter type='and' >
                                    <condition value='100000003' attribute='new_servingtime' operator= 'eq' />                                       
                                    <condition value='0' attribute='statecode' operator= 'eq' />
                                    <condition value='" + targetId + @"' attribute='contractid' operator= 'eq' />
                                </filter >                                          
                            </link-entity> 
                         </entity >
                     </fetch>";

                    //Retrieve a collection of records
                    EntityCollection unitsum_pm_result =
                        service.RetrieveMultiple(new FetchExpression(unitsum_pm));


                    //PM SNACK list for the week
                    var pm_list = new List<int>();

                    //iterate through all records and get the sum for each day
                    //place the sums into the pm_list

                    if (unitsum_pm_result.Entities.Count == 0)
                    {
                        pm_list.AddRange(Enumerable.Repeat(0, 5));
                    }
                    else
                    {
                        foreach (var unit in unitsum_pm_result.Entities)
                        {
                            var mondaysum = ((int)((AliasedValue)unit["new_mondayunits_pmsum"]).Value);
                            pm_list.Add(mondaysum);
                            var tuesdaysum = ((int)((AliasedValue)unit["new_tuesdayunits_pmsum"]).Value);
                            pm_list.Add(tuesdaysum);
                            var wednesdaysum = ((int)((AliasedValue)unit["new_unitswednesday_pmsum"]).Value);
                            pm_list.Add(wednesdaysum);
                            var thursdaysum = ((int)((AliasedValue)unit["new_unitsthursday_pmsum"]).Value);
                            pm_list.Add(thursdaysum);
                            var fridaysum = ((int)((AliasedValue)unit["new_unitsfriday_pmsum"]).Value);
                            pm_list.Add(fridaysum);
                        }
                    }

                    //iterate through entire contract duration
                    foreach(var day in weekdays)
                    {
                        Entity alterunit = new Entity("new_alterunitorder");                   //create new alter unit record
                        alterunit.Attributes.Add("new_orderdate", DateTime.Parse(day));        //set the day field
                        var currentday = day.Split(',')[0];                                    //current day

                        switch (currentday)
                        {
                            case "Monday":
                                alterunit.Attributes.Add("new_amsnack", am_list[0]);        //set the total am snacks
                                alterunit.Attributes.Add("new_lunch", lunch_list[0]);       //set the total lunch meals
                                alterunit.Attributes.Add("new_pmsnack", pm_list[0]);        //set the total pm snacks
                                break;
                            case "Tuesday":
                                alterunit.Attributes.Add("new_amsnack", am_list[1]);
                                alterunit.Attributes.Add("new_lunch", lunch_list[1]);
                                alterunit.Attributes.Add("new_pmsnack", pm_list[1]);
                                break;
                            case "Wednesday":
                                alterunit.Attributes.Add("new_amsnack", am_list[2]);
                                alterunit.Attributes.Add("new_lunch", lunch_list[2]);
                                alterunit.Attributes.Add("new_pmsnack", pm_list[2]);
                                break;
                            case "Thursday":
                                alterunit.Attributes.Add("new_amsnack", am_list[3]);
                                alterunit.Attributes.Add("new_lunch", lunch_list[3]);
                                alterunit.Attributes.Add("new_pmsnack", pm_list[3]);
                                break;
                            case "Friday":
                                alterunit.Attributes.Add("new_amsnack", am_list[4]);
                                alterunit.Attributes.Add("new_lunch", lunch_list[4]);
                                alterunit.Attributes.Add("new_pmsnack", pm_list[4]);
                                break;
                            default:
                                Console.WriteLine($"An unexpected value ({currentday})");
                                break;

                        }

                        alterunit.Attributes.Add("new_name", contractNumber);                   //set the record name

                        //set the unit order record relation
                        alterunit.Attributes["new_orderlineid"] =                               
                            new EntityReference("new_units", unitOrderId);             
                        service.Create(alterunit);                                              //create the record
                    }



                } 

Here is the result of debugging:

There is also an Exception thrown on line 190:

Answer 1

It will return a fully qualified EntityCollection, but the Entities property will have zero records in the collection.

Answer 2

The entity collection does in fact return a fully qualified EntityCollection and the entity count will be zero. HOWEVER, It seems that when using the aggregate 'sum', if the collection is empty the platform throws an error. It doesn't happen with 'count'. To fix it, I did:

//iterate through all records and get the sum for each day
                    //place the sums into the am_list
                    foreach(var unit in unitsum_am_result.Entities)
                    {
                        try
                        {
                            am_list.Add(((int)((AliasedValue)unit["new_mondayunits_amsum"]).Value));
                            am_list.Add(((int)((AliasedValue)unit["new_tuesdayunits_amsum"]).Value));
                            am_list.Add(((int)((AliasedValue)unit["new_unitswednesday_amsum"]).Value));
                            am_list.Add(((int)((AliasedValue)unit["new_unitsthursday_amsum"]).Value)); 
                            am_list.Add(((int)((AliasedValue)unit["new_unitsfriday_amsum"]).Value));
                        }
                        catch
                        {
                            am_list.AddRange(Enumerable.Repeat(0, 5));
                        }
                    }

Answer 3

Neither null nor empty.

You can use the below snippet to identify the resultset:

if (unitsum_am_result.Entities.Count == 0)
{
    am_list.AddRange(Enumerable.Repeat(0, 5));
}
else
{
    //foreach....
}