Microsoft Dynamics CRM (Archived)

How to calculate sum of column value from entity collection?

(0) Share

Report

Posted on by dkrishna

483

Hello,

How to calculate sum of column value from entity collection in c#? Pls suggest how can we do it in c# in a simpler way?

*This post is locked for comments

I have the same question (0)

All responses (14)

Answers (1)

Suggested answer

Macoy 275 on at

Like (0)

Report

Hi Krishna,

You can achieve this using FetchExpression aggregation. See sample below:

// Fetch the sum of estimatedvalue for all opportunities. This is the equivalent of

// SELECT SUM(estimatedvalue) AS estimatedvalue_sum ... in SQL.

string estimatedvalue_sum = @"

<fetch distinct='false' mapping='logical' aggregate='true'>

<entity name='opportunity'>

<attribute name='estimatedvalue' alias='estimatedvalue_sum' aggregate='sum' />

</entity>

</fetch>";

EntityCollection estimatedvalue_sum_result = _serviceProxy.RetrieveMultiple(new FetchExpression(estimatedvalue_sum));

foreach (var c in estimatedvalue_sum_result.Entities)

{

decimal total= ((Money)((AliasedValue)c["estimatedvalue_sum"]).Value).Value;

System.Console.WriteLine("Sum of estimated value of all opportunities: " + total);

}

System.Console.WriteLine("just a sample");

Regards,

Macoy

Was this reply helpful? Yes No
dkrishna 483 on at

Like (0)

Report

I want to do sum from c# code and not from fetch xml. Pls suggest for this.

Was this reply helpful? Yes No
Alex Fun Wei Jie 33,628 on at

Like (0)

Report

Hi Krishna prasad,

which method did you use for retrieve, query expression? fetchxml ? please elaborate.

Was this reply helpful? Yes No
dkrishna 483 on at

Like (0)

Report

To retrieve entity collection i use fetch xml but in that i dont want to use aggregate function in fetch xml. I want to do sum of field from code once i got collection.

Was this reply helpful? Yes No
Suggested answer

Macoy 275 on at

Like (0)

Report

Hi Krishna,

My given sample is applicable for c#. FetchExpression is one type of query you can use instead of QueryExpression.

Regards,

Macoy

Was this reply helpful? Yes No
Suggested answer

Alex Fun Wei Jie 33,628 on at

Like (0)

Report

Hi,

try this, the below example i am using Money field.

EntityCollection ec = ExecuteFetch(fetchXmlString);

if (ec.Entities.Count > 0)

{

//declare a variable here

double total =0;

foreach (var item in ec.Entities)

{

if (item.Attributes.Contains("fieldschemaname"))

{

total +=((Money)item.Attributes["fieldschemaname"]).Value;

}

}

}

Was this reply helpful? Yes No
dkrishna 483 on at

Like (0)

Report

Can you pls suggest using linq to get sum and to avoid loop for every record if there are more records.

Was this reply helpful? Yes No
Suggested answer

Temmy Wahyu Raharjo 2,916 on at

Like (0)

Report

Actually linq also will loop based on the data also. :)

var totalSum = result.Entities.Sum(e=> e.Contains("new_amount") ? e.GetAttributeValue<Money>("new_amount").Value : 0)

if there are more records then you need to do this:

var totalSum = 0;

var query =new QueryExpression("yourentity");

..

query.PageInfo = new PagingInfo

{

Count = queryCount,

PageNumber = pageNumber,

PagingCookie = null,

ReturnTotalRecordCount = true

};

while (true)

{

var result = service.RetrieveMultiple(query);

totalSum+ = result.Entities.Sum(e=> e.Contains("new_amount") ? e.GetAttributeValue<Money>("new_amount").Value : 0);

if (result.MoreRecords)

{

query.PageInfo.PageNumber++;

query.PageInfo.PagingCookie = result.PagingCookie;

}

else

{

break;

}

}

Was this reply helpful? Yes No
Vaibhav Shukla 120 on at

Like (0)

Report

int total =0;

EntityCollection ec = service.RetrieveMultiple(EntityName);

foreach (var item in ec.Entities)

{

if (item.Attributes.Contains("primaryfieldname"))

{

total ++;

}

}

}

Was this reply helpful? Yes No
dkrishna 483 on at

Like (0)

Report

Thanks alot temmy It works !!!. can you pls also suggest if i had to add where condition same linq like casetype=700001 which is optionset value how i can add it?

Was this reply helpful? Yes No