{"id":36,"date":"2013-03-06T10:07:00","date_gmt":"2013-03-06T04:37:00","guid":{"rendered":"https:\/\/www.inogic.com\/blog\/?p=36"},"modified":"2013-03-06T10:07:00","modified_gmt":"2013-03-06T04:37:00","slug":"aggregate-and-grouping-functions-using-linq-in-crm","status":"publish","type":"post","link":"https:\/\/www.inogic.com\/blog\/2013\/03\/aggregate-and-grouping-functions-using-linq-in-crm\/","title":{"rendered":"Aggregate and Grouping functions using LINQ in CRM"},"content":{"rendered":"
<\/span><\/p>\n
As you know, QueryExpression are built as an Object Model. They support all the features of FetchXML except for grouping & aggregates.<\/p>\n

<\/span><\/span><\/div>\n

<\/span><\/p>\n

FetchXML supports all the features of QueryExpression including grouping & aggregates. Queries here are built as XML statements.<\/p>\n

<\/span><\/span><\/div>\n

<\/span><\/p>\n

LINQ queries are built using standard language similar to SQL, but internally it uses QueryExpression and hence it is limited to the features of QueryExppression<\/p>\n

<\/span><\/span><\/div>\n

<\/span><\/p>\n

<\/span><\/a><\/div>\n

<\/span>
<\/span><\/p>\n

The QueryExpression<\/b>class supports complex queries.<\/p>\n

<\/span><\/span><\/div>\n

The QueryByAttribute<\/b> class is a simple means to search for entities where attributes matches specified values.<\/p>\n

<\/span><\/span>
<\/span>
When using LINQ, it returns IQueryable which is not a collection & hence we can\u2019t do group-by or aggregate.<\/p>\n

<\/entity><\/entity><\/span><\/span>
<\/span>
IQueryable queryDemo = from a in orgServiceContext.AccountSet<\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 where a.Address1_City.Contains(“a”)<\/span><\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 select a;<\/span><\/p>\n

\u00a0<\/span><\/p>\n<\/div>\n

<\/span>\u00a0<\/div>\n
However, LINQ supports group by in following way.<\/p>\n

<\/span><\/span><\/b>
\u00a0<\/div>\n

<\/span><\/p>\n

Write a simple LINQ Query<\/span><\/span><\/div>\n

<\/span><\/p>\n

var lnqQuery = from o in orgServiceContext.OpportunitySet<\/span><\/span><\/div>\n

<\/span><\/p>\n

where o.EstimatedValue.Value >= 10000<\/span><\/div>\n

<\/span><\/p>\n

select new<\/p>\n

<\/span><\/span><\/div>\n

<\/span><\/p>\n

\u00a0\u00a0 {<\/p>\n

<\/span><\/span><\/div>\n

<\/span><\/p>\n

OpportunityTopic = o.Name,<\/span><\/div>\n

<\/span><\/p>\n

PotentialCustomer = o.CustomerId,<\/p>\n

<\/span><\/span><\/div>\n

<\/span><\/p>\n

Rating = o.OpportunityRatingCode<\/p>\n

<\/span><\/span><\/div>\n

<\/span><\/p>\n

};<\/span><\/span>
<\/p>\n

<\/span><\/span>\u00a0<\/div>\n

<\/span><\/p>\n

In another query pass your created query as a list. You can either do a ToList() or you can make your own List like List estList = new List() where Groups is your custom class with set & get. <\/p>\n

<\/span><\/span><\/div>\n

<\/span><\/p>\n

<\/span><\/div>\n

<\/span><\/p>\n

Here, we are using ToList() & grouping the collection by Rating.<\/p>\n

<\/span><\/span>
\u00a0<\/div>\n

<\/span><\/p>\n

var lnqQuery2 = (from f in lnqQuery.ToList()<\/p>\n

<\/span><\/span>
<\/span>\u00a0<\/div>\n

\/\/Pass the lnqQuery as a list using ToList() & then group by<\/p>\n

<\/span><\/span>
\u00a0<\/div>\n

<\/span><\/p>\n

group f by f.Rating into queryGrp<\/p>\n

<\/span><\/span>
\u00a0<\/div>\n

<\/span><\/p>\n

let first = queryGrp.First()<\/p>\n

<\/span><\/span>
\u00a0<\/div>\n

<\/span><\/p>\n

\/\/selecting only the first record of all grouped record<\/span>
\u00a0<\/div>\n

<\/span><\/p>\n

select new<\/p>\n

<\/span><\/span><\/div>\n

<\/span><\/p>\n

{<\/p>\n

<\/span><\/span><\/div>\n

<\/span><\/p>\n

Name = first.OpportunityTopic,<\/span><\/span>
<\/p>\n

<\/span><\/span>\u00a0<\/div>\n

<\/span><\/p>\n

Rating = first.Rating.Value<\/p>\n

<\/span><\/span><\/div>\n

<\/span><\/p>\n

});<\/p>\n

<\/span><\/span>
\u00a0<\/div>\n

<\/span><\/p>\n

Now if we want to retrieve all the records from the Query Group, then we can simply create a list of query Group using ToList() & iterate through it as shown in below example<\/span><\/div>\n

<\/span><\/p>\n

var lnqQuery2 = (from f in lnqQuery.ToList()<\/p>\n

<\/span><\/span>
\u00a0<\/div>\n

<\/span><\/p>\n

\/\/Pass the lnqQuery as a list using ToList() & then group by<\/p>\n

<\/span><\/span>
\u00a0<\/div>\n

<\/span><\/p>\n

group f by f.Rating into queryGrp<\/p>\n

<\/span><\/span>
\u00a0<\/div>\n

<\/span><\/p>\n

\/\/Make a list of queryGrp using ToList()<\/span><\/span>
<\/p>\n

<\/span><\/span>\u00a0<\/div>\n

<\/span><\/p>\n

select queryGrp.ToList());<\/p>\n

<\/span><\/span>
\u00a0<\/div>\n

<\/span><\/p>\n

\/\/Create a DataTable<\/p>\n

<\/span><\/span>
\u00a0<\/div>\n

<\/span><\/p>\n

DataTable table = new DataTable();<\/p>\n

<\/span><\/span>
\u00a0<\/div>\n

<\/span><\/p>\n

\/\/Add some columns<\/span><\/p>\n

\u00a0<\/div>\n

<\/span><\/p>\n

table.Columns.Add(“Topic”);<\/p>\n

<\/span><\/span>
\u00a0<\/div>\n

<\/span><\/p>\n

table.Columns.Add(“Cust”);<\/p>\n

<\/span><\/span>
\u00a0<\/div>\n

<\/span><\/p>\n

table.Columns.Add(“Rating”);<\/p>\n

<\/span><\/span>
\u00a0<\/div>\n

<\/span><\/p>\n

foreach (var e1 in lnqQuery2)<\/p>\n

<\/span><\/span>
\u00a0<\/div>\n

<\/span><\/p>\n

{<\/p>\n

<\/span><\/span><\/div>\n

<\/span><\/p>\n

\/\/iterate through each record grouped with rating<\/span><\/p>\n

\u00a0<\/div>\n

<\/span><\/p>\n

foreach (var e2 in e1)<\/p>\n

<\/span><\/span><\/div>\n

<\/span><\/p>\n

{<\/p>\n

<\/span><\/span><\/div>\n

<\/span><\/p>\n

\/\/iterate through each individual record that belongs in a rating<\/span>
<\/span><\/p>\n

\u00a0<\/p><\/div>\n

<\/span><\/p>\n

DataRow dataRow = table.NewRow();<\/p>\n

<\/span><\/span>
<\/span>\u00a0<\/div>\n

dataRow[“Topic”] = e2.OpportunityTopic;<\/p>\n

<\/span><\/span>
\u00a0<\/div>\n

<\/span><\/p>\n

dataRow[“Cust”] = e2.PotentialCustomer.Name;<\/span>
\u00a0<\/div>\n

<\/span><\/p>\n

dataRow[“Rating”] = e2.Rating;<\/p>\n

<\/span><\/span>
\u00a0<\/div>\n

<\/span><\/p>\n

table.Rows.Add(dataRow);<\/p>\n

<\/span><\/span><\/div>\n

<\/span><\/p>\n

\u00a0}<\/p>\n

<\/span><\/span><\/div>\n

<\/span><\/p>\n

}<\/p>\n

<\/span><\/span><\/div>\n

<\/span><\/p>\n

\/\/here you have your list in your dataGridView<\/span><\/p>\n

\u00a0<\/div>\n

<\/span><\/p>\n

dataGridView1.DataSource = table;<\/span><\/p>\n

<\/span><\/p>\n

\u00a0<\/div>\n

<\/span><\/p>\n

Note-<\/b> Here we are just showing the rating value. You can get the rating by using FormattedValues[“opportunityratingcode”]\n

<\/span><\/span><\/div>\n

<\/span><\/p>\n

For more details you can visit <\/span>here<\/a><\/span><\/span><\/div>\n

<\/span><\/p>\n

<\/span>
Hope this article helps!<\/p>\n

<\/span><\/span><\/span><\/div>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span><\/p>\n

\u00a0<\/span><\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

As you know, QueryExpression are built as an Object Model. They support all the features of FetchXML except for grouping & aggregates. FetchXML supports all the features of QueryExpression including grouping & aggregates. Queries here are built as XML statements. LINQ queries are built using standard language similar to SQL, but internally it uses QueryExpression\u2026 Read More »<\/a><\/span><\/p>\n","protected":false},"author":13,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[19],"tags":[122,795,876,992,1392],"class_list":["post-36","post","type-post","status-publish","format-standard","hentry","category-dynamics-crm","tag-aggregate","tag-fetchxml","tag-grouping","tag-linq","tag-queryexpression"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/posts\/36","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/users\/13"}],"replies":[{"embeddable":true,"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/comments?post=36"}],"version-history":[{"count":0,"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/posts\/36\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/media?parent=36"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/categories?post=36"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/tags?post=36"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}