Click here to Skip to main content
15,879,326 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
I have a query in sql but I have to use Linq to perform the same operation How can I do this ?
The query is :
SQL
SELECT sum(credit) - sum(debit) AS Bal, date
FROM AccData
GROUP BY date;


I have written a query but that certainly seems wrong as it has multiple errors, please help.
I have with me a Data Table AccData on which this query has to be performed
Posted
Updated 24-Apr-12 23:23pm
v2
Comments
pietvredeveld 25-Apr-12 5:32am    
Posting some code of what you have done, can help us to help you!
Piet
sagar wasule 25-Apr-12 5:53am    
I have received a datatable from another method , I'm writing a method to perform operation on this particular datatable according to the query I wrote above using Linq.

1 solution

The following LINQ query can be used to obtain the above results
C#
void Main()
{
    DataTable accData = new DataTable();
    accData.Columns.Add("Date",typeof(DateTime),null);
    accData.Columns.Add("Credit",typeof(double),null);
    accData.Columns.Add("Debit",typeof(double),null);
    
    accData.Rows.Add(new DateTime(2012,03,24),500,300);
    accData.Rows.Add(new DateTime(2012,03,24),350,275);
    accData.Rows.Add(new DateTime(2012,03,24),400,200);
    accData.Rows.Add(new DateTime(2012,03,25),250,100);
    accData.Rows.Add(new DateTime(2012,03,25),350,150);
    accData.Rows.Add(new DateTime(2012,03,27),250,75);
    accData.Rows.Add(new DateTime(2012,03,27),175,50);
    
    DataTable balance = new DataTable();
    balance.Columns.Add("Date",typeof(DateTime),null);
    balance.Columns.Add("Bal",typeof(double),null);
    
   
    accData.AsEnumerable().GroupBy (row =>row.Field<datetime>("Date"))
    .Select (row => {DataRow balRow = balance.NewRow(); 
    	balRow["Date"]=row.Key;
    	balRow["Bal"]=row.Sum (r => r.Field<double>("Credit"))
    				  - row.Sum (r => r.Field<double>("Debit"));
    	return balRow;}).CopyToDataTable(balance,LoadOption.OverwriteChanges);
}
//Contents balance DataTable
//
//Date                  Bal
//24/3/2012 12:00:00 AM 475 
//25/3/2012 12:00:00 AM 350 
//27/3/2012 12:00:00 AM 300 


[Edit]Group Data for illustration as noted in the comment to this solution[/Edit]
C#
//Key=  24/3/2012 12:00:00 AM 
//
//Date             Credit Debit
//24/3/2012 12:00:00 AM 500 300 
//24/3/2012 12:00:00 AM 350 275 
//24/3/2012 12:00:00 AM 400 200 
// 
//Key=  25/3/2012 12:00:00 AM 
//
//Date             Credit Debit
//25/3/2012 12:00:00 AM 250 100 
//25/3/2012 12:00:00 AM 350 150 
// 
//Key=  27/3/2012 12:00:00 AM 
//
//Date             Credit Debit
//27/3/2012 12:00:00 AM 250 75 
//27/3/2012 12:00:00 AM 175 50
 
Share this answer
 
v3
Comments
sagar wasule 25-Apr-12 6:18am    
Can you please tell from where 'r' came from ??

in row.Sum (r => r.Field<double>("Credit"))
- row.Sum (r => r.Field<double>("Debit"));
VJ Reddy 25-Apr-12 6:56am    
r is different from row.
When Grouping is done, an IEnumerable collection is returned in which each element contains a key and an IEnumerable of elements of the original collection. In the above example row contains key i.e. date, and an IEnumerable of the Original DataRow. The r in the above example is the element of this inner IEnuerable of the row. Please see the solution in which I have posted the grouped data for illustration.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900