Answered by:
SDK.Query.QueryExpression - Count in Select clause

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- Proposed as answer by Regan Markwick Thursday, October 15, 2015 4:42 AM
- Marked as answer by Christo Vermeulen Thursday, October 15, 2015 5:57 AM
Thursday, September 3, 2015 1:42 PMModerator
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- Proposed as answer by Regan Markwick Thursday, October 15, 2015 4:42 AM
- Marked as answer by Christo Vermeulen Thursday, October 15, 2015 5:57 AM
Thursday, September 3, 2015 1:42 PMModerator -
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"
- Proposed as answer by Mr. Santosh Bhagat Tuesday, September 15, 2015 10:35 AM
Tuesday, September 15, 2015 10:35 AM