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.

INQ queries are built using standard language similar to SQL, but internally it uses QueryExpression and hence it is limited to the features of QueryExppression

The QueryExpression class supports complex queries.

The QueryByAttribute class is a simple means to search for entities where attributes matches specified values.

When using LINQ, it returns IQueryable<Entity> which is not a collection & hence we can’t do group-by or aggregate.

IQueryable<Entity> queryDemo = from a in orgServiceContext.AccountSet

where a.Address1_City.Contains("a")

select a;

 

However, LINQ supports group by in following way.

 Write a simple LINQ Query

  var lnqQuery = from o in orgServiceContext.OpportunitySet

  where o.EstimatedValue.Value >= 10000

  select new

       {

 OpportunityTopic = o.Name,

 PotentialCustomer = o.CustomerId,

  Rating = o.OpportunityRatingCode

   };

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<Groups> estList = new List<Groups>() where Groups is your custom class with set & get.

Here, we are using ToList() & grouping the collection by Rating.

 var lnqQuery2 = (from f in lnqQuery.ToList()

//Pass the lnqQuery as a list using ToList() & then group by

group f by f.Rating into queryGrp

 let first = queryGrp.First()

//selecting only the first record of all grouped record

    select new

     {

  Name = first.OpportunityTopic,

  Rating = first.Rating.Value

     });

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

var lnqQuery2 = (from f in lnqQuery.ToList()

//Pass the lnqQuery as a list using ToList() & then group by

 group f by f.Rating into queryGrp

 //Make a list of queryGrp using ToList()

 select queryGrp.ToList());

//Create a DataTable

DataTable table = new DataTable();

//Add some columns

table.Columns.Add("Topic");

table.Columns.Add("Cust");

table.Columns.Add("Rating");

foreach (var e1 in lnqQuery2)

   {

 //iterate through each record grouped with rating

  foreach (var e2 in e1)

   {

//iterate through each individual record that belongs in a rating

DataRow dataRow = table.NewRow();

 dataRow["Topic"] = e2.OpportunityTopic;

dataRow["Cust"] = e2.PotentialCustomer.Name;

dataRow["Rating"] = e2.Rating

 table.Rows.Add(dataRow);

 }

}

//here you have your list in your dataGridView

dataGridView1.DataSource = table;

 

Note- Here we are just showing the rating value. You can get the rating by using FormattedValues["opportunityratingcode"]

 For more details you can visit here

Hope this article helps!

 

 

 

 

-------------------------------------------------

Posted by: Inogic

For more information/discussions (documents, sample code snippets, detailed work flow or diagrams),

Please be free to visit the following links or email us:

Web: http://www.inogic.com

Blog: http://inogic.blogspot.com

Email: news@inogic.com

--------------------------------------------------