How do I match two identical database tables with LINQ?

Question

I want to match 2 identical tables:

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?

UPDATE: Here's the LINQ code:

    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";
        }
   }

Answer 1

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));
}

Answer 2

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

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.

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:

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
    ...