动态LINQ Where查询与其他表的联接 [英] Dynamic LINQ Where query with joining other tables

问题描述

我有几个表(构建程序时不知道确切的表号)，看起来像这样(表与表的行数和列数可能不同):

I have several tables (the exact number is not known when the program is build) looking like this (the number of rows and columns may differ from table to table):

源数据存储在数据集中.现在，我想生成一个新表，其中存储了所有ID的所有数据(图片仅显示ID为10和20的结果，但目标表应包含所有ID的数据):

The source data is stored in a data set. Now I want to generate a new table where all data of all ids is stored (the picture shows only the result for id 10 and 20 but the target table should contain the data for all ids):

等效的SQLite语句如下所示:

The equivalent SQLite statement for that looks like this:

SELECT * FROM Dataset 
JOIN Datensatz2 ON (Dataset.ID=Datensatz2.ID)
JOIN Datensatz3 ON (Datensatz3.ID=Dataset.ID)
JOIN Datensatz4 ON (Datensatz4.ID=Dataset.ID)
WHERE Dataset.Id=10
UNION
SELECT * FROM Dataset 
JOIN Datensatz2 ON (Dataset.ID=Datensatz2.ID)
JOIN Datensatz3 ON (Datensatz3.ID=Dataset.ID)
JOIN Datensatz4 ON (Datensatz4.ID=Dataset.ID)
WHERE Dataset.Id=20
...

双重ID列将在之后删除，因此不必担心.现在的问题是如何将其转换为动态LINQ查询?

The double id columns will be removed afterwards so don´t worry about that. The questions is now how to convert it as a dynamic LINQ query?

推荐答案

有很多悬而未决的问题，但这也许有助于解决.由于表已经存储在DataSet中，因此可以使用Linq-To-DataSet和Enumerable.GroupBy按ID进行分组:

There are plenty of open question but maybe this helps to solve it. Since the tables are already stored in a DataSet you could use Linq-To-DataSet and Enumerable.GroupBy to group by ID:

var idTables = ds.Tables.Cast<DataTable>().Where(t => t.Columns.Contains("Id"));
if(!idTables.Any()){ MessageBox.Show("No id-tables"); return; }

var idRowGroups = idTables.SelectMany(t => t.AsEnumerable())
    .GroupBy(row => row.Field<int>("Id"))
    .Select(grp => new { ID = grp.Key, Rows = grp });

foreach(var idGroup in idRowGroups)
{
  Console.WriteLine("ID:{0} Rows:{1}"
      , idGroup.ID
      , String.Join(" | ", idGroup.Rows.Select(row => String.Join(",", row.ItemArray))));
}

样本数据:

var ds = new DataSet();
DataTable t1 = new DataTable();
t1.Columns.Add("Id", typeof(int));
t1.Columns.Add("Data", typeof(int));
t1.Rows.Add(1, 1);
t1.Rows.Add(2, 10);
t1.Rows.Add(3, 100);
t1.Rows.Add(4, 1000);
ds.Tables.Add(t1);
t1 = new DataTable();
t1.Columns.Add("Id", typeof(int));
t1.Columns.Add("Data", typeof(int));
t1.Rows.Add(4, 5);
t1.Rows.Add(5, 50);
t1.Rows.Add(7, 500);
t1.Rows.Add(3, 5997);
ds.Tables.Add(t1);
t1 = new DataTable();
t1.Columns.Add("Id", typeof(int));
t1.Columns.Add("Data1", typeof(int));
t1.Columns.Add("Data2", typeof(int));
t1.Rows.Add(1, 5, 0);
t1.Rows.Add(3, 7, 1);
t1.Rows.Add(5, 9, 11);
t1.Rows.Add(7, 11, 222);
ds.Tables.Add(t1);

输出:

ID:1 Rows:1,1 | 1,5,0
ID:2 Rows:2,10
ID:3 Rows:3,100 | 3,5997 | 3,7,1
ID:4 Rows:4,1000 | 4,5
ID:5 Rows:5,50 | 5,9,11
ID:7 Rows:7,500 | 7,11,222

这篇关于动态LINQ Where查询与其他表的联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！