Graeme Hill's Dev Blog

Saving changes to stored procedure results in LINQ to SQL

Star date: 2010.040

The great thing about fetching data via a LINQ to SQL query is that you get a nice formatted result and you can easily save back any changes you make with SubmitChanges(). Unfortunately, we all inevitably fall into scenarios where we have to make use of stored procedures for performance or other reasons. If you have a stored procedure whose result set contains columns from just a single table then you can easily map the stored procedure to that table, but in most cases the result set involves multiple tables making things a little more tricky. It's easy to execute a stored procedure from LINQ to SQL (just drag the SP from the server explorer into the designer and then execute it like a function on the data context) but you lose some of the benefits of LINQ to SQL. First of all, you just get a flat result set instead of a hierarchical result set using the auto generated entity classes. Second, you can't just make changes to the result and call SubmitChanges. Luckily, with a little extra work, the flat, detached result set can be converted into a hierarchical, attached result set where changes can easily be saved.

If you don't want to bother reading the whole article and all of the code, here's the short answer: use the Attach() method.

Below is an example that runs a stored procedure to return all users in the database joined with their articles. The results are converted into an attached list of users, each containing a collection of articles. Notice that not all of the columns need to be known, just the primary key and timestamp are required. For more info on the timestamp, check out this article.

Module Module1

    Sub Main()

        Using testContext As New TestDataContext

            ' Print SQL queries to the console for testing purposes
            testContext.Log = Console.Out

            ' Get attached entities
            Dim users = GetAttachedUsersWithGroups(testContext)

            ' Make some random changes to prove the concept
            users.First.userName = "foo"
            users.First.Articles.First.text = "bar"

            ' Submit the changes to see what SQL gets executed

        End Using


    End Sub

    Public Function GetAttachedUsersWithGroups(
        ByVal context As TestDataContext) As IEnumerable(Of User)

        ' Get some data from a stored procedure
        Dim result = context.GetAllUsersWithArticles

        ' Convert flat result set to groups of articles by user
        Dim userGroups = From row In result _
                         Group row By row.userID, row.userTimestamp _
                         Into articles = Group _
                         Select userID, userTimestamp, articles

        Dim users As New List(Of User)

        ' Create LINQ to SQL entities
        For Each userGroup In userGroups
            Dim user As New User With {.userID = userGroup.userID, _
                                       .ts = userGroup.userTimestamp}
            For Each article In userGroup.articles
                user.Articles.Add(New Article With {
                    .articleID = article.articleID, _
                    .title = article.title, _
                    .ts = article.articleTimestamp})

        ' Attach the users to the data context.  This will also attach the articles
        ' because they have been added to each user's Articles collection.

        Return users

    End Function

End Module