How to use LINQ over a SQL Query

Question

0.00/5 (No votes)

See more:

Here is a table which is generated after executing a query:

Month	item1	item2	item3
March	25	0	0
March	25	0	0
March	25	0	0
March	0	25	0
March	0	25	0
March	0	0	25
March	0	0	25

Now using LINQ i want to calculate the table value and display like this

Month	item1	item2	item3
March	75	50	50

How to apply LINQ over a SQL?
please help me to solve this.

Thanks in advance

Posted 22-Apr-12 19:35pm

sahabiswarup

Updated 22-Apr-12 19:50pm

v2

Add a Solution

4 solutions

Solution 1

Try the following LINQ:

C#

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.)

Posted 22-Apr-12 21:54pm

Shmuel Zang

Comments

VJ Reddy 23-Apr-12 4:34am

Good answer. 5!

Shmuel Zang 23-Apr-12 7:12am

Thanks.

sahabiswarup 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 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...)

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.

C#

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

Posted 22-Apr-12 22:40pm

VJ Reddy

Updated 23-Apr-12 1:09am

v5

Comments

sahabiswarup 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 23-Apr-12 7:09am

Please see modified Solution.

Shmuel Zang 23-Apr-12 7:24am

5'ed.

VJ Reddy 23-Apr-12 7:43am

Thank you.

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

Posted 22-Apr-12 21:55pm

Argonia

Add a Solution

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

sahabiswarup · Accepted Answer · 2012-04-23T00:45:00

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>

How to use LINQ over a SQL Query

4 solutions

Solution 1

Solution 3

Solution 4

Solution 2

Add your solution here

Preview 0

How to use LINQ over a SQL Query

4 solutions

Solution 1

Solution 3

Solution 4

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us