Linq to SQL Stored Procedures with Multiple Results - IMultipleResults
Linq to SQL Stored Procedures with Multiple Results - IMultipleResults
Continuing my post series about Linq to SQL, this post talks about using stored procedures that return multiple result sets in Linq to SQL. If you missed any of my previous posts about Linq to SQL, here is a reminder:
Linq to SQL Attribute Based Mapping Linq to SQL Xml Based Mapping How To: Model Inheritance in Databases Linq to SQL Inheritance Linq to SQL Stored Procedures Sql Server supports returning more than a single result type from a stored procedure. This was very useful when we wanted to fill a large Dataset with multiple tables in a single access to the database. Similarly, this is also very useful when using Linq to SQL, and
So, having created the following stored procedure, based on the schema in the post Linq to SQL Stored Procedures.
CREATE PROCEDURE dbo.GetPostByID
(
@PostID int
)
AS
SELECT *
FROM Posts AS p
WHERE p.PostID = @PostID
SELECT c.*
FROM Categories AS c
JOIN PostCategories AS pc
ON (pc.CategoryID = c.CategoryID)
WHERE pc.PostID = @PostID
The calling method in the class the inherits from DataContext should look like:
[Database(Name = "Blog")]
public class BlogContext : DataContext
{
...
[Function(Name = "dbo.GetPostByID")]
[ResultType(typeof(Post))]
[ResultType(typeof(Category))]
public IMultipleResults GetPostByID(int postID)
{
IExecuteResult result =
this.ExecuteMethodCall(this,
((MethodInfo)(MethodInfo.GetCurrentMethod())),
postID);
return (IMultipleResults)(result.ReturnValue);
}
}
Notice that the method is decorated not only with the Function attribute that maps to the stored procedure name, but also with the ReturnType attributes with the types of the result sets that the stored procedure returns. Additionally, the method returns an untyped interface of IMultipleResults:
public interface IMultipleResults : IFunctionResult, IDisposable
{
IEnumerable<TElement> GetResult<TElement>();
}
so the program can use this interface in order to retrieve the results:
BlogContext ctx = new BlogContext(...);
IMultipleResults results = ctx.GetPostByID(...);
IEnumerable<Post> posts = results.GetResult<Post>();
IEnumerable<Category> categories = results.GetResult<Category>();
Enjoy!