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
testContext.SubmitChanges()
End Using
Console.ReadKey()
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})
Next
users.Add(user)
Next
' Attach the users to the data context. This will also attach the articles
' because they have been added to each user's Articles collection.
context.Users.AttachAll(users)
Return users
End Function
End Module