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 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!
