title: Linq to SQL Stored Procedures with Multiple Results
body: <p>We have followed the approach below to get the data from multiple results using LINQ To SQL</p>
  
  <pre><code>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
  </code></pre>
  
  <p>The calling method in the class the inherits from DataContext should look like:</p>
  
  <pre><code>[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);
      }
  }
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>public interface IMultipleResults : IFunctionResult, IDisposable
  {
      IEnumerable&lt;TElement&gt; GetResult&lt;TElement&gt;();
  }
  </code></pre>
  
  <p>so the program can use this interface in order to retrieve the results:</p>
  
  <pre><code>BlogContext ctx = new BlogContext(...);
  
  IMultipleResults results = ctx.GetPostByID(...);
  
  IEnumerable&lt;Post&gt; posts = results.GetResult&lt;Post&gt;();
  
  IEnumerable&lt;Category&gt; categories = results.GetResult&lt;Category&gt;();
  </code></pre>
  
  <p>In the above stored procedures we had two select queries
  1. Select query without join
  2. Select query with Join</p>
  
  <p>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.</p>
  
  <ol>
  <li>Please if anybody can let me know how to achieve this using LINQ to SQL</li>
  <li>What is the performance trade-off if we use the above approach vis-à-vis implement the above approach with simple SQL </li>
  </ol>