Linq to SQL Stored Procedures with Multiple Results

Question

We have followed the approach below to get the data from multiple results using LINQ To SQL

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

In the above stored procedures we had two select queries 1. Select query without join 2. Select query with Join

But in the above second select query the data which is displayed is from one of the table i.e. from Categories table. But we have used join and want to display the data table with the results from both the tables i.e. from Categories as well as PostCategories.

1. Please if anybody can let me know how to achieve this using LINQ to SQL
2. What is the performance trade-off if we use the above approach vis-à-vis implement the above approach with simple SQL

Answer 1

Scott Guthrie (the guy who runs the .Net dev teams at MS) covered how to do this on his blog some months ago much better than I ever could, link here. On that page there is a section titled "Handling Multiple Result Shapes from SPROCs". That explains how to handle multiple results from stored procs of different shapes (or the same shape).

I highly recommend subscribing to his RSS feed. He is pretty much THE authoritative source on all things .Net.