Sign in
Microsoft.com
 
Microsoft
 
 
 

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

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote
    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

  • Question
    Sign in to vote
    0
    Sign in to vote

    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

  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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