使用动态列名称uisng LINQ查询数据表 [英] Query datatable with dynamic column names uisng LINQ
问题描述
我将通过分享我已经阅读了与LINQ相关的MSDN站点（这是针对数据库使用已知列名而不是内存数据集和数据表）并且花了大约20个小时搜索各种论坛来解释这个问题。解决我必须解决的问题。
我有一个 c＃ Winforms应用程序，它将接受一个CSV文件并将数据加载到数据表。读取数据表列名称并将其填充到另一个数据表中，该数据表用于将源数据表映射到规范化的最终数据表。向用户显示一个屏幕，其中提供有关哪个源列与哪个目标列匹配的输入。
问题
如何使用映射表仅复制目标表所需的源表中的列，以告知应该去哪里。在SQL中，我会通过动态SQL执行此操作，例如：
DECLARE @sql nvarchar （ 4000 ）
- 这将创建动态查询语句并嵌入源列名称的连接列表（STUFF）如果未映射列以避免列数不匹配，则函数处理映射到目标列的串联或放入空字符串
SELECT @sql = ' SELECT' + STUFF（（ SELECT ' ，' + ISNULL（m.src，' '） FROM tbl_map m FOR XML PATH（' '））， 1 ， 1 ，' '）+ ' FROM tbl_src'
- 这将使用源表插入记录对目标表的动态查询
INSERT INTO target_table
EXEC （ @ sql ）
同样，我在任何地方都没有读过任何关于使用数据表作为预先不知道列名的来源的事。
示例来源：
------------------------ ---------- -------------------------------------
| FName | LName | DOB | SSN |性别| LicNbr |国家|
|鲍勃|史密斯| 19230105 | 123456789 | M | N4257665 | NJ |
|简| Doe | 19670530 | 000000000 | F | 12457890 | PA |
---------------------------------------------- -------------------------
样本映射：
--------------------------
|目标|来源|
| first_name | FName |
| last_name | LName |
| license_state |国家|
| license_id | LicNbr |
--------------------------
预期的样本输出：
-------------------- -----------------------------------
| first_name | last_name | license_state | license_id |
|鲍勃|史密斯| NJ | N4257665 |
|简| Doe | PA | 12457890 |
---------------------------------------------- ---------
以下是涉及的数据集/数据表：
// 用于保存源数据表和目标数据表之间映射的数据
DataTable dt = new DataTable（ dt_map ）;
// 将列添加到映射表
DataColumn dc = new DataColumn（ source， typeof （ string ））;
dc.DefaultValue = string .Empty;
dt.Columns.Add（dc）;
dc = new DataColumn（ target， typeof （ string ））;
dc.DefaultValue = string .Empty;
dt.Columns.Add（dc）;
// 加载填充目标表列名称的初始行数据以映射到
dt.BeginLoadData（）;
dt.Rows.Add（ first_name， ）;
dt.Rows.Add（ last_name， ）;
dt.Rows.Add（ license_state， ）;
dt.Rows.Add（ license_id， ）;
dt.EndLoadData（）;
// 将映射数据表添加到数据集
ds_mstr .Tables.Add（dt.Copy（））;
// 创建使用的最终规范化数据表
dt = new DataTable（ dt_final ）;
// 将列添加到最终数据表
dc = new DataColumn（ first_name，< span class =code-keyword> typeof （ string ））;
dc.DefaultValue = string .Empty;
dt.Columns.Add（dc）;
dc = new DataColumn（ last_name， typeof （ string ））;
dc.DefaultValue = string .Empty;
dt.Columns.Add（dc）;
dc = new DataColumn（ license_state， typeof （ string ））;
dc.DefaultValue = string .Empty;
dt.Columns.Add（dc）;
dc = new DataColumn（ license_id， typeof （ string ））;
dc.DefaultValue = string .Empty;
dt.Columns.Add（dc）;
// 将最终数据表添加到数据集
ds_mstr .Tables.Add（dt.Copy（））;
同样，源列名称是未知的，直到从文件，命名和数量会有所不同。为简单起见，假设所有内容都被视为字符串。感谢能够提供解决方案/指导的任何人，甚至更多的谢谢，如果有解释可以提供答案，那么我可以在此过程中学到一些东西并成为更好的开发人员！
我的尝试：
搜索可能与我正在寻找的任何地方相近的解决方案或任何东西（ LINQ查询使用映射两个表之间的动态列名称，使用映射两个表之间的列名的表。）
尝试objecttype.GetProperty（variabletype） .GetValue（dragRow，null）
你不知道
对象TypedragRow
objecttype是对象变量
variabletype is改变你得到
嗯......
实现这一目标的方法很少：
- 使用动态查询库
ScottGu&＃博客 - 动态LINQ（第1部分：使用LINQ动态查询库） [ ^ ]
- 使用表达式树
如何：使用表达式树构建动态查询（C＃和Visual Basic） [ ^ ]
带有表达式树的动态LINQ查询 - 简单对话 [ ^ ]
- 使用Func< t，>代表
如果您只想返回存储在字典中的那些字段，您必须提供一种修改数据表对象的列集合的方法。
Func（T， TResult）代表（系统） [ ^ ]
示例：
// 源数据表
DataTable src = new DataTable （）;
src.Columns.Add（ new DataColumn（ FName， typeof （ string ）））;
src.Columns.Add（ new DataColumn（ LName， typeof （ string ）））;
src.Columns.Add（ new DataColumn（ DOB， typeof （ string ）））;
src.Columns.Add（ new DataColumn（ SSN， typeof （ string ）））;
src.Columns.Add（ new DataColumn（ 性别， typeof （ string ）））;
src.Columns.Add（ new DataColumn（ LicNbr， typeof （ string ）））;
src.Columns.Add（ new DataColumn（ 州， typeof （ string ）））;
src.Rows.Add（ new object [] { Bob， Smith， 19230105， 123456789， M， N4257665， NJ}）;
src.Rows.Add（ new object [] { 简， Doe， 19670530， 000000000， F， 12457890， PA}）;
// 地图字段的数据表
DataTable map = new DataTable（）;
map.Columns.Add（ new DataColumn（ 目标， typeof （ string ）））;
map.Columns.Add（ new DataColumn（ 来源， typeof （ string ）））;
map.Rows.Add（ new object [] { first_name， FName参数}）;
map.Rows.Add（ new object [] { last_name， LName的}）;
map.Rows.Add（ new object [] { license_id， LicNbr}）;
map.Rows.Add（ new object [] { license_state， 状态}）;
// 获取KeyValuePair的字典对象<字符串，字符串>
// 此对象将用于将列的一个名称翻译为第二个
Dictionary< string ，字符串> col_map = map.AsEnumerable（）。ToDictionary（x => x.Field< string>（ Source），x => x.Field< string>（ Target））;
// 委托更改列的名称
Func< DataRow，Dictionary< ; string，string>，DataRow> mappedRow = 委托（DataRow行，字典<字符串，字符串> mapp）
{
foreach （DataColumn c in row.Table.Columns）
{
try {
c.ColumnName = mapp [c.ColumnName];
} catch （KeyNotFoundException ex）
{
// < span class =code-comment>忽略错误并继续循环
继续;
}
}
返回行;
};
// 查询
var result = src.AsEnumerable（）
.Select（x = > mappedRow（x，col_map））;
结果：
first_name last_name DOB SSN性别license_id license_state
Bob Smith 19230105 123456789 M N4257665 NJ
Jane Doe 19670530 000000000 F 12457890 PA
其他有趣的资源：
c＃ - 在LINQ查询中按列名引用DataRows，然后通过映射与另一个查询进行过滤数据集 - 堆栈溢出 [ ^ ]
sql server - 使用对象属性，C＃，LINQ映射sql表的列名 - Stack overflow [ ^ ]
I'll preface this question by sharing that I have read the MSDN site related to LINQ (which is geared towards database usage with known column names and not in-memory datasets and datatables) and have spent maybe 20 hours searching various forums for a solution for what I have to solve.
I have an c# Winforms application which will accept a CSV file and load the data into a datatable. The datatable column names are read and populated into another datatable that serves to map the source datatable to the normalized final datatable. The user is presented with a screen where they provide input on which source column matches which destination column.
Problem
How to copy only the columns from the source table needed to the destination table using the mapping table to tell what is supposed to go where. In SQL I would do this via dynamic SQL like:
DECLARE @sql nvarchar(4000)
-- This creates the dynamic query statement and embeds the concatenated list of source column names (the STUFF function handles the concatenation) mapped to the target columns or puts in an empty string if the column was not mapped to avoid a column count mismatch when inserting
SELECT @sql = 'SELECT ' + STUFF((SELECT ',' + ISNULL(m.src,'') FROM tbl_map m FOR XML PATH('')),1,1,'') + ' FROM tbl_src'
-- This inserts the records from the source table using the dynamic query into the target table
INSERT INTO target_table
EXEC(@sql)
Again, nothing I've read anywhere has dealt with the use of datatable as the source where the column names are not known in advance.
Sample Source:
-----------------------------------------------------------------------
| FName | LName | DOB | SSN | Sex | LicNbr | State |
| Bob | Smith | 19230105 | 123456789 | M | N4257665 | NJ |
| Jane | Doe | 19670530 | 000000000 | F | 12457890 | PA |
-----------------------------------------------------------------------
Sample Mapping:
--------------------------
| Target | Source |
| first_name | FName |
| last_name | LName |
| license_state | State |
| license_id | LicNbr |
--------------------------
Sample Output Expected:
-------------------------------------------------------
| first_name | last_name | license_state | license_id |
| Bob | Smith | NJ | N4257665 |
| Jane | Doe | PA | 12457890 |
-------------------------------------------------------
Here are the dataset/datatables involved:
//Datatable to hold the mapping between the source datatable and the target datatable
DataTable dt = new DataTable("dt_map");
//Add columns to the mapping table
DataColumn dc = new DataColumn("source", typeof(string));
dc.DefaultValue = string.Empty;
dt.Columns.Add(dc);
dc = new DataColumn("target", typeof(string));
dc.DefaultValue = string.Empty;
dt.Columns.Add(dc);
//Load initial row data populating the target table column names to map to
dt.BeginLoadData();
dt.Rows.Add("first_name", "");
dt.Rows.Add("last_name", "");
dt.Rows.Add("license_state", "");
dt.Rows.Add("license_id", "");
dt.EndLoadData();
//Add the mapping datatable to the dataset
ds_mstr.Tables.Add(dt.Copy());
//Create the final normalized datatable which be used
dt = new DataTable("dt_final");
//Add columns to the final datatable
dc = new DataColumn("first_name", typeof(string));
dc.DefaultValue = string.Empty;
dt.Columns.Add(dc);
dc = new DataColumn("last_name", typeof(string));
dc.DefaultValue = string.Empty;
dt.Columns.Add(dc);
dc = new DataColumn("license_state", typeof(string));
dc.DefaultValue = string.Empty;
dt.Columns.Add(dc);
dc = new DataColumn("license_id", typeof(string));
dc.DefaultValue = string.Empty;
dt.Columns.Add(dc);
//Add the final datatable to the dataset
ds_mstr.Tables.Add(dt.Copy());
Again, the source column names are unknown until imported from the file and will vary in naming and quantity. Assume everything is being treated as a string for the sake of simplicity. Thank you to whoever can provide a solution/guidance and even more thanks if an explanation can come with the answer so I can learn something along the way and be a better developer for it!
What I have tried:
Searching for solutions or anything that might be anywhere close to what I'm looking for (LINQ query using dynamic columns names mapped between two tables using a table that maps the column names between the two tables).
Try objecttype.GetProperty("variabletype").GetValue(dragRow, null)
you get unknown
object TypedragRow
objecttype is object variable
variabletype is variable the you to get
Well...
There's few ways to achieve that:
- using Dynamic Query Library
ScottGu's Blog - Dynamic LINQ (Part 1: Using the LINQ Dynamic Query Library)[^]
- using Expression Trees
How to: Use Expression Trees to Build Dynamic Queries (C# and Visual Basic)[^]
Dynamic LINQ Queries with Expression Trees - Simple Talk[^]
- using Func<t,> delegate
In case you want to return only those fields which are stored in dictionary, you have to provide a way to modify collection of columns for datatable object.
Func(T, TResult) Delegate (System)[^]
Example:
//source datatable DataTable src = new DataTable(); src.Columns.Add(new DataColumn("FName", typeof(string))); src.Columns.Add(new DataColumn("LName", typeof(string))); src.Columns.Add(new DataColumn("DOB", typeof(string))); src.Columns.Add(new DataColumn("SSN", typeof(string))); src.Columns.Add(new DataColumn("Sex", typeof(string))); src.Columns.Add(new DataColumn("LicNbr", typeof(string))); src.Columns.Add(new DataColumn("State", typeof(string))); src.Rows.Add(new object[]{"Bob", "Smith", "19230105", "123456789", "M", "N4257665", "NJ"}); src.Rows.Add(new object[]{"Jane ", "Doe", "19670530", "000000000", "F", "12457890", "PA"}); //datatable for map fields DataTable map = new DataTable(); map.Columns.Add(new DataColumn("Target", typeof(string))); map.Columns.Add(new DataColumn("Source", typeof(string))); map.Rows.Add(new object[]{"first_name","FName"}); map.Rows.Add(new object[]{"last_name","LName"}); map.Rows.Add(new object[]{"license_id","LicNbr"}); map.Rows.Add(new object[]{"license_state","State"}); //Dictionary object to get KeyValuePair<string, string> //this object will be used to "translate" one name of column into second Dictionary<string, string> col_map = map.AsEnumerable().ToDictionary(x=>x.Field<string>("Source"), x=>x.Field<string>("Target")); //delegate to change the names of columns Func<DataRow, Dictionary<string, string>, DataRow> mappedRow = delegate(DataRow row, Dictionary<string, string> mapp) { foreach(DataColumn c in row.Table.Columns) { try { c.ColumnName = mapp[c.ColumnName]; } catch (KeyNotFoundException ex) { //ignore error and continue for loop continue; } } return row; }; //query var result = src.AsEnumerable() .Select(x=> mappedRow(x, col_map));
Result:
first_name last_name DOB SSN Sex license_id license_state Bob Smith 19230105 123456789 M N4257665 NJ Jane Doe 19670530 000000000 F 12457890 PA
Other interesting resources:
c# - Referencing DataRows by column name in a LINQ query, then filter via mapping with another dataset - Stack Overflow[^]
sql server - Map column names of sql table with an object property, C#, LINQ - Stack Overflow[^]
这篇关于使用动态列名称uisng LINQ查询数据表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
