locked
SDK.Query.QueryExpression - Count in Select clause RRS feed

  • Question

  • Using c#, I need to build a query using the SDK.Query.QueryExpression library for Microsoft Dynamics 2015.  I am unable to figure out how to order and count the number of occurrences.  

    I want to get a count of how many time each Product was used in all the Opportunities.

    The sql query itself is rather simple:

    SELECT b.Name, count(a.ProductId) as 'accurances'
    FROM [ProkonCRM_MSCRM].[dbo].[OpportunityProductBase] a,[ProkonCRM_MSCRM].[dbo].[ProductBase] b
    where a.ProductId = b.ProductId
    group by b.name 



    Christo Vermeulen

    Thursday, September 3, 2015 12:11 PM

Answers

  • Hello,

    You would not be able to do it using QueryExpression. You will have to go with FetchExpression and following query:

    <fetch mapping="logical" aggregate="true" version="1.0">
      <entity name="opportunityproduct">
        <attribute name="productidname" groupby="true" alias="product"/>
        <attribute name="productid" alias="accurances" aggregate="countcolumn" />
      </entity>
    </fetch>


    Dynamics CRM MVP
    My blog

    Thursday, September 3, 2015 1:42 PM
    Moderator

All replies

  • Hello,

    You would not be able to do it using QueryExpression. You will have to go with FetchExpression and following query:

    <fetch mapping="logical" aggregate="true" version="1.0">
      <entity name="opportunityproduct">
        <attribute name="productidname" groupby="true" alias="product"/>
        <attribute name="productid" alias="accurances" aggregate="countcolumn" />
      </entity>
    </fetch>


    Dynamics CRM MVP
    My blog

    Thursday, September 3, 2015 1:42 PM
    Moderator
  • It's not possible using Query Expression. Please refer to the MSDN Documentation regarding each query type. Personally I don't like having large XML strings, also if the record sets are likely to be greater than 5000 you will need to use paging for either. It may be better to run a simpler query and then you can run LINQ (or do a .count() etc.) over you result set.

    • Proposed as answer by Regan Markwick Thursday, October 15, 2015 4:43 AM
    Thursday, September 10, 2015 4:13 AM
  • QueryExpressionquery = newQueryExpression();

                query.EntityName =

    "opportunityproduct";

                query.ColumnSet =

    newColumnSet(new[] { "name", "productid"});

               

    FilterExpressionfilter = newFilterExpression();

                filter.FilterOperator =

    LogicalOperator.And;

               

    ConditionExpressioncondition = newConditionExpression();

                condition.AttributeName =

    "productid";

                condition.Operator =

    ConditionOperator.Equal;

                condition.Values.Add(yourID);

                filter.Conditions.Add(condition);

                query.Criteria.AddFilter(filter);

               

    varitems = service.RetrieveMultiple(query);

    int totalCount=items.TotalRecordCount;


    Hope this helps. ----------------------------------------------------------------------- Santosh Bhagat If this post answers your question, please click "Mark As Answer" on the post and "Vote as Helpful"

    Tuesday, September 15, 2015 10:35 AM