C# 从大型 DataTable 列中选择不同的值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17466253/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Select distinct values from a large DataTable column
提问by user2545743
I have a DataTable with 22 columns and one of the columns I have is called "id". I would like to query this column and keep all the distinct values in a list. The table can have between 10 and a million rows.
我有一个包含 22 列的数据表，其中一列称为“id”。我想查询此列并将所有不同的值保留在列表中。该表可以有 10 到 100 万行。
What is the best method to do this? Currently I am using a for loop to go though the column and compare the values and if the values are the same then the it goes to the next and when not the same it adds the id to the array. But as the table can have 10 to a million rows is there a more efficient way to do this! How would I go about doing this more efficiently?
执行此操作的最佳方法是什么？目前我正在使用 for 循环遍历列并比较值，如果值相同，则转到下一个，如果不相同，则将 id 添加到数组中。但是由于表可以有 10 到一百万行，所以有更有效的方法来做到这一点！我将如何更有效地做到这一点？
采纳答案by Ehsan
Method 1:
方法一：
DataView view = new DataView(table);
DataTable distinctValues = view.ToTable(true, "id");
Method 2:You will have to create a class matching your datatable column names and then you can use the following extension method to convert Datatable to List
方法 2：您必须创建一个与您的数据表列名称匹配的类，然后您可以使用以下扩展方法将数据表转换为列表
public static List<T> ToList<T>(this DataTable table) where T : new()
{
List<PropertyInfo> properties = typeof(T).GetProperties().ToList();
List<T> result = new List<T>();
foreach (var row in table.Rows)
{
var item = CreateItemFromRow<T>((DataRow)row, properties);
result.Add(item);
}
return result;
}
private static T CreateItemFromRow<T>(DataRow row, List<PropertyInfo> properties) where T : new()
{
T item = new T();
foreach (var property in properties)
{
if (row.Table.Columns.Contains(property.Name))
{
if (row[property.Name] != DBNull.Value)
property.SetValue(item, row[property.Name], null);
}
}
return item;
}
and then you can get distinct from list using
然后你可以使用
YourList.Select(x => x.Id).Distinct();
Please note that this will return you complete Records and not just ids.
请注意，这将返回完整的记录，而不仅仅是 ID。
回答by Rajeev Kumar
This will retrun you distinct Ids
这将返回您不同的 ID
var distinctIds = datatable.AsEnumerable()
.Select(s=> new {
id = s.Field<string>("id"),
})
.Distinct().ToList();
回答by Veer
dt- your data table name
dt- 您的数据表名称
ColumnName- your columnname i.e id
ColumnName- 您的列名，即 ID
DataView view = new DataView(dt);
DataTable distinctValues = new DataTable();
distinctValues = view.ToTable(true, ColumnName);
回答by Alex Filipovici
Try this:
尝试这个：
var idColumn="id";
var list = dt.DefaultView
.ToTable(true, idColumn)
.Rows
.Cast<DataRow>()
.Select(row => row[idColumn])
.ToList();
回答by Mou
Sorry to post answer for very old thread. my answer may help other in future.
很抱歉为非常旧的线程发布答案。我的回答将来可能会帮助其他人。
string[] TobeDistinct = {"Name","City","State"};
DataTable dtDistinct = GetDistinctRecords(DTwithDuplicate, TobeDistinct);
//Following function will return Distinct records for Name, City and State column.
public static DataTable GetDistinctRecords(DataTable dt, string[] Columns)
{
DataTable dtUniqRecords = new DataTable();
dtUniqRecords = dt.DefaultView.ToTable(true, Columns);
return dtUniqRecords;
}
回答by minerupset
All credit to Rajeev Kumar's answer, but I received a list of anonymous type that evaluated to string, which was not as easy to iterate over. Updating the code as below helped to return a List that was more easy to manipulate (or, for example, drop straight into a foreach block).
所有功劳都归功于 Rajeev Kumar 的回答，但我收到了一个评估为字符串的匿名类型列表，它并不容易迭代。更新如下代码有助于返回一个更易于操作的 List（或者，例如，直接放入 foreach 块）。
var distinctIds = datatable.AsEnumerable().Select(row => row.Field<string>("id")).Distinct().ToList();
