C# Linq-to-SQL Trying to recive multiple selects

Question

Hello!

I got this SQL code in a SP: (MS SQL 2008)

    DECLARE @type tinyint
    SELECT @type = Type FROM Contents WHERE ContentID = @ContentID    

    SELECT [ParentContentID], [Headline], [ShortDescription], [CategoryID], [Type], [State], [DatePublished], [Name] FROM Contents INNER JOIN Users ON Users.ID = Contents.PublishedBy WHERE ContentID = @ContentID

    IF (@type = 2) -- Content with text
    BEGIN
  SELECT [Preamble], [ContentText], [FaceBook], [Twitter], [PrintPage], [TipAFriend] FROM ContentText WHERE ContentID = @ContentID
    END

    SELECT [ID], [ImagePath], [ImageType] FROM ContentImages WHERE ContentID = @ContentID
    SELECT [ID], [BoxID] FROM ContentBoxes WHERE ContentID = @ContentID

I thought that i should be smart so i added a Linq-to-SQL class to my project and dragged the SP to the class. However, i can't seem to access the data from the second, third and forth select statement. I was hoping that the Linq-to-SQL class would produce 4 data tables with the information thus letting me access them like: data[2].Row[0].ImagePath.

Do i have to create my own code to get the code from the SQL-server in order to get this functionality?

Answer 1

I don't think linq-to-sql supports multiple result sets out of the box.

PLINQO, a suite of CodeSmith templates I'm using for my current project, handles this well: PLINQO: Stored Procedures with multiple result sets

Answer 2

LINQ to SQL does support multiple results sets from stored procedures. You'll want to look at the documentation for IMultipleResults, and you'll need to write some code in the data context partial (rather than just relying on what the designer generates). Some links to start you off:

• IMultipleResults Interface
• Linq to SQL Stored Procedures with Multiple Results
• Other IMultipleResults questions here on StackOverflow