Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: LINQ SQL
Here is a table which is generated after executing a query:

Monthitem1item2item3
March2500
March2500
March2500
March0250
March0250
March0025
March0025
Now using LINQ i want to calculate the table value and display like this

Monthitem1item2item3
March755050
 
How to apply LINQ over a SQL?
please help me to solve this.
 
Thanks in advance
Posted 22-Apr-12 19:35pm
Edited 22-Apr-12 19:50pm
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Try the following LINQ:

var result = from myRow in MyTable
                group myRow by myRow.Month
                into rowGroup
                select new
                        {
                            Month = rowGroup.Key,
                            Item1 = rowGroup.Sum(r => r.Item1),
                            Item2 = rowGroup.Sum(r => r.Item2),
                            Item3 = rowGroup.Sum(r => r.Item3)
                        };

(Replace MyTable with your table.)

  Permalink  
Comments
VJ Reddy at 23-Apr-12 4:34am
   
Good answer. 5!
Shmuel Zang at 23-Apr-12 7:12am
   
Thanks.
biswarup88 at 23-Apr-12 5:22am
   
if i am doing this way it will show "MyTable" does not exists.
DataTable dt1 = new DataTable();
string sqlStatement1 = "SELECT DATENAME(mm, doe) AS [MonthName],[item1], [item2], [item3] FROM ( SELECT doe,item, amount FROM item_details) p PIVOT ( SUM(amount) FOR item IN ([item1], [item2], [item3])) AS pvt";
SqlCommand sqlCmd1 = new SqlCommand(sqlStatement1, conn);
SqlDataAdapter sqlDa1 = new SqlDataAdapter(sqlCmd1);
sqlDa1.Fill(dt1);
var result = from myRow in MyTable
group myRow by myRow.Month
into rowGroup
select new
{
Month = rowGroup.Key,
Item1 = rowGroup.Sum(r => r.Item1),
Item2 = rowGroup.Sum(r => r.Item2),
Item3 = rowGroup.Sum(r => r.Item3)
};
GridView1.DataSource = query;
GridView1.DataBind();
Shmuel Zang at 23-Apr-12 7:21am
   
As I wrote, you have to replace MyTable with your table. But, if you use a DataTable, see the solution of VJ Reddy. (My solution is more compatible for EntityFramework or something like...)
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

The solution 1 given by Shmuel Zang is good. But, I think the code as it may not work for DataTable.
In the question it is stated that table is generated from query, I suppose it is a DataTable, and the result required is also formulated in the question in the same format of the input table. For this case the following code can be used to Group the data in the DataTable and then aggregate the values and finally to create the result as a DataTable.
I have added April month values to illustrate grouping.
void Main()
{
    DataTable itemDetails = new DataTable();
    itemDetails.Columns.Add("Id",typeof(int),null);
    itemDetails.Columns.Add("Item",typeof(string),null);
    itemDetails.Columns.Add("Date",typeof(DateTime),null);
    itemDetails.Columns.Add("Amount",typeof(int),null);
    
    itemDetails.Rows.Add(1,   "Item1",new DateTime(2012,03,24),     25);
    itemDetails.Rows.Add(1,   "Item2",new DateTime(2012,03,24),     25);
    itemDetails.Rows.Add(1,   "Item3",new DateTime(2012,03,24),     25);
    itemDetails.Rows.Add(1,   "Item1",new DateTime(2012,03,25),     25);
    itemDetails.Rows.Add(1,   "Item1",new DateTime(2012,03,26),     25);
    itemDetails.Rows.Add(1,   "Item2",new DateTime(2012,03,27),     25);
    itemDetails.Rows.Add(1,   "Item3",new DateTime(2012,03,28),     25);
    
    DataTable monthValues = new DataTable();
    monthValues.Columns.Add("Month", typeof(string),null);
    monthValues.Columns.Add("Item1", typeof(int),null);
    monthValues.Columns.Add("Item2", typeof(int),null);
    monthValues.Columns.Add("Item3", typeof(int),null);
    monthValues.Columns["Item1"].DefaultValue=0;
    monthValues.Columns["Item2"].DefaultValue=0;
    monthValues.Columns["Item3"].DefaultValue=0;
    
    itemDetails.AsEnumerable().Select (row => {
    	DataRow mvRow = monthValues.NewRow();
    	mvRow["Month"]=row.Field<DateTime>("Date").ToString("MMM");
    	mvRow[row.Field<string>("Item")]=row.Field<int>("Amount");
    	return mvRow;
    }).CopyToDataTable(monthValues,LoadOption.OverwriteChanges);
    
    DataTable totalTable = monthValues.AsEnumerable().GroupBy (row =>row.Field<string>("Month"))
    .Select (row => {DataRow totalRow = monthValues.NewRow(); 
	totalRow["Month"]=row.Key;
	totalRow["Item1"]=row.Sum (r => r.Field<int>("Item1"));
	totalRow["Item2"]=row.Sum (r => r.Field<int>("Item2"));
	totalRow["Item3"]=row.Sum (r => r.Field<int>("Item3"));
	return totalRow;}).CopyToDataTable();
    
    //Print Total Table to Console
    foreach(DataColumn col in monthValues.Columns)
    	Console.Write(col.ColumnName + "\t");
    Console.WriteLine ();
    foreach(DataRow row in totalTable.Rows){
    	foreach (var element in row.ItemArray)
    	{
    		Console.Write(element.ToString() + "\t");			
    	}
    	Console.WriteLine ();
    	
    }
}
//Month  Item1  Item2  Item3  
//Mar  75  50  50  
  Permalink  
v5
Comments
biswarup88 at 23-Apr-12 5:18am
   
thanks for the above solution. But you fixed the row value like
monthValues.Rows.Add("March",25,0,0);
monthValues.Rows.Add("March",25,0,0);
monthValues.Rows.Add("March",25,0,0);
monthValues.Rows.Add("March",0,25,0);
monthValues.Rows.Add("March",0,25,0);
monthValues.Rows.Add("March",0,0,25);
monthValues.Rows.Add("March",0,0,25);
monthValues.Rows.Add("April",50,0,0);
monthValues.Rows.Add("April",50,0,0);
monthValues.Rows.Add("April",50,0,0);
monthValues.Rows.Add("April",0,50,0);
monthValues.Rows.Add("April",0,50,0);
monthValues.Rows.Add("April",0,0,50);
monthValues.Rows.Add("April",0,0,50);
i want to do this process dynamically.
As my Table is as follows:
Table item_details
 
id item date amount
1 item1 2012-03-24 25
1 item2 2012-03-24 25
1 item3 2012-03-24 25
1 item1 2012-03-25 25
1 item1 2012-03-26 25
1 item2 2012-03-27 25
1 item3 2012-03-28 25
 
this is my table structure.
 
and using query i want to display

Month Item1 Item2 Item3
Jan 0 0 0
Feb 0 0 0
Mar 75 50 50
 
Hope i can explain my problem properly.
VJ Reddy at 23-Apr-12 7:09am
   
Please see modified Solution.
Shmuel Zang at 23-Apr-12 7:24am
   
5'ed.
VJ Reddy at 23-Apr-12 7:43am
   
Thank you.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Well as far as i know in C# you can use the same way like a normal sql query. There are a lot of examples in msdn.
101 LINQ Samples

About the sql query i am not sure what exactly you want to do . To sum according all months or just for march but in both cases you need sum(). Please check this
SQL Tutorial
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 4

  DataTable dt1 = new DataTable();
            string sqlStatement1 = "SELECT  DATENAME(mm, doe) AS [MonthName],[Beef], [Buffaloes], [Chicks], [Chivon],[Dry Fish], [Dry Meat], [Egg], [Fish], [Fowl],[Goats], [Milch Cow], [Piglets], [Pigs], [Pork] FROM ( SELECT doe,item, amount FROM entry_item_new) p PIVOT ( SUM(amount) FOR item IN ([Beef],[Buffaloes],[Chicks], [Chivon],[Dry Fish], [Dry Meat], [Egg], [Fish], [Fowl],[Goats], [Milch Cow], [Piglets], [Pigs], [Pork])) AS pvt";
            SqlCommand sqlCmd1 = new SqlCommand(sqlStatement1, conn);
            SqlDataAdapter sqlDa1 = new SqlDataAdapter(sqlCmd1);
            sqlDa1.Fill(dt1);
            var result = from r in dt1.AsEnumerable()
                         group r by r.Field<string>("MonthName")
                             into rowGroup
                             select new
                             {
                                 MonthName = rowGroup.Key,
                                 Beef = rowGroup.Sum(s => (s["Beef"] == DBNull.Value) ? 0 : Convert.ToDouble(s["Beef"])),
                                 //Beef = rowGroup.Sum(r => r.Field<int>("Beef")),
                                 Buffaloes = rowGroup.Sum(s => (s["Buffaloes"] == DBNull.Value) ? 0 : Convert.ToDouble(s["Buffaloes"])),
                                 Chicks = rowGroup.Sum(s => (s["Chicks"] == DBNull.Value) ? 0 : Convert.ToDouble(s["Chicks"])),
                                 Chivon = rowGroup.Sum(s => (s["Chivon"] == DBNull.Value) ? 0 : Convert.ToDouble(s["Chivon"])),
                                 Dry_Fish = rowGroup.Sum(s => (s["Dry Fish"] == DBNull.Value) ? 0 : Convert.ToDouble(s["Dry Fish"])),
                                 Dry_Meat = rowGroup.Sum(s => (s["Dry Meat"] == DBNull.Value) ? 0 : Convert.ToDouble(s["Dry Meat"])),
                                 Egg = rowGroup.Sum(s => (s["Egg"] == DBNull.Value) ? 0 : Convert.ToDouble(s["Egg"])),
                                 Fish = rowGroup.Sum(s => (s["Fish"] == DBNull.Value) ? 0 : Convert.ToDouble(s["Fish"])),
                                 Fowl = rowGroup.Sum(s => (s["Fowl"] == DBNull.Value) ? 0 : Convert.ToDouble(s["Fowl"])),
                                 Goats = rowGroup.Sum(s => (s["Goats"] == DBNull.Value) ? 0 : Convert.ToDouble(s["Goats"])),
                                 Milch_Cow = rowGroup.Sum(s => (s["Milch Cow"] == DBNull.Value) ? 0 : Convert.ToDouble(s["Milch Cow"])),
                                 Piglets = rowGroup.Sum(s => (s["Piglets"] == DBNull.Value) ? 0 : Convert.ToDouble(s["Piglets"])),
                                 Pigs = rowGroup.Sum(s => (s["Pigs"] == DBNull.Value) ? 0 : Convert.ToDouble(s["Pigs"])),
                                 Pork = rowGroup.Sum(s => (s["Pork"] == DBNull.Value) ? 0 : Convert.ToDouble(s["Pork"]))
                             };</int></string>
  Permalink  

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

  Print Answers RSS
0 CPallini 375
1 Sergey Alexandrovich Kryukov 370
2 BillWoodruff 314
3 George Jonsson 274
4 OriginalGriff 257
0 OriginalGriff 5,030
1 CPallini 4,115
2 Sergey Alexandrovich Kryukov 3,554
3 George Jonsson 2,826
4 Gihan Liyanage 2,386


Advertise | Privacy | Mobile
Web01 | 2.8.140916.1 | Last Updated 23 Apr 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100