azure-functionscrmdynamics-crm-sdk

CRMSDK QueryExpression didn't return all the related attributes


I have one Azure Function, it is built with crmsdk and framework 4.6.2. The QueryExpression query returns two fields but misses the other three attributes, two fields contain correct information. I converted QueryExpression to FetchXML and use that in XrmTool box, it retrieves all data and field name are all correct. I tried different CRM environment and QueryExpression gets all attributes, it seems like environment related. I then checked the data and all the five attributes have value. Has anyone seen such strange issue? Thanks.

I expected queryexpression returns all attributes.

    public IEnumerable<ReadyDocketLineDto> GetDocketLinesToBeProcessed()
        {
            var qryDocketLine = new QueryExpression(company_cpseventdocketline.EntityLogicalName);

            #region get docket line
            qryDocketLine.ColumnSet.AddColumns(
                company_cpseventdocketline.Fields.Id,
                company_cpseventdocketline.Fields.company_docketlinetypecode,
                company_cpseventdocketline.Fields.company_paymentclassificationid,
                company_cpseventdocketline.Fields.company_name,
                company_cpseventdocketline.Fields.company_amountincludingGST,
                company_cpseventdocketline.Fields.company_amountexcludingGST,
                company_cpseventdocketline.Fields.company_cpseventdocketid,
                company_cpseventdocketline.Fields.CreatedBy,
                company_cpseventdocketline.Fields.company_recharge_nonbillable,
                company_cpseventdocketline.Fields.CreatedOn,
                company_cpseventdocketline.Fields.company_classificationreason
                );

            qryDocketLine.Criteria.AddCondition(company_cpseventdocketline.Fields.StatusCode, ConditionOperator.Equal, (int)company_cpseventdocketline_StatusCode.Ready);
            qryDocketLine.Criteria.AddCondition(company_cpseventdocketline.Fields.company_cpseventdocketid, ConditionOperator.NotNull);
            qryDocketLine.Criteria.AddCondition(company_cpseventdocketline.Fields.company_name, ConditionOperator.Equal, "DLN-0003995255");
            #endregion

            #region link to docket
            var lnkDocketLineToDocket = qryDocketLine.AddLink(
                company_cpseventdocket.EntityLogicalName,
                company_cpseventdocketline.Fields.company_cpseventdocketid,
                company_cpseventdocket.Fields.Id,
                JoinOperator.LeftOuter
                );

            lnkDocketLineToDocket.EntityAlias = "docket";

            lnkDocketLineToDocket.Columns.AddColumns(
                company_cpseventdocket.Fields.company_OutFreeDistance,
                company_cpseventdocket.Fields.company_OutExcessDistance,
                company_cpseventdocket.Fields.company_TowFreeDistance,
                company_cpseventdocket.Fields.company_TowExcessDistance,
                company_cpseventdocket.Fields.company_calculatedattendancedistance,
                company_cpseventdocket.Fields.company_calculatedtowingdistance,
                company_cpseventdocket.Fields.company_docketnumber,
                company_cpseventdocket.Fields.company_eventdispositioncodes,
                company_cpseventdocket.Fields.company_customerid,
                company_cpseventdocket.Fields.company_accountid,
                company_cpseventdocket.Fields.company_suburb,
                company_cpseventdocket.Fields.CreatedBy,
                company_cpseventdocket.Fields.company_cpsmemberclubid,
                company_cpseventdocket.Fields.company_cpsprogramid,
                company_cpseventdocket.Fields.company_cpsproductid,
                company_cpseventdocket.Fields.company_vehicleregistration,
                company_cpseventdocket.Fields.company_eventoccurredon,
                company_cpseventdocket.Fields.company_CPSeventid
            );

            #endregion

            #region link to agreement
            var lnkDocketLineToAgreement = qryDocketLine.AddLink(
               company_vendoragreement.EntityLogicalName,
               company_cpseventdocketline.Fields.company_agreementid,
               company_vendoragreement.Fields.Id,
               JoinOperator.LeftOuter);

            lnkDocketLineToAgreement.EntityAlias = "agreement";

            lnkDocketLineToAgreement.Columns.AddColumns(
                company_vendoragreement.PrimaryNameAttribute,
                company_vendoragreement.Fields.company_DAXVendorID,
                company_vendoragreement.Fields.StateCode,
                company_vendoragreement.Fields.StatusCode,
                company_vendoragreement.Fields.company_teamsupplier);
            #endregion

            #region link to vendor
            var lnkDocketLineToAgreementToVendor = lnkDocketLineToAgreement.AddLink(
                company_vendor.EntityLogicalName,
                company_vendoragreement.Fields.company_vendorid,
                company_vendor.Fields.Id,
                JoinOperator.LeftOuter);

            lnkDocketLineToAgreementToVendor.EntityAlias = "vendor";

            lnkDocketLineToAgreementToVendor.Columns.AddColumns(company_vendor.Fields.company_gstapplicable);

            #endregion

            #region link to payment classification
            var lnkDocketLineToPaymentClassification = qryDocketLine.AddLink(
                company_paymentclassification.EntityLogicalName,
                company_cpseventdocketline.Fields.company_paymentclassificationid,
                company_paymentclassification.Fields.company_paymentclassificationId,
                JoinOperator.LeftOuter);

            lnkDocketLineToPaymentClassification.EntityAlias = "classificationcode";

            lnkDocketLineToPaymentClassification.Columns.AddColumns(
                company_paymentclassification.Fields.company_name,
                company_paymentclassification.Fields.company_paymentclassificationId,
                company_paymentclassification.Fields.company_glaccount,
                company_paymentclassification.Fields.company_classificationcode,
                company_paymentclassification.Fields.company_nonbillableglaccount
            );
            #endregion

            #region  link to system user who created the docketline
            var linkDocketLineToSystemUser = qryDocketLine.AddLink(
                SystemUser.EntityLogicalName,
                company_cpseventdocketline.Fields.CreatedBy,
                SystemUser.PrimaryIdAttribute,
                JoinOperator.LeftOuter
                );
            linkDocketLineToSystemUser.EntityAlias = "systemuser";
            linkDocketLineToSystemUser.Columns.AddColumns(SystemUser.Fields.DomainName);

            #endregion

            #region get the DAX transaction mappings
            List<company_cpsdaxtransactionmapping> daxTxnMappings = new List<company_cpsdaxtransactionmapping>();

            var qryDaxTxnMappings = new QueryExpression(company_cpsdaxtransactionmapping.EntityLogicalName);
            qryDaxTxnMappings.Criteria.AddCondition(company_cpsdaxtransactionmapping.Fields.StateCode, ConditionOperator.Equal, (int)company_cpsdaxtransactionmappingState.Active);
            qryDaxTxnMappings.ColumnSet = new ColumnSet(true);

            var results = organisationService.RetrieveMultiple(qryDaxTxnMappings);
            daxTxnMappings = results.Entities.Cast<company_cpsdaxtransactionmapping>().ToList();
            #endregion

            #region link to the extgernal system reference to get the dax customer id
            var lnkDocketToExternalSystemReference = lnkDocketLineToDocket.AddLink(
                company_externalsystemreference.EntityLogicalName,
                company_cpseventdocket.Fields.company_accountid,
                company_externalsystemreference.Fields.company_Customer,
                JoinOperator.LeftOuter
                );
            lnkDocketToExternalSystemReference.EntityAlias = "externalsystemreference";
            lnkDocketToExternalSystemReference.Columns.AddColumns(company_externalsystemreference.Fields.company_SystemIdentifier);
            lnkDocketToExternalSystemReference.LinkCriteria.AddCondition(new ConditionExpression(company_externalsystemreference.Fields.company_externalsystemid, ConditionOperator.Equal, company_externalsystem.RecordIds.DAXCustomer));

            #endregion

            var docketLines = bulkRequestHelper.RetrieveMultiple(qryDocketLine);

            foreach (var dl in docketLines)
            {
                yield return ConvertEntityToDocketLineDto(dl, daxTxnMappings);
            }
        }

Solution

  • Thanks everyone, I found the answer, there is a column security setting for the missing columns and I am not part of the readable team.