vb.net – Linq连接两个共享列并将结果放入新数据表的数据表

发布时间：2020-12-17 07:15:00 所属栏目：百科来源：网络整理

导读：我有以下数据表表格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 | y

我有以下数据表

表格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;
        }

    }
}

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!