none
Linq to Sql Insert/Update RRS feed

  • Question

  • Hi all,

    What Im trying to do seems very simple but I cant get around it using linq to sql. Im importing data from access, excel and flat files to an sql database. I have an entity say Address. I use a long loop to go through rows from my source. I want to check if Address exists and if not create a new one and save it. Address has ID (Primary Key Auto Generated), and No (Unique indexed) among other fields.

    Trouble comes here: In my loop we may have several rows with the same No. I look for Address with same No and if it doesnt exist, it should be created. It it exists it should be updated with current row values (when user imports same file but has changed other fields). I allways get 'cannot insert duplicate' every time I try this. I have used other ORMs which look for objects created even in memory but not saved yet and they do this very effectively. I now want to use linq to sql but it doesnt seem to work for me. I also want to write minimum code for this because it involves alot of objects in the loop. How can I achieve this in linq to sql.

    Help me please before I give up!!

    Thanks and regards

    bughata.
    • Edited by bughata Thursday, July 30, 2009 12:54 PM
    Thursday, July 30, 2009 7:48 AM

All replies

  • Not sure if it fulfills all your requirements, but here is something that should work:

                var context = new DataClassesDataContext();
                var query = from row in context.Persons
                            where row.Id == 6
                            select row;
    
                if(query.Count() == 0)
                {
                    var person = new Person
                                     {
                                         Name = "foo"
                                     };
                    context.Persons.InsertOnSubmit(person);
                }
                else
                {
                    var person = query.First();
                    person.Name = "Foo";
                    
                }
                context.SubmitChanges();
    

    • Marked as answer by bughata Friday, July 31, 2009 7:45 AM
    • Unmarked as answer by bughata Friday, July 31, 2009 7:57 AM
    Thursday, July 30, 2009 2:05 PM
  • Thank you very much Thomas. Your Answer works. Now, In my circumstance, things are a bit more complicated:

    As I explained, I need to call this code in a loop (in a background thread) with a progress UI. I can see here we need to call context.SubmitChanges after every object is created/updated. What if the user decides to cancel the operation? Some objects will have been saved. I think I may have to use transactions but Im not sure (I am very new to linq - 1st project). What happens then, because calling 'Save()' at the end will mean the query wont work, or will it?

    Here's pseudocode for this scenario:
    while (reader.Read())
    {
    //Check if row already exists, if not create new, if so, update existing
    var context = new DataClassesDataContext();
                var query = from row in context.Persons
                            where row.Id == 6
                            select row;
    
                if(query.Count() == 0)
                {
                    var person = new Person
                                     {
                                         Name = "foo"
                                     };
                    context.Persons.InsertOnSubmit(person);
                }
                else
                {
                    var person = query.First();
                    person.Name = "Foo";
                    
                }
     //I may need to rollback entire process therefore this would be inappropriate here
                context.SubmitChanges();
    
    }
    Note:

    1. Multiple rows may have the same Id so when reader is in 2nd or Nth row (with same id) it should update not insert
    2. Loop may be canceled at any time so calling SubmitChanges() may not be appropriate after inserting a single entity.

    I am trying to use Attach (to find object in memory) but don't know if this is correct.


    Please guide me. Thanks for your time guys.

    Regards

    bughata.
    • Edited by bughata Friday, July 31, 2009 9:43 AM Updated explanation
    Friday, July 31, 2009 8:36 AM
  • I would assume you can just move the declaration of context, and the submitchanges out of the loop.

    The declaration before the loop, and the commit after.
    Friday, July 31, 2009 11:04 AM
  • Indeed that's possible but the query will have 0 objects in the second iteration since the object has been added only 'in memory'. I need to check if an object exists both in memory or database. That's whats difficult for me. Any suggestions?

    Regards

    bughata.
    Saturday, August 1, 2009 9:30 AM