如何在数据表上运行LINQ [英] How to run LINQ over a datatable

问题描述

这是我通过它生成数据表的SQL查询

Here is my sql query through which i am generating a datatable

select user_id,Convert(char,date_of_travel,103) as date_of_travel,amount from booking where month(date_of_travel)=''5'' union select user_id,Convert(char,date_of_travel,103) as date_of_travel,amount from sight_seeing where month(date_of_travel)=''5'' order by user_id

user_id date_of_travel amount
   101    01/05/2012   1000
   101    02/05/2012   500
   101    05/05/2012   700
   102    11/05/2012   800
   103    02/05/2012   850

现在我想在此数据表上运行以下查询

Now i want to run this following query over this datatable

SELECT user_id,SUM(amount) FROM table GROUP BY user_id

请帮我解决这个问题.
预先感谢.

Please help me to solve this.
Thanks in advance.

推荐答案

mr.priyank 的解决方案1很好.

或者，在 http://msdn.microsoft.com/en上解释DataRow 的Field 扩展方法-us/library/bb360891.aspx [ ^ ]和IEnumerable的CopyToDataTable 扩展方法在此处 http://msdn.microsoft. com/en-us/library/bb359707 [ ^ ]可以如下使用:

The solution 1 by mr.priyank is good.

Alternatively the Field extension method of DataRow explained here http://msdn.microsoft.com/en-us/library/bb360891.aspx[^] and CopyToDataTable extension method of IEnumerable explained here http://msdn.microsoft.com/en-us/library/bb359707[^] can be used as follows:

DataTable data = new DataTable("Data");
data.Columns.Add("UserId",typeof(string),null);
data.Columns.Add("DateOfTravel",typeof(DateTime),null);
data.Columns.Add("Amount",typeof(double),null);

Thread.CurrentThread.CurrentCulture = CultureInfo.GetCultureInfo("hi-IN");
data.Rows.Add("101",DateTime.Parse("01/05/2012"),1000);
data.Rows.Add("101",DateTime.Parse("02/05/2012"),500);
data.Rows.Add("101",DateTime.Parse("05/05/2012"),700);
data.Rows.Add("102",DateTime.Parse("11/05/2012"),800);
data.Rows.Add("103",DateTime.Parse("02/05/2012"),850);

DataTable groupedData = new DataTable("GroupedData");
groupedData.Columns.Add("UserId",typeof(string),null);
groupedData.Columns.Add("Sum",typeof(double),null);

data.AsEnumerable().GroupBy (dr => dr.Field<string>("UserId"))
	.Select (dr => {
		DataRow row = groupedData.NewRow();
		row["UserId"]=dr.Key;
		row["Sum"] = dr.Sum (d => d.Field<double>("Amount"));
		return row;
	}).CopyToDataTable(groupedData,LoadOption.OverwriteChanges);
//groupedData
//UserId Sum
//101   2200 
//102    800 
//103    850

// let dt has the data from the database

System.Data.DataTable dtGroupedResult = new DataTable();
            dtGroupedResult.Columns.Add("userid");
           dtGroupedResult.Columns.Add("sumamount", typeof(Decimal));

            dt.AsEnumerable().GroupBy(e => e["userid"]).Select(e => new { userid = e.Key, sumammount = e.Sum(t => Convert.ToDecimal(t["amount"])) }).ToList().ForEach(e => { dtGroupedResult.Rows.Add(e.userid, e.sumammount); });

这篇关于如何在数据表上运行LINQ的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！