plugins dynamics-crm crm microsoft-dynamics xrm

FetchXML vs Query Expression vs LINQ Query: What should I use here?

I need help from those kind enough to provide help! I am running an instance of Dynamics 365 and I need help deciding whether to use FetchXML, LINQ Query or Query expression to query an entity I have called contract lines....

Let me explain the project:

Inside the contracts entity, I have contract lines that look like this:

The contract lines (summed together) will tell you what to order (and how much to order) for each day of the week, for the entire duration of the contract. The duration of the contract could be 6+ months.. What I want to do is take this information and expand it. So I want to get what the orders would be like for each day of the contract from start of the contract until the end of the contract. Once a contract is created, a record is created in another entity called Unit Orders, like this:

Inside the unit order entity, there is a subgrid of another entity (called the alter unit order entity). This entity has a 1:N relationship to the unit order entity. Just like how the data from the contract entity went into the unit order entity, we want the data from the contract line entity to go into the alter unit orders entity. The trick here is writing an algorithm that processes the data from contract lines into the alter unit orders entity. We are summing up how much is being ordered for AM SNACK, LUNCH and PM SNACK for each day of the contract. Please note that the orders are only filled on weekdays (there are no orders for the weekends). I have already started writing the plugin and I have managed to query the contract line sub-grid from the contract entity. My problem is, I am stuck at that and I need help putting together the algorithm to spit out what I want... here is what I have so far:

using System;
using System.ServiceModel;
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Client;
using Microsoft.Xrm.Sdk.Query;


/// <summary>
/// This plugin takes the data provided in the contract lines and makes Unit Orders.. Inside the unit orders, an Alter Unit Orders table is present.
/// The Alter Unit Orders table describes the daily order for each day in the contract's duration. 
/// </summary>

namespace DCWIMS.Plugins
{
    [CrmPluginRegistration(MessageNameEnum.Update,
    "contract",
    StageEnum.PreOperation,
    ExecutionModeEnum.Synchronous,
    "title",
    "Post-Update Contract",
    1000,
    IsolationModeEnum.Sandbox,
    Image1Name = "PreImage",
    Image1Type = ImageTypeEnum.PreImage,
    Image1Attributes = "title")]
    public class UnitPlugin : IPlugin
    {



        public void Execute(IServiceProvider serviceProvider)
        {
            // Extract the tracing service for use in debugging sandboxed plug-ins.
            // Wil be registering this plugin, thus will need to add tracing service related code.

            ITracingService tracing = (ITracingService)serviceProvider.GetService(typeof(ITracingService));

            //obtain execution context from service provider.
            IPluginExecutionContext context = (IPluginExecutionContext)
                serviceProvider.GetService(typeof(IPluginExecutionContext));

            // The InputParameters colletion contains all the data passed in the message request.
            if (context.InputParameters.Contains("Target") &&
                context.InputParameters["Target"] is Entity)
            {
                //obtain the target entity from the input parameters.
                Entity entity = (Entity)context.InputParameters["Target"];

                //verify that the target entity represents the the contract entity and is in an active state
                if (entity.LogicalName != "contract" && entity.GetAttributeValue<OptionSetValue>("statecode").Value != 0)
                    return;

                //obtain the organization service for web service calls.

                IOrganizationServiceFactory serviceFactory =
                        (IOrganizationServiceFactory)serviceProvider.GetService(typeof(IOrganizationServiceFactory));

                IOrganizationService service = serviceFactory.CreateOrganizationService(context.UserId);

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

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

                //Get all weekdays in the contract duration
                Eachday range = new Eachday();
                var weekdays = range.WeekDay(startDate, endDate);       //weekdays list

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


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

                //AM SNACK


                //LUNCH


                //PM SNACK

                var am_list = new List<int>();
                var lunch_list = new List<int>();
                var pm_list = new List<int>();

                foreach(var day in weekdays)
                {
                    var alterunit = new Entity("new_alterunitorder");
                    alterunit.Attributes.Add("new_orderdate", DateTime.Parse(day));

                    switch (day.Split(',')[0])
                    {
                        case "Monday":
                            alterunit.Attributes.Add("new_amsnack", am_list[0]);
                            alterunit.Attributes.Add("new_lunch", lunch_list[0]);
                            alterunit.Attributes.Add("new_pmsnack", pm_list[0]);
                            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 ({day.Split(',')})");
                            break;

                    }
                    alterunit.Attributes.Add("new_name", contractNumber);
                    service.Create(alterunit);
                }

                }

                catch (FaultException<OrganizationServiceFault> ex)
                {
                    throw new InvalidPluginExecutionException("An error occured.. Phil is responsible.", ex);
                }

                catch (Exception ex)
                {
                    tracing.Trace("An Error Occured: {0}", ex.ToString());
                    throw;

                }
            }
        }
    }
}

Here is the code I am using to fetch all weekdays for the contract duration:

public class Eachday
{
    public List<string> WeekDay(DateTime from, DateTime thru)
    {
        List<string> days_list = new List<string>();
        for (var day = from.Date; day.Date <= thru.Date; day = day.AddDays(1))
        {
            days_list.Add(day.ToLongDateString());
            if (day.DayOfWeek == DayOfWeek.Sunday || day.DayOfWeek == DayOfWeek.Saturday)
                days_list.Remove(day.ToShortDateString());
        }

        return days_list;
    }

I added this in the try block to get a list of weekdays:

                //Get Contract StartDate
                DateTime startDate = (DateTime)entity["activeon"];

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

                //Get all weekdays in the contract duration
                Eachday range = new Eachday();
                var weekdays = range.WeekDay(startDate, endDate);

I need to put the weekly order quantities into a list for each day of the week. Essentially, I am going to have 3 lists. Then I would use the lists in a switch to spit out the totals!!

                foreach(var day in weekdays)
                {
                    var alterunit = new Entity("new_alterunitorder");
                    alterunit.Attributes.Add("new_orderdate", DateTime.Parse(day));

                    switch (day.Split(',')[0])
                    {
                        case "Monday":
                            alterunit.Attributes.Add("new_amsnack", am_list[0]);
                            alterunit.Attributes.Add("new_lunch", lunch_list[0]);
                            alterunit.Attributes.Add("new_pmsnack", pm_list[0]);
                            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 ({day.Split(',')})");
                            break;

                    }
                    alterunit.Attributes.Add("new_name", contractNumber);
                    service.Create(alterunit);
                }

Solution

I see you are debuting with plugins. I find the QueryExpression pretty complicated in general. Instead you should use a fetchXml. In short, fetchXml is the language for queries in Dynamics. All the views use it. You can generate fetchXml in the advanced find window.

In term of performance, it's also the best. To my knowledge, there is no other options, in plugins, to calculate a sum without retrieve the whole data in memory.

Here's a example I found in MSDN

string estimatedvalue_sum = @"  <fetch distinct='false' mapping='logical' aggregate='true'> 
    <entity name='opportunity'> 
       <attribute name='estimatedvalue' alias='estimatedvalue_sum' aggregate='sum' /> 
    </entity>  </fetch>";

EntityCollection estimatedvalue_sum_result =
_serviceProxy.RetrieveMultiple(new FetchExpression(estimatedvalue_sum));

foreach (var c in estimatedvalue_sum_result.Entities) {
    decimal aggregate7 = ((Money)((AliasedValue)c["estimatedvalue_sum"]).Value).Value;
    System.Console.WriteLine("Sum of estimated value of all opportunities: " + aggregate7);

}

Source: https://msdn.microsoft.com/en-us/library/gg309565.aspx#sum

Also, here's general tips:

_serviceProxy is the service variable
In the target, you will find what has changed
In the pre image, you will find what was before your change
Post images are useful when you trigger different plugins on the same event, and I always recommend to avoid that. Keep everything in the same class, it gives you more control.

First Revision: You could create an entity containing the sum for each day. Replace "(Money)" by the right type. Not sure if it's currency or int.

foreach(var unit in unitsum_am_result.Entities)
{
    decimal mondaySum = ((Money)((AliasedValue)unit["new_mondayunits_amsum"]).Value).Value;
    decimal tuesdaySum = ((Money)((AliasedValue)unit["new_tuesdayunits_amsum"]).Value).Value;
    decimal wednesdaySum = ((Money)((AliasedValue)unit["new_unitswednesday_amsum"]).Value).Value;
    decimal thursdaySum = ((Money)  ((AliasedValue)unit["new_unitsthursday_amsum"]).Value).Value;
    decimal fridaySum = ((Money)        ((AliasedValue)unit["new_unitsfriday_amsum"]).Value).Value;

    var unitOrder = new Entity("new_unit_order");
    unitOrder.Attributes.Add("new_orderSum", mondaySum);
    unitOrder.Attributes.Add("new_date", mondayDate);
    _serviceProxy.Create(unitOrder);

    // Do the same for the other sums/days
}

Second revision To get a lookup value from the contract, the best option is to create a pre-image, on your plugin, using the plugin registration tool. In the image, select the fields you need to retrieve from the entity. It works like the target.

PluginExecutionContext.PreEntityImages != null && PluginExecutionContext.PreEntityImages.Count >= 1 ? PluginExecutionContext.PreEntityImages.FirstOrDefault().Value : null;

Be aware, if on your update event you do not modify the Unit, you will find the value in the image. If you do update the Unit, you will find the old value in the image and the new value in the target. So you always have to check both.