Introduction

LINQ to SQL is great as an ORM tool. It provides ease of access to data in our data oriented applications. It is also very easy to learn. In this article, we will be discussing about SET based operations for database operations using LINQ to SQL.

Background

Like many other ORMs, it is not very good with data insertion, manipulation, and deletion. Though it does allow all of these operations, all of these operations are not efficient in terms of the performance standpoint of applications involving large data manipulations. These operations are submitted to the database as soon as we call the submitChanges() method of our DataContext object. These are submitted as individual INSERT, UPDATE, or DELETE statements for each record involved. You might notice these statements using SQL Profiler when the submitChanges() method is called.

Since the system does not support bulk insertion, manipulation, or deletion out of the box, we need to provide this functionality ourselves.

Discussion

We know that LINQ to SQL supports Stored Procedures as a first class citizen through DataContext. Like entities, we need to add the definition of the Stored Procedures to our DBMLs. The solution proposed here would use a Stored Procedure. This is not a generic solution, but would help in doing bulk operations with your database entities. Those who have been doing bulk database operations in .NET might have used similar solutions using datasets. This involves sending XML to the Stored Procedures and using OpenXML for applying SET based operations to our data.

To present the solution, let us create a table in the database. I am using SQL Server 2005. We are creating a table named TBL_TEST_TEST with two columns (ID and Name). Here, ID is an identity column which is the primary key of the table.

As discussed earlier, we are passing XML data to this Stored Procedure in the nText argument. We are using OpenXML to get data and insert into our table.

Now, we open the LINQ to SQL designer for adding this Stored Procedure to Test.dbml. Drag the Stored Procedure from Server Explorer to the tab of the designer set aside for Stored Procedures. Update the name of the Stored Procedure to some thing meaningful. We update the name to insertTestData.

We open Program.cs and write some code to generate data. Add the following code to the Main method of the Program class:

You can see that we have neither used db.insertOnSubmit() nor submitChanges(). But we have generated data in an array of TBL_TEST_TEST type objects provided by LINQ to SQL. After generating data in the array, we convert it to XML using XMLSerializer. We pass this XML directly to the Stored Procedure using the DataContext object.

Now finally, we are successful in inserting 50 rows of data in one shot to the database.

Bulk Updates

You might be more interested to learn about operations involving IQueryable types. Let us create this Stored Procedure in the database:

In the above code, we have queried the database using the IQueryable interface. After fetching data, we have updated it using a foreach loop. We have serialized it to XML data, and have written it to StringBuilderObject sBuilder. We have passed this data to the database using the Stored Procedure already added. This way, we achieve sending bulk data to our database for update.

Bulk Deletion

Now finally, we discuss about bulk deletion. This would be nearly the same solution. The difference is just that instead of updating, we will be deleting data. Let's attempt to delete all records from the database with the values of ID greater than 25.

when trying to use the transact-sql code in this article, i discovered that the sp_xml_preparedocument sproc has been removed from sql svr 2008r2. further research indicates that the sproc may have been deprecated as early as 2005, but i can not confirm that. i checked both 2008r2 installations and the sproc is no longer installed.

Thanks for the article. It's exactly what I am looking for. But one quick question. What change need to be made to the Insert stored procedure if I have to insert say, Address, phone# along with the NAME into the table. In other words, I want to insert more than one column using this approach.

"Like many other ORMs, it is not very good with data insertion, manipulation and deletion."
While I'll be the first to admit LINQ to SQL is not the best ORM (most people don't actually consider it a true ORM), this statement is just false. If your point is that ORMs are generally slower then straight SQL, datasets, or other CRUD methods, then my repsonse is "of course" because you sacrifice some performance for better manageability.

Your technique for manipulating bulk data is nice, but it is nothing more then a custom sproc that should be executed outside the LINQ context. What is the point of creating or querying all the objects just to serialize them and send them as XML through a sproc?
If you are truly doing bulk operations (thousands of records, not 50) you do not want to create all the objects (especially with change tracking enabled) to do something that could be scripted or done with dynamic SQL statements.

My apologies for sounding harsh, but I think people will have difficulty with this solution when they really dig into LINQ to SQL.

I too agree with this comment. The solution presented isn't in fact massive update with linq. Linq is just the way to call the massive update stored procedure. This wont scale easily for other entities without creating other storedprocedures (except if you create a sp with dynamic sql). Also, this isn't supported in all sql engines, as, for instance, sql compact doesn't support stored procedures.