locked
Comparing Two Tables in Lightswitch RRS feed

  • Question

  • Hi,

    I've asked a similar question before but I didn't get an answer that was easy for me to implement.

    Basically the problem isn't that complicated. I have two source tables which I need to compare on certain fields and put the records/entities that don't match in two target tables. The comparison is done on the server.

    So, all records in the left table that have a certain field value (customer id) that don't exist in the right table, so are unique to the left table need to be added to the left target table. Customer id's in the right table that don't exist in the left table need to be added to the right target table. Other field values could be added to the comparison, but never will entities be compared on all fields.

    The suggestion I got before was to implement the IEqualityComparer interface but that just seems to be a bit much for this task and also I have had trouble finding a good example on how to implement this.

    Is there a simple way to do this? I'm pretty sure there is, but I can't seem to come up with one myself.

    Regards,

    H.

    Thursday, April 19, 2012 10:08 AM

Answers

  • "So, all records in the left table that have a certain field value (customer id) that don't exist in the right table..."

    var missingCustomers = from c in Customers
                       
    where !listCustomerIds.Contains(c.CustomerId )
                       
    select c;

    or similar...


    Xpert360 blog | twitter : @xpert360 Opinions are my own. Please mark as answer if this helps solve your problem.

    • Marked as answer by Hoorah Thursday, April 19, 2012 1:56 PM
    Thursday, April 19, 2012 1:18 PM

All replies

  • My first thought is all this should be in the database be it view(s), scheduled SQL tasks, stored procs, triggers or whatever technology takes your fancy. Depends on lots of thing including volume of data, update frequency, etc. This is the sort of task that TSQL and SQL server excel at.

    Xpert360 blog | twitter : @xpert360 Opinions are my own. Please mark as answer if this helps solve your problem.

    Thursday, April 19, 2012 11:00 AM
  • My first thought is all this should be in the database be it view(s), scheduled SQL tasks, stored procs, triggers or whatever technology takes your fancy. Depends on lots of thing including volume of data, update frequency, etc. This is the sort of task that TSQL and SQL server excel at.

    Xpert360 blog | twitter : @xpert360 Opinions are my own. Please mark as answer if this helps solve your problem.

    This need to be done once a day for approximately 1000 records. I need to do this within the application, not the database.
    Thursday, April 19, 2012 11:03 AM
  • The database is part of your application unless it is remote and out fo your control. You could probably do what you want to once per day in 2 TSQL statements taking a couple of seconds. We would normally solve this at the database side in less time than this thread has taken up between us. We would not give it a second thought.

    However, if you need to do this in the middle tier than provided you have access to the two collections/lists of data in some format available to LINQ then a LINQ query can mimic precisely what TSQL could do in this instance. If you needed to persist the results then that part is more expensive. You could consider storing lists of record id's if you don't need any calculated columns.

    To that end you can visit a site I highly recommend if you need some LINQ help and advice: http://www.linqpad.net/WhyLINQBeatsSQL.aspx . That said you scenario may fall into the section "When not to use LINQ for querying databases" in which case you should do it in SQL or operate on collections/lists if readily available.


    Xpert360 blog | twitter : @xpert360 Opinions are my own. Please mark as answer if this helps solve your problem.

    • Edited by Xpert360 Thursday, April 19, 2012 12:12 PM
    Thursday, April 19, 2012 12:05 PM
  • "You could probably do what you want to once per day in 2 TSQL statements taking a couple of seconds [...] a LINQ query can mimic precisely what TSQL could do in this instance."

    Can you share how I could do this, using these LINQ queries, if it is that simple?

    Thursday, April 19, 2012 12:25 PM
  • "So, all records in the left table that have a certain field value (customer id) that don't exist in the right table..."

    var missingCustomers = from c in Customers
                       
    where !listCustomerIds.Contains(c.CustomerId )
                       
    select c;

    or similar...


    Xpert360 blog | twitter : @xpert360 Opinions are my own. Please mark as answer if this helps solve your problem.

    • Marked as answer by Hoorah Thursday, April 19, 2012 1:56 PM
    Thursday, April 19, 2012 1:18 PM
  • This seems to work. I should have put all the id's in a list that support .contains() and hadn't thought of that.

    Thanks.

    Thursday, April 19, 2012 1:55 PM