我有一个包含4列的DataTable，如下所示：
private static DataSet dataSet;
private const string tableName = "MyTable";
private const string columnName1 = "Supplier"; //Column names
private const string columnName2 = "Invoice";
private const string columnName3 = "Item";
private const string columnName4 = "Amount";
我使用下面的linq查询，按照供应商、发票列对表进行分组，并计算了金额的总和：
private static DataTable GroupQueryA(DataTable dataTable)
{
DataTable groupedTable = dataTable.AsEnumerable()
.GroupBy(r => new { Key1 = r.Field<string>(columnName1), Key2 = r.Field<string>(columnName2) })
.Select(g => new GroupSum
{
Key1 = g.Key.Key1,
Key2 = g.Key.Key2,
Sum = g.Sum(x => x.Field<double>(columnName4))
}).PropertiesToDataTable<GroupSum>();
return groupedTable;
}
我声明的GroupSum类型如下：
private class GroupSum
{
public string Key1 { get; set; }
public string Key2 { get; set; }
public Double Sum { get; set; }
}
我从以下链接中复制了PropertiesToDataTable()方法：
使用Linq将Datatable按多列分组并求和转换为Datatable
它非常完美地工作，对于如下的表格行：
AddRow(dataTable, "SA", "INVA", "ITA", 10);
AddRow(dataTable, "SA", "INVA", "ITB", 20);
AddRow(dataTable, "SB", "INVB", "ITC", 50);
我的查询结果返回了2行:
"SA", "INVA", 30
"SB", "INVB", 50
然而，我决定修改我的查询，不再使用匿名类型，而是编写了以下代码:
public class GroupKeys
{
public string Key1 { get; set; }
public string Key2 { get; set; }
}
以及查询。
private static DataTable GroupQueryB(DataTable dataTable)
{
DataTable groupedTable = dataTable.AsEnumerable()
.GroupBy(r => new GroupKeys { Key1 = r.Field<string>(columnName1), Key2 = r.Field<string>(columnName2) })
.Select(g => new GroupSum
{
Key1 = g.Key.Key1,
Key2 = g.Key.Key2,
Sum = g.Sum(x => x.Field<double>(columnName4))
}).PropertiesToDataTable<GroupSum>();
return groupedTable;
}
现在对于相同的源数据，我收到了不同的结果：
"SA", "INVA", 10
"SA", "INVA", 20
"SB", "INVB", 50
虽然查询中唯一的区别在于一行代码，但似乎源数据根本没有分组。
//QueryA
.GroupBy(r => new { Key1 = r.Field<string>(columnName1), Key2 = r.Field<string>(columnName2) })
//QueryB
.GroupBy(r => new GroupKeys { Key1 = r.Field<string>(columnName1), Key2 = r.Field<string>(columnName2) })
请问有人能解释一下吗？ 对于那些想在Visual Studio中运行测试的人，请查看下面的完整源代码。
internal static class TestForStackOverflow
{
private static DataSet dataSet;
private const string tableName = "MyTable";
private const string columnName1 = "Supplier"; //Column names
private const string columnName2 = "Invoice";
private const string columnName3 = "Item";
private const string columnName4 = "Amount";
private class GroupKeys
{
public string Key1 { get; set; }
public string Key2 { get; set; }
}
private class GroupSum
{
public string Key1 { get; set; }
public string Key2 { get; set; }
public Double Sum { get; set; }
}
public static void Test()
{
DataTable dataTable = InitializeDataTable();
//DataTable groupedTable = GroupQueryA(dataTable); //Please uncomment to run test A
DataTable groupedTable = GroupQueryB(dataTable); //Please uncomment to run test B
DisplayData(groupedTable);
}
private static DataTable GroupQueryA(DataTable dataTable)
{
DataTable groupedTable = dataTable.AsEnumerable()
.GroupBy(r => new { Key1 = r.Field<string>(columnName1), Key2 = r.Field<string>(columnName2) })
.Select(g => new GroupSum
{
Key1 = g.Key.Key1,
Key2 = g.Key.Key2,
Sum = g.Sum(x => x.Field<double>(columnName4))
}).PropertiesToDataTable<GroupSum>();
return groupedTable;
}
private static DataTable GroupQueryB(DataTable dataTable)
{
DataTable groupedTable = dataTable.AsEnumerable()
.GroupBy(r => new GroupKeys { Key1 = r.Field<string>(columnName1), Key2 = r.Field<string>(columnName2) })
.Select(g => new GroupSum
{
Key1 = g.Key.Key1,
Key2 = g.Key.Key2,
Sum = g.Sum(x => x.Field<double>(columnName4))
}).PropertiesToDataTable<GroupSum>();
return groupedTable;
}
private static System.Data.DataTable PropertiesToDataTable<T>(this System.Collections.Generic.IEnumerable<T> source)
{
System.Data.DataTable dt = new System.Data.DataTable();
//Weź listę właściwości typu <T> i dla każdej właściwości dodaj do tabeli kolumnę tego samego typu co właściwość
var props = System.ComponentModel.TypeDescriptor.GetProperties(typeof(T));
foreach (System.ComponentModel.PropertyDescriptor prop in props)
{
System.Data.DataColumn dc = dt.Columns.Add(prop.Name, prop.PropertyType);
dc.Caption = prop.DisplayName;
dc.ReadOnly = prop.IsReadOnly;
}
//Kopiuj rekordy z kwerendy do DataTable
foreach (T item in source)
{
System.Data.DataRow dr = dt.NewRow();
foreach (System.ComponentModel.PropertyDescriptor prop in props)
{
dr[prop.Name] = prop.GetValue(item);
}
dt.Rows.Add(dr);
}
return dt;
}
private static DataTable InitializeDataTable()
{
dataSet = new DataSet();
DataTable dataTable = dataSet.Tables.Add(tableName);
dataTable.Columns.Add( columnName1, typeof(string));
dataTable.Columns.Add( columnName2, typeof(string));
dataTable.Columns.Add( columnName3, typeof(string));
dataTable.Columns.Add( columnName4, typeof(double));
AddRow(dataTable, "SA", "INVA", "ITA", 10);
AddRow(dataTable, "SA", "INVA", "ITB", 20);
AddRow(dataTable, "SB", "INVB", "ITC", 50);
return dataTable;
}
private static void AddRow( DataTable dataTable, string supplier, string invoice, string item, double amount)
{
DataRow row = dataTable.NewRow();
row[columnName1] = supplier;
row[columnName2] = invoice;
row[columnName3] = item;
row[columnName4] = amount;
dataTable.Rows.Add(row);
}
private static void DisplayData(System.Data.DataTable table)
{
foreach (System.Data.DataRow row in table.Rows)
{
foreach (System.Data.DataColumn col in table.Columns)
{
Console.WriteLine("{0} = {1}", col.ColumnName, row[col]);
}
Console.WriteLine("============================");
}
}
}
GetHashCode/Equals，所以对于您自己的类型，您需要自己实现它们。而您没有这样做，因此导致了GroupBy的差异。 - Ivan Stoev