Click here to Skip to main content
Click here to Skip to main content

LINQ to SQL: Update several rows

, 13 Mar 2011
Rate this:
Please Sign up or sign in to vote.
Updating several rows using LINQ to SQL.

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 want 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 straightforward with LINQ to SQL, like this:

//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();

I haven't included all the code above but you get the idea. We write easy LINQ queries and LINQ to SQL takes care of the SQL queries 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 IDs. The array is in the same order as the tabs is visually. Something like this:

{
   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 
}

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:

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);
}

I know from the beginning that the line selecting the items for me would generate a SQL query each time, this one:

var dbListItem = datacontext.Todo_Lists.Where(
    m => m.UserId == userID).Single(
    m => m.ListID == list);

However, I was hoping that LINQ to SQL would concatenate all the update statements into one SQL query. But when I did a little profiling on SQL Server, I found out that SubmitChanges() generated a 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:

Request #1: 0.1050060s
Request #2: 0.1180067s
Request #3: 0.1050060s

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 than one user. And then I haven't even taken into account the overhead of SQL queries to the production server that doesn't reside on the web server which 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 database call. On the LINQ to SQL datacontext, there is a method called ExecuteCommand() that we can use to execute a query straight into the database. Like this:

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(";");
    }
    DBTodo_ListsDataContext datacontext = new DBTodo_ListsDataContext();
    datacontext.ExecuteCommand(query.ToString(), new object[] { });
    return Json(new { sorted = true }, JsonRequestBehavior.AllowGet);
}

So what's so much better with this? The execution time talks for itself. The execute for three tests with this code came back to:

Request #1: 0.0500029s
Request #2: 0.0230013s
Request #3: 0.0310018s

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!

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Kristofer Kallsbo
CEO Kallsbo Consulting
Sweden Sweden
I develop in C# on .Net platforms like MVC. Like to use jQuery to build rich interfaces. I also blog about development and snags I got and the solutions I found for them.
 
I also a full time CIO at a Swedish energy company. When there is time I do some part time consulting on cloud issues.
Follow on   Twitter

Comments and Discussions

 
GeneralMy vote of 5 PinmemberclientSurfer13-Mar-11 8:40 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web02 | 2.8.140721.1 | Last Updated 13 Mar 2011
Article Copyright 2011 by Kristofer Kallsbo
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid