Linq 更新和合并两个结果集(来自数据表)

本文关键字：结果 数据表 两个 更新 合并 Linq | 更新日期: 2023-09-27 18:36:31

我有两个表： 

Customer:                
+------------------------------+
|   ID        |    Address     |
|------------------------------|
|   1         | London, UK     |
|   2         | Paris, France  |
+------------------------------+
Updated Customer:                
+------------------------------+
|   ID        |    Address     |
|------------------------------|
|   1         | Birmingham, UK |
+------------------------------+

如何合并表以获得此结果？ ：

Customer:                
+------------------------------+
|   ID        |    Address     |
|------------------------------|
|   1         | Birmingham, UK |
|   2         | Paris, France  |
+------------------------------+

C#/Linq 代码我尝试过联合：

DataTable customer = new DataTable();
customer.Columns.Add("ID", typeof(int));
customer.Columns.Add("Address", typeof(string));
DataTable updatedCustomer = new DataTable();
updatedCustomer.Columns.Add("ID", typeof(int));
updatedCustomer.Columns.Add("Address", typeof(string));
customer.Rows.Add(1, "London, UK");
customer.Rows.Add(2, "Paris, France");
updatedCustomer.Rows.Add(1, "Birmingham, UK");
var cust = from row in customer.AsEnumerable()
select new
{
    ID = row[0],
    Address = row[1]
};
var uCust = from row in updatedCustomer.AsEnumerable()
select new
{
    ID = row[0],
    Address = row[1]
};
var updatedTable = cust.Union(uCust);
//Please use cust and uCust objects, not customer and UpdatedCustomer.

但是，联盟给了我一张包含所有 3 行的表格。

Linq 更新和合并两个结果集(来自数据表)

您可以编写自己的比较器来比较 ID 

public class IDComparer  : IEqualityComparer<DataRow> 
{   
    public bool Equals(DataRow x, DataRow y)
    {           
        return (int) x["ID"] == (int) y["ID"] ;
    }
    public int GetHashCode(DataRow obj)
    {                           
        return (int) obj["ID"] ;
    }               
}

然后使用它，

var updatedTable = uCust.Union(cust, new IDComparer ())

添加主键并使用合并将获得答案。

    DataTable customer = new DataTable();
    var customerIdColumn = new DataColumn("ID", typeof(int));
    customer.Columns.Add(customerIdColumn);
    customer.Columns.Add("Address", typeof(string));
    customer.PrimaryKey = new[] { customerIdColumn };
    DataTable updatedCustomer = new DataTable();
    var updatedCustomerIdColumn = new DataColumn("ID", typeof(int));
    updatedCustomer.Columns.Add(updatedCustomerIdColumn);
    updatedCustomer.Columns.Add("Address", typeof(string));
    updatedCustomer.PrimaryKey = new[] { updatedCustomerIdColumn };
    customer.Rows.Add(1, "London, UK");
    customer.Rows.Add(2, "Paris, France");
    updatedCustomer.Rows.Add(1, "Birmingham, UK");

    customer.Merge(updatedCustomer, false);

编辑

要使用 linq 执行此操作，您需要一个 not in。 为了清楚起见，我把它做得相当冗长。 但是，您应该能够非常轻松地使代码更加简洁。

var cust = customer.AsEnumerable();
var uCust = updatedCustomer.AsEnumerable();
var newCust = (from c in cust join u in uCust on c.Field<int>("ID") equals u.Field<int>("ID") select u);
var newUCust =
    (from cu in cust where !(from ucu in uCust select ucu.Field<int>("ID")).Contains(cu.Field<int>("ID")) select cu);
var joined = newCust.Concat(newUCust);

var query=from c in customer.AsEnumerable()
         join uc in updatedCustomer.AsEnumerable()
         on c.Field<int>("ID") equals uc.Field<int>("ID") into lf
         from uc in lf.DefaultIfEmpty()
         select new
         {
             ID=c.Field<int>("ID"),
             Address=uc==null?c.Field<string>("Address"):uc.Field<string>("Address")
         };
//this will get the result you want,but it is not DataTable.
//you need to convert query to datatable .
DataTable result =customer.Clone();
query.ToList().ForEach(q=>result.Rows.Add(q.ID,q.Address));
相关文章：