LINQ to SQL: Update several rows
So I was coding away on an MVC project with integrated jQuery. I use jQuery UI Tabs in my page and let the user add new, delete and re-order them. All this I wan’t to save to the database so I can display it the next time the user logs on. I’m using JsonResult to handle the Ajax calls from jQuery on the server side. Add and remove is pretty straight forward with LINQ to SQL like this:
[csharp]//Add
DBTodo_ListsDataContext datacontext = new DBTodo_ListsDataContext();
Todo_List newList = new Todo_List();
newList.ListName = listName;
newList.ListDesc = listDesc;
newList.UserId = userID;
datacontext.Todo_Lists.InsertOnSubmit(newList);
datacontext.SubmitChanges();
//Remove : or really mark as trashed
DBTodo_ListsDataContext datacontext = new DBTodo_ListsDataContext();
Todo_List trashList = datacontext.Todo_Lists.Where(m => m.UserId == userID).Single(m => m.ListID == listID);
trashList.ListTrash = true;
datacontext.SubmitChanges();[/csharp]
I haven’t included all the code above but you get the idea.
We write easy Linq querys and LINQ to SQL takes care of the SQL querys for us. So when I set out to build the sort order update function I thought that Linq was going to do a better job. On the tab re-order I send an Ajax request with an int array with all the tab id’s. The array is in the same order as the tabs is visually. Something like this:
[js]{ 10, 14, 11, 16, 17, 18, 8, 19, 21, 20, 22, 27, 23, 24, 28, 29, 26, 30, 31, 32, 33, 34, 35, 37, 36, 38, 39, 40, 41, 42, 43, 44, 45, 47, 46, 50, 51, 53, 54, 55, 63, 60, 1 }[/js]
So now I have to use that array to reset all the sort order fields for the lists in the array above. First I went like this:
[csharp]public JsonResult UpdateSortOrder(List<int> listsSortOrder) {
int sortOrder = 0;
Guid userID = (Guid)Membership.GetUser().ProviderUserKey;
DBTodo_ListsDataContext datacontext = new DBTodo_ListsDataContext();
// Loop throug the sort order
foreach (int list in listsSortOrder) {
var dbListItem = datacontext.Todo_Lists.Where(m => m.UserId == userID).Single(m => m.ListID == list);
dbListItem.ListSortOrder = sortOrder;
// Count up the sort order
sortOrder++;
}
// Commit changes
datacontext.SubmitChanges();
return Json(new { sorted = true }, JsonRequestBehavior.AllowGet);
}[/csharp]
I know from the beginning that the line selecting the items for me would generate an SQL query each time, this one:
[csharp]var dbListItem = datacontext.Todo_Lists.Where(m => m.UserId == userID).Single(m => m.ListID == list);[/csharp]
How ever I was hoping that LINQ to SQL would concat all the update statements into one SQL query. But when I did a little profiling on the SQL server I found out that the SubmitChanges() generated one SQL connection and query for each updated item, in this case 43 of them. So I checked the execution time on the web server for this code, i tried three times and got the result below:
[ps]Request #1: 0.1050060s
Request #2: 0.1180067s
Request #3: 0.1050060s[/ps]
Not that this is long but you have to take into account that this is on my dev machine. I’m the only user and I hope my finished project will have more then one user. And then I haven’t even taken into account the overhead of SQL querys to the production server that doesn’t reside on the web server witch my SQL does on my dev machine. So what is a better approach? Build the command in one and the same query string and do one DB call. On the LINQ to SQL datacontext there is a method called ExecuteCommand() that we can use to execute a query straight into the DB. Like this:
[csharp]public JsonResult UpdateSortOrder(List<int> listsSortOrder) {
int sortOrder = 0;
System.Text.StringBuilder query = new System.Text.StringBuilder();
string userID = Membership.GetUser().ProviderUserKey.ToString();
foreach (int list in listsSortOrder) {
query.Append("UPDATE ToDo_Lists SET ListSortOrder = ");
query.Append(sortOrder);
query.Append(" WHERE UserID = ‘");
query.Append(userID);
query.Append("’ AND ListID = ");
query.Append(list);query.Append(";");
sortOrder++;
}
DBTodo_ListsDataContext datacontext = new DBTodo_ListsDataContext();
datacontext.ExecuteCommand(query.ToString(), new object[] { });
return Json(new { sorted = true }, JsonRequestBehavior.AllowGet);
}[/csharp]
So what’s so much better with this? The execution times talks for it self. The execute for three test with this code came back to:
[ps]Request #1: 0.0500029s
Request #2: 0.0230013s
Request #3: 0.0310018s[/ps]
So my conclusion is that sometimes it’s a great idea to go around LINQ to SQL for performance. So sometimes you have to go old-school!
Hackviking aka Kristofer Källsbo 