How to mass insert/update in linq to sql?

Question

Hi

How can I do these 2 scenarios.

Currently I am doing something like this

public class Repository
{
     private LinqtoSqlContext dbcontext = new LinqtoSqlContext();

   public void Update()
   {
   // find record
   // update record
   // save record ( dbcontext.submitChanges()
   }

   public void Insert()
   {
      // make a database table object ( ie ProductTable t = new ProductTable() { productname 
           ="something"}
      // insert record ( dbcontext.ProductTable.insertOnSubmit())
     // dbcontext.submitChanges();
   }
}

So now I am trying to load an XML file what has tons of records. First I validate the records one at a time. I then want to insert them into the database but instead of doing submitChanges() after each record I want to do a mass submit at the end.

So I have something like this

public class Repository
{
    private LinqtoSqlContext dbcontext = new LinqtoSqlContext();
   public void Update()
   {
   // find record
   // update record
   }

   public void Insert()
   {
      // make a database table object ( ie ProductTable t = new ProductTable() { productname 
           ="something"}
      // insert record ( dbcontext.ProductTable.insertOnSubmit())
   }

   public void SaveToDb()
   {
      dbcontext.submitChanges();
   }
}

Then in my service layer I would do like

for(int i = 0; i < 100; i++)
{
    validate();
    if(valid == true)
    {
       update();
       insert()
    }
}

SaveToDb();

So pretend my for loop is has a count for all the record found in the xml file. I first validate it. If valid then I have to update a table before I insert the record. I then insert the record.

After that I want to save everything in one go.

I am not sure if I can do a mass save when updating of if that has to be after every time or what.

But I thought it would work for sure for the insert one.

Nothing seems to crash and I am not sure how to check if the records are being added to the dbcontext.

Answer 1

The simple answer is: you do not. Linq2Sql is a lot of things - it is not a replacement for bulk upload / bulk copy. You will be a LOT more efficient using the ETL route:

• Generate a flat file (csv etc.) with the new data
• Load it into the database using bulk load mechanisms
• If the data is updating etc. - load it into temporary tables and use the MERGE command to merge it into the main table.

Linq2Sql will by design always suck in mass insert scenarios. ORM's just are not ETL tools.

Answer 2

Linq2SQL (as has been noted) does not handle this well by default, but luckily there are some solutions out there. here's one i used for a website when i wanted to do some bulk deletes. It worked well for me and due to its use of extension methods it was basically indistinguishable from regular Lin2SQL methods.

Answer 3

Even when you add multiple records to the DataContext before calling SubmitChanges, LINQ2SQL will loop through and insert them one by one. You can verify this by implementing one of the partial methods on an entity class ("InsertMyObject(MyObject instance)"). It will be called for each pending row individually.

I don't see anything wrong with your plan -- you say it works, but you just don't know how to verify it? Can't you simply look in the database to check if the records got added?

Another way to see what records are pending in the DataContext and have not yet been added is to call GetChangeSet() on the data context and then refer to the "Inserts" property of the returned object to get a list of rows that will be inserted when SubmitChanges is called.

Answer 4

I haven't really "released" this project yet, but it's a T4-based repository system that extends Linq To SQL and implements a bunch of batch operations (delete, update, create csv, etc.): http://code.google.com/p/grim-repo/. You can check out the source code and implement it however you see fit.

Also, this link has some great source code for batch operations: http://www.aneyfamily.com/terryandann/post/2008/04/Batch-Updates-and-Deletes-with-LINQ-to-SQL.aspx

And, also, I know it's tempting, but don't crap on the elderly. Try performing batch operations with DataAdapters/ADO.net: http://davidhayden.com/blog/dave/archive/2006/01/05/2665.aspx. It's faster, but inevitably hairier.

Finally, if you have an XML file, you can create a stored procedure that takes advantage of SQL server's built-in sproc, sp_xml_preparedocument. Check out how to use it here: http://msdn.microsoft.com/en-us/library/ms187367.aspx