如何用VB.NET Linq连接共享列并生成新数据表？

本文共计597个文字，预计阅读时间需要3分钟。

我拥有一份数据表，表格如下：+------+------+| catalogid | name | snum |+------+------+| 353 | xx | 4 || 364 | yy | 3 || 882 | zz | 3 || 224 | mm | 71 || 999 | kk | 321 || 74 | kk | 4 || 54 | ii | 5 || 11 | u | 6 |+------+------+

我有以下数据表

表格1：

+-----------+------+------+ | catalogid | name | snum | +-----------+------+------+ | 353 | xx | 4 | | 364 | yy | 3 | | 882 | zz | 3 | | 224 | mm | 71 | | 999 | kk | 321 | | 74 | kk | 4 | | 54 | ii | 5 | | 11 | u | 6 | | 23 | yy | 6 | +-----------+------+------+

表2：

+-----------+----------+--------------+ | catalogid | numitems | ignoreditems | +-----------+----------+--------------+ | 353 | 4 | 0 | | 364 | 10 | 0 | | 882 | 2 | 0 | | 224 | 0 | 7 | +-----------+----------+--------------+

使用LINQ我想加入它们并将结果复制到新的数据表.它们都共享catalogid,并且在结果中它应该只显示其tableid存在于table2中的记录

结果：

+-----------+------+------+-----------+---------------+ | catalogid | name | snum | numitems | ignoreditems | +-----------+------+------+-----------+---------------+ | 353 | xx | 4 | 4 | 0 | | 364 | yy | 3 | 10 | 0 | | 882 | zz | 3 | 2 | 0 | | 224 | mm | 71 | 0 | 7 | +-----------+------+------+-----------+---------------+

这是我的尝试,但它不起作用：

Dim query = From a In oresult.AsEnumerable Group Join b In products.AsEnumerable On a.Field(Of Integer)("catalogid") Equals b.Field(Of Integer)("catalogid") Into Group query.copytodatatable

CopyToDatatable无法正常工作,我无法弄清楚原因

CopyToDataTable()仅在查询返回IEnumerable<'DataRow>时有效.在您的查询中,您将返回匿名类型.匿名类型不携带CopyToDataTable()的扩展方法.

您可以使用下面列出的ConvertToDataTable扩展创建表.你必须将它转换为VB.NET(如果你谷歌,有转换器).

using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data; using Common; namespace TestConsole { public class Linq_join_2_datatables_that_share_a_column_and_put_result_in_new_datatable { public class Table1 { public int CatalogId { get; set; } public string Name { get; set; } public int SNum { get; set; } } public class Table2 { public int CatalogId { get; set; } public int NumItems { get; set; } public int IgnoredItems { get; set; } } public static void Start() { DataTable table1 = new DataTable(); table1.Columns.Add("catalogid", typeof(int)); table1.Columns.Add("name", typeof(string)); table1.Columns.Add("snum", typeof(int)); DataRow row = table1.Rows.Add(353, "xx", 4); DataTable table2 = new DataTable(); table2.Columns.Add("catalogid", typeof(int)); table2.Columns.Add("numitems", typeof(int)); table2.Columns.Add("ignoreditems", typeof(int)); table2.Rows.Add(353, 4, 0); var query = (from t1 in table1.AsEnumerable() join t2 in table2.AsEnumerable() on t1.Field<int>("catalogid") equals t2.Field<int>("catalogid") select new { catalogid = t1.Field<int>("catalogid"), name = t1.Field<string>("name"), snum = t1.Field<int>("snum"), numitems = t2.Field<int>("numitems"), ignoreditems = t2.Field<int>("ignoreditems") }).ToList(); DataTable table3 = query.ConvertToDataTable(); } } } using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data; using System.ComponentModel; using System.Reflection; namespace Common { public static class DataTableExtensions { public static DataTable ConvertToDataTable<T>(this IList<T> data) { PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(T)); DataTable table = new DataTable(); foreach (PropertyDescriptor prop in properties) table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType); foreach (T item in data) { DataRow row = table.NewRow(); foreach (PropertyDescriptor prop in properties) row[prop.Name] = prop.GetValue(item) ?? DBNull.Value; table.Rows.Add(row); } table.AcceptChanges(); return table; } } }