D365 CE: difference between OrganizationServiceContext and QueryExpression through Service?

Question

I'm trying to execute a LINQ query within a plugin, using the OrganizationServiceContext, to retrieve some quotes. On these quotes, I'm using .Select() to only select the value for the field cgk_totalnetprice, as shown below:

quotes = OrganizationServiceContext.QuoteSet
   .Where(_ =>
      _.OpportunityId != null &&
      _.OpportunityId.Id == opportunityId &&
      _.QuoteId != currentQuote.Id &&
      (_.StatusCode.Value == (int)Quote_StatusCode.Won || _.StatusCode.Value == (int)Quote_StatusCode.WonOrder) &&
      _.cgk_quotetypecode != null &&
      (_.cgk_quotetypecode.Value == (int)QuoteTypeCode.Regular || _.cgk_quotetypecode.Value == (int)QuoteTypeCode.ServiceUnderWarranty))
   .Select(x => new Quote() { Id = x.Id, cgk_totalnetprice = x.cgk_totalnetprice})
   .ToList();

However, when retrieving those quotes, the context does not return a value for all except one quote (and it is not the quote that triggered the update in the first place, but just a random one that was not updated at all)

Weird part: when I rewrite the query to a QueryExpression, everything works perfectly:

QueryExpression qe = new QueryExpression("quote");
//Exclude current quote
qe.Criteria.AddCondition("quoteid", ConditionOperator.NotEqual, currentQuote.Id);
//Opportunity
qe.Criteria.AddCondition("opportunityid", ConditionOperator.NotNull);
qe.Criteria.AddCondition("opportunityid", ConditionOperator.Equal, opportunityId);
//State-Status
FilterExpression statusFilter = new FilterExpression(LogicalOperator.Or);
statusFilter.AddCondition("statuscode", ConditionOperator.Equal, (int)Quote_StatusCode.Won);
statusFilter.AddCondition("statuscode", ConditionOperator.Equal, (int)Quote_StatusCode.WonOrder);
qe.Criteria.AddFilter(statusFilter);
//QuoteType
qe.Criteria.AddCondition("cgk_quotetypecode", ConditionOperator.NotNull);
FilterExpression typeFilter = new FilterExpression(LogicalOperator.Or);
typeFilter.AddCondition("cgk_quotetypecode", ConditionOperator.Equal, (int)QuoteTypeCode.Regular);
typeFilter.AddCondition("cgk_quotetypecode", ConditionOperator.Equal, (int)QuoteTypeCode.ServiceUnderWarranty);
qe.Criteria.AddFilter(typeFilter);
qe.ColumnSet = new ColumnSet("quoteid", "cgk_totalnetprice");
quotes = this.OrganizationService.RetrieveMultiple(qe).Entities.Cast<Quote>().ToList();

What could cause this difference between OrganizationServiceContext and OrganizationService + QueryExpression??

Henk van Boeijen · Answer 1 · 2021-11-09T07:53:34.843

Queries on OrganizationServiceContext rely on LINQ for CRM, which in turn translates LINQ expressions into QueryExpression objects. LINQ for CRM comes with a few weaknesses:

it does not implement all capabilities of the underlying QueryExpression,
it only supports a limited set of LINQ constructs (see MS Docs),
in some cases it creates incorrect queries,
query processing is approx. 10% slower.

Your query looks pretty straightforward, yet it fails. Maybe you can leave the line _.cgk_quotetypecode != null && out. I guess it is not needed and combined with the subsequent filtering on the same attribute it may trick the LINQ parser into constructing the wrong filter and/or conditions.

Another option is to materialize the LINQ query first and then select the columns needed. Of course this will lead to a select *, but it's often worth trying while troubleshooting.

E.g. you could write:

   .ToArray()
   .Select(x => new Quote() { Id = x.Id, cgk_totalnetprice = x.cgk_totalnetprice});

Working with Dynamics CRM/365 CE I learned to avoid LINQ for CRM. Instead I use a bunch of extension methods allowing me to create QueryExpression queries in a much less verbose way.

Final suggestion: in some cases a filter's LogicalOperator.Or can be replaced by ConditionOperator.In or ConditionOperator.Between. Doing so the construct

//State-Status
FilterExpression statusFilter = new FilterExpression(LogicalOperator.Or);
statusFilter.AddCondition("statuscode", ConditionOperator.Equal, (int)Quote_StatusCode.Won);
statusFilter.AddCondition("statuscode", ConditionOperator.Equal, (int)Quote_StatusCode.WonOrder);
qe.Criteria.AddFilter(statusFilter);

can simply be replaced by this oneliner:

qe.Criteria.AddCondition("statuscode", ConditionOperator.In, (int)Quote_StatusCode.Won, (int)Quote_StatusCode.WonOrder);

score 0 · Answer 2 · answered Nov 18 '21 at 17:32

I generally use the query syntax for Dynamics LINQ queries. I'd suggest standard query troubleshooting - start with no conditions and add them one-by-one.

Below is an idea of how my query would look. I'd keep adding / modifying the where clauses until the query returned what I expected. We could use && operators instead of multiple where clauses, but I find that having the multiple where clauses often makes commenting and uncommenting easier.

using(var ctx = new OrganizationServiceContext(ctx))
{
    var x = from q in ctx.CreateQuery<Quote>()
            where q.QuoteId ! = currentQuote.Id
            where q.OpportunityId != null
            where q.cgk_quotetypecode != null 
            where q.cgk_quotetypecode == QuoteTypeCode.Regular || QuoteTypeCode.ServiceUnderWarranty    
            select new Quote
            {
                Id = q.Id,
                cgk_totalnetprice = x.cgk_totalnetprice
            };
    var quotes = x.ToList();
}

D365 CE: difference between OrganizationServiceContext and QueryExpression through Service?

2 Answers2