如何通过SQL查询使用LINQ [英] How to use LINQ over a SQL Query

问题描述

这是执行查询后生成的表:

Month	item1	item2	item3
三月	25	0	0
三月	25	0	0
三月	25	0	0
三月	0	25	0
三月	0	25	0
三月	0	0	25
三月	0	0	25

现在使用LINQ，我想计算表值并像这样显示

桌子>

如何通过SQL应用LINQ?
请帮我解决这个问题.

在此先感谢

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

Month	item1	item2	item3
三月	75	50	50

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

推荐答案

尝试以下LINQ:

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)
                        };

(用表替换MyTable.)

由 Shmuel Zang 给出的解决方案1是好的.但是，我认为该代码可能不适用于DataTable.
在问题中指出，表是从查询生成的，我想它是DataTable，并且所需的结果也以与输入表相同的格式表达在问题中.在这种情况下，可以使用以下代码对Group 中的数据进行Group ，然后汇总值，最后将结果创建为DataTable.
我添加了四月月份的值来说明分组.

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  

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>

这篇关于如何通过SQL查询使用LINQ的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！