根据列名列表将一个数据表拆分为多个数据表 [英] Split a Datatable into multiple Datatables based on a list of column names

问题描述

我有一个如下所示的数据表

I have a datatable that looks lik the following

ID        Country      Supplier
515       DE           A
515       CH           A
515       FR           A
516       DE           B
516       FR           B
517       DE           C
517       IT           C

我有一个 List<string> 包含动态数量的列名，例如，如果列表包含单个列:

and i have a List<string> that contins a dynamic number of column names, for example if the list contained a single column:

Supplier

我想从这个表中生成一个 List<DataTable> 或一个 DataSet 并根据该列表中的列名分隔表，所以在这种情况下我只由 Supplier 列，结果将是 3 个如下所示的 DataTables

i want to produce a List<DataTable> or a DataSet from this table and seperate the table based on the column names in that list so in this case i seperate only by the Supplier column, the result will be 3 DataTables that look like the following

    ----------table 1--------
    515       DE           A
    515       CH           A
    515       FR           A
    ----------table 2--------
    516       DE           B
    516       FR           B
    ----------table 3--------  
    517       DE           C
    517       IT           C

但如果列名的 List 包含例如以下内容:

but if the List<string> of column names contained for example the following:

Supplier
Country

结果将是 7 个数据表，每个数据表包含一行

the the result would be 7 Datatables each datatable containing a row

    ----------table 1--------
    515       DE           A
    ----------table 2--------
    515       CH           A
    ----------table 3--------
    515       FR           A
    ----------table 4--------
    516       DE           B
    ----------table 5--------
    516       FR           B
    ----------table 6--------  
    517       DE           C
    ----------table 7--------
    517       IT           C

另一个例子是，如果列名的 List 只包含 Country 列，那么结果将是

another example is, if the List<string> of column names contained only the Country column then the result would be

----------table 1--------
515       DE           A
516       DE           B
517       DE           C
----------table 2--------
515       CH           A
----------table 3--------
515       FR           A
516       FR           B
----------table 4--------
517       IT           C

我如何使用 linq 实现这一点，查询将根据列表中包含的列名是动态的，请您指导我吗?

how can i achive this using linq, the query will be dynamic based on the column names that are contained in the list, could you please guide me?

我已经为 DataTable.Select 和 Select distinct 和嵌套循环使用了一个动态字符串，但它看起来很复杂，我想知道是否有更有效的方法来实现这一点

i have done it already using a daynamic string for DataTable.Select and Select distinct and nested loops but it looks complicated and i wonder if there is more efficient way to achive this

推荐答案

你可能想使用 System.Linq.Dynamic

var dt = new DataTable();
var res = new List<DataTable>();

dt.Columns.Add("ID", typeof(int));
dt.Columns.Add("Country", typeof(string));
dt.Columns.Add("Supplier", typeof(string));
dt.Rows.Add(515, "DE", "A");
dt.Rows.Add(515, "CH", "A");  
dt.Rows.Add(515, "FR", "A");
dt.Rows.Add(516, "DE", "B");
dt.Rows.Add(516, "FR", "B");
dt.Rows.Add(517, "DE", "C");
dt.Rows.Add(517, "IT", "C");

var fields = new List<string>() { "Supplier", "Country"};
var qfields = string.Join(", ", fields.Select(x => "it["" + x + ""] as " + x));
// qfields = "it["Supplier"] as Supplier, it["Country"] as Country"

var q = dt
    .AsEnumerable()
    .AsQueryable()
    .GroupBy("new(" + qfields + ")", "it")
    .Select("new (it as Data)");
foreach (dynamic d in q)
{
    var dtemp = dt.Clone();

    foreach (var row in d.Data)
        dtemp.Rows.Add(row.ItemArray);

    res.Add(dtemp);
}

这篇关于根据列名列表将一个数据表拆分为多个数据表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！