如何组合两个数据表并合并到新的数据表

Question

我有一个代码，将读取工作簿中的所有excel文件，并将保存到数据集中的数据表。现在基于一定的条件，我必须合并数据表在新的一个和存储到一个oracle数据库。下面的代码我用来合并两个数据表dt1和dt2到新的表dtnew，但结果是奇怪的。如果第一个数据表有3行，第二个有6行，它给出18行结果，而不是只给出匹配的行值，这意味着这里3行包含两个表值。在Nutshell中，我创建了第三个表dtnew，并设计了结构，然后使用了condition .逻辑上有什么问题或者有更好的方法

DataSet ds = Exceltotable(@name);
                        for (int i = 0; i < ds.Tables.Count; i++)
                        {
                            if (ds.Tables[i].TableName == "Table2")
                            {
                                dt1 = ds.Tables[i];
                            }
                            if (ds.Tables[i].TableName == "Table3")
                            {
                                dt2 = ds.Tables[i];
                            }
                            if (dt1 != null && dt2 != null)
                            {
                                break;
                            }
                        }
                        System.Data.DataTable dtnew = new System.Data.DataTable();
                        dtnew.Columns.Add("LOCATION", typeof(string));
                        dtnew.Columns.Add("RBSSHARE", typeof(string));
                        dtnew.Columns.Add("VENDOR", typeof(string));
                        dtnew.Columns.Add("PWRTYPE", typeof(string));
                        dtnew.Columns.Add("EQPTINSTYPE", typeof(string));
                        dtnew.Columns.Add("BTSHEIGHT", typeof(string));
                        dtnew.Columns.Add("BTSFAN", typeof(string));
                        dtnew.Columns.Add("HMSAVAIL", typeof(string));
                        dtnew.Columns.Add("MODELNAME", typeof(string));
                        dtnew.Columns.Add("MODELNO", typeof(string));
                        dtnew.Columns.Add("SERIALNO", typeof(string));
                        dtnew.Columns.Add("REVNO", typeof(string));
                        dtnew.Columns.Add("TECHNOLOGY", typeof(string));
                        dtnew.Columns.Add("Asset description", typeof(string));
                        dtnew.Columns.Add("Asset Category", typeof(string));
                        dtnew.Columns.Add("ASSETTAG", typeof(string));
                        dtnew.Columns.Add("ADDREMARK", typeof(string));
                        dtnew.Columns.Add("DELIVERYDATE", typeof(string));
                        var query = from  pp in dt1.AsEnumerable() 
                                    join ii in dt2.AsEnumerable()
                                        on pp.Field<string>("LOCATION") equals
                                            ii.Field<string>("LOCATION")
                                    select dtnew.LoadDataRow(new object[]
{
pp.Field<string>("LOCATION"),
pp.Field<string>("RBSSHARE"),
pp.Field<string>("VENDOR"),
pp.Field<string>("PWRTYPE"),
pp.Field<string>("EQPTINSTYPE"),
pp.Field<string>("BTSHEIGHT"),
pp.Field<string>("BTSFAN"),
pp.Field<string>("HMSAVAIL"),
pp.Field<string>("MODELNAME"),
pp.Field<string>("MODELNO"),
pp.Field<string>("SERIALNO"),
pp.Field<string>("REVNO"),
ii.Field<string>("TECHNOLOGY"),
ii.Field<string>("TECHNOLOGY"),
ii.Field<string>("Asset description"),
ii.Field<string>("ASSETTAG"),
ii.Field<string>("ADDREMARK"),
ii.Field<string>("DELIVERYDATE")
}, false);
                        query.CopyToDataTable();
                        ds.Tables.Add(dtnew);

Answer 1

试试下面的代码:

var query = 
    from row1 in dt1.AsEnumerable()
    from row2 in dt2.AsEnumerable()
                   .Where(x => x["LOCATION"] == row1["LOCATION"])
                   .DefaultIfEmpty()
    select dtNew.LoadDataRow(new object[]
    {
        row1.Field<string>("LOCATION"),
        row1.Field<string>("RBSSHARE"),
        row1.Field<string>("VENDOR"),
        row1.Field<string>("PWRTYPE"),
        row1.Field<string>("EQPTINSTYPE"),
        row1.Field<string>("BTSHEIGHT"),
        row1.Field<string>("BTSFAN"),
        row1.Field<string>("HMSAVAIL"),
        row1.Field<string>("MODELNAME"),
        row1.Field<string>("MODELNO"),
        row1.Field<string>("SERIALNO"),
        row1.Field<string>("REVNO"),
        row2 == null ? null : row2.Field<string>("TECHNOLOGY"),
        row2 == null ? null : row2.Field<string>("TECHNOLOGY"),
        row2 == null ? null : row2.Field<string>("Asset description"),
        row2 == null ? null : row2.Field<string>("ASSETTAG"),
        row2 == null ? null : row2.Field<string>("ADDREMARK"),
        row2 == null ? null : row2.Field<string>("DELIVERYDATE")
    }, false);