如何将两个相同的数据库表与LINQ匹配？

I want to match 2 identical tables:

我想匹配2个相同的表:

sourceProducts (productName, ProductionDate, ManID, shipper, distributer)
CommProducts   (productName, ProductionDate, ManID, shipper, distributer)

but the number of rows and the record contents may differ. How do I select a certain record = raw from one table and get its clone record from the other table (e.g., check if the same record exists)? How do I do this using LinQ?

但行数和记录内容可能不同。如何从一个表中选择某个记录= raw并从另一个表中获取其克隆记录(例如,检查是否存在相同的记录)?我如何使用LinQ执行此操作?

UPDATE: Here's the LINQ code:

更新:这是LINQ代码:

    protected void checkBtn_Click(object sender, EventArgs e)
    {

        MyProductsDataContext mySdb = new MyProductsDataContext();

        Product   mypro = new Product  { ManId = int.Parse(TxtManI.Text), ProductName = TxtProN.Text, ProductionDate =DateTime .Parse ( TxtProDat.Text), Shipper = TxtShipI.Text, Distributer = TxtDistI.Text };

        var spro = (from p in mySdb.Products
                        select new { p.ManId, p.ProductName, p.ProductionDate, p.Shipper, p.Distributer }).
                        Intersect(from s in mySdb.SourceProducts  select new { s.ManId, s.ProductName, s.ProductionDate, s.Shipper, s.Distributer });

        if (spro != null)
        {
            LblMessage.Text = "Acceptable product Data Inserted Sucessfully";
            InsertData();
        }
        else
        {
            LblMessage.Text = "Invalid Product or bad Entry Please retype";
        }
   }

2 个解决方案

#1

I would join on ManId and then compare the rest of the values in a where clause:

我将加入ManId,然后比较where子句中的其余值:

bool productExists = (
    from p in mySdb.Products
    join s in mySdb.SourceProducts
      on p.ManId equals s.ManId
    where p.ProductName == s.ProductName
       && p.ProductionDate == s.ProductionDate
       && p.Shipper == s.Shipper
       && p.Distributer = s.Distributer
    select new { p.ManId, p.ProductName, p.ProductionDate, p.Shipper, p.Distributer }
    ).Any();

if (productExists)
{
    LblMessage.Text = "Acceptable product Data Inserted Sucessfully";
    InsertData();
}
else
{
    LblMessage.Text = "Invalid Product or bad Entry Please retype";
}

I've used Any() to produce an efficient EXISTS SQL query. You could use SingleOrDefault() or FirstOrDefault() instead if you actually need to use the product returned.

我使用Any()来生成有效的EXISTS SQL查询。如果您确实需要使用返回的产品,则可以使用SingleOrDefault()或FirstOrDefault()。

I also don't see anywhere that you're using your new Product's ID - you might need to add that filter to the query as well:

我也没有看到您使用新产品ID的任何地方 - 您可能还需要将该过滤器添加到查询中:

Product   mypro = new Product  { ... };

bool productExists = (
    from p in mySdb.Products
    where p.ManId equals mypro.ManId
    join s in mySdb.SourceProducts
      on p.ManId equals s.ManId
    ...

#2

You can probably do this using a join but I've hobbled together a unit test which shows one way to this

您可以使用连接执行此操作,但我已经将单元测试结合在一起,显示了一种方法

public class TestProduct
{
    public int ManId { get; set; }
    public string ProductName { get; set; }
    public DateTime ProductionDate { get; set; }
    public string Shipper { get; set; }
    public string Distributor { get; set; }
}

[TestMethod]
public void TestSourceTable()
{
    // Set up a test list
    var list = new List<TestProduct>();
    for (int i=0;i<5;i++)
    {
        var p = new TestProduct
            {
                Distributor = "D" + i,
                ManId = i,
                ProductionDate = DateTime.Now,
                ProductName = "P" + i,
                Shipper = "S" + i
            };
        list.Add(p);
    }

    // Get an existing product
    var existingProduct = list[4];

    // Get an unknown product
    var unknownProduct = new TestProduct()
        {
            ManId = -1,
            Distributor = "",
            ProductionDate = DateTime.Now.AddDays(-1),
            ProductName = "",
            Shipper = ""
        };

     // product found
     Assert.True(list.Any(p => p == existingProduct));

     // product not found
     Assert.False(list.Any(p => p == unknownProduct));
}

#1