My colleague asked me this question. Instead of doing the conventional way, he liked to try on LINQ for simplicity. The naming convention was ignored for this example. This was something that we hooked up together.
var query = from row in dtInvoice.AsEnumerable()
group row by new { InvNo = row.Field< string >( "InvNo" ),
EmpUNID = row.Field< decimal >( "EmployeeUNID" )} into grp
orderby grp.Key.InvNo
select new
{
Key = grp.Key,
InvNo = grp.Key.InvNo,
EmpID = grp.Key.EmpUNID,
TotalCost = grp.Sum(r => r.Field< decimal >( "TotalCost" )),
TotalRev = grp.Sum(r => r.Field< decimal >( "TotalRev" ))
};
In order to come out with this solution, you have to understand the concept of LINQ which has similarities to T-SQL.
Explanations:
dtIncoice is a datatable. You can use generic list or any preferred choice. Instead of doing this grouping in T-SQL Stored Procedure, my colleague chose to do this in C# instead.
You notice that when we perform grouping, we have to do this:
group row by new { }
Do that if you plan to have multiple grouping, if not you can do this instead:
group row by { row.Field<string>("InvNo") }
Then why do I have to do this in multiple grouping?
group row by new { A = some values, B = some values }
If you don't do that, you will probably get an error when compiling your code or won't get proper results:
Anonymous type members must be declared with a member assignment, simple name or member access.
However, you can ignore that if you have single field
In order to use InvNo and EmpUNID from grouping aggregation, you need to reassign that values when you perform a Select in the LINQ query. That makes a lot of sense.
select new
{
InvNo = grp.Key.InvNo,
EmpID = grp.Key.EmpUNID,
}
If not, when you query this later for further processing, you are not able to access it. Hope you find this useful.
Thank you.