none
problem when performing large number of inserts RRS feed

  • Question

  • I'm having major issues when using Linq to sql for my database migration tool. This tool essentially copies the data from an old schema to a new schema that is the basis of a new application. The tool is copying several thousands of records. The largest tables have about 30 to 40 thousand records in them.

    In my program I use the "InsertOnSubmit" record to insert records and then call the Context.SubmitRecords() method after every 100 records. This works fine for the smaller tables that only have a few thousand records, but when I start copying one of the larger tables, everything works fine until it gets to around 5000 inserts. Then I start getting SqlExceptions that complain about null values being passed into my mandatory table fields. The problem is that the entity object that I inserted when I call InsertOnSubmit is FULLY populated as it should be. I verified this by stepping through with the debugger. For some reason, when I call InsertOnSubmit, the queue is not being properly set with the query parameters. I have verified that this is not a one off error but catching the exception and allowing the program to continue. The error occurs in every subsequent record processed.

    Is there anyone out there with a thorough knowledge of the inner workings of the Linq to sql data context who can explain the problem I am having here. I am banging my head against the wall here. Previously I used NHibernate for this program which worked excellently but I wanted to leverage the code generation in visual studio so I moved it over to linqtosql. I am praying that I don't have to revert it.

    Thanks in advance 

    Saturday, April 18, 2009 3:56 PM

Answers

  • Problem solved. Turns out that linq doesn't like it when you assign an object to the related entities inside an entity object. E.g. If I have a one to many relationship between a "staff" and "department" table, when I populate a staff object for update, the department object inside staff (representing the relationship) should never be populated in any situation. Only the foreign key property should be populated. Unfortunately I don't understand LINQ enough to explain this.
    • Marked as answer by Vivl Sunday, April 19, 2009 7:56 AM
    Sunday, April 19, 2009 7:56 AM

All replies

  • Some extra info,

    I have been doing my updates in lots of 100 and I cannot insert more than 5900 rows. Linq to SQL is behaving very weirdly. In the log, 99 of the records are inserted correctly but the 100th is inserted with nulls. This happens over and over but none of the rows are inserted (I assume it's because SubmitChanges submits 1 transaction and if the transaction fails, all inserts fail). Previously when I tried calling SubmitChanges on every record, the max number of inserts I could do was 5000 and then every insert after that was trying to pass in nulls.

    Other things I have tried are calling connection.close and the connection.open after I call SubmitChanges (every 100 inserts), and creating a whole new datacontext instance after I call SubmitChanges. Neither made any difference and yielded the exact same symptom. Interestingly, the updates ran much faster when I was creating a new datacontext every 100 inserts.

    I hope someone out there has ideas....Tongue Tied 

    Saturday, April 18, 2009 3:57 PM
  • Try creating a new DataContext every 100 rows.  The DataContext is 'rembering' all the prior inserts and when you call SubmitChanges again it has to grovel through them to see if any of them changed.   It's probably not going to fix your null problem but will make the inserting go faster.
    Wayward LINQ Lacky
    Saturday, April 18, 2009 4:56 PM
    Moderator
  • Thanks for the reply Matt. You may have missed the part in my second post where I explained that I already tried creating a new context every 100 rows.

    "and creating a whole new datacontext instance after I call SubmitChanges. Neither made any difference and yielded the exact same symptom. Interestingly, the updates ran much faster when I was creating a new datacontext every 100 inserts."

    Any other ideas would be greatly appreciated.

    Thanks
    Saturday, April 18, 2009 5:01 PM
  • Problem solved. Turns out that linq doesn't like it when you assign an object to the related entities inside an entity object. E.g. If I have a one to many relationship between a "staff" and "department" table, when I populate a staff object for update, the department object inside staff (representing the relationship) should never be populated in any situation. Only the foreign key property should be populated. Unfortunately I don't understand LINQ enough to explain this.
    • Marked as answer by Vivl Sunday, April 19, 2009 7:56 AM
    Sunday, April 19, 2009 7:56 AM