LINQ to SQL - Mapping Stored Procedure with Multiple Results

SQL Part 

1. Create tables for getting result set.

CREATE TABLE [dbo].[Color](

    [ID] [int] IDENTITY(1,1) NOT NULL,

    [Color] [varchar](50) NULL

) ON [PRIMARY]



CREATE TABLE [dbo].[Car](

    [ID] [int] IDENTITY(1,1) NOT NULL,

    [Make] [varchar](50) NULL,

    [Model] [varchar](50) NULL,

    [NumOfDoors] [tinyint] NULL,

    [ModelYear] [smallint] NULL,

    [ColorId] [int] NULL

) ON [PRIMARY]

2. Insert data into tables.

Color Table

ID    Color
1    Red
2    White
3    Blue
4    Green

Car Table

ID Make Model NumOfDoors ModelYear ColorId 1 Ford Mustang 2 1964 1 2 ford F150 2 2010 1 3 Toyota Camry 4 2011 1 4 Ford Taurus 5 1995 2 5 ford F250 2 2010 3 6 Chevrolet Volt 4 2010 1 7 Ford Focus 4 2012 4 8 Chevrolet Aveo 4 2011 2 9 Chevrolet Camaro 4 1978 4 10 Honda Civic 4 2012 1 11 Chevrolet Cruse 4 2012 1 12 Toyota Rav4 5 2000 1

3. Create Stored Procedure to return multiple result.

CREATE PROCEDURE [dbo].[GetBoth] 

    @colorId int = 0    

AS

BEGIN

    SET NOCOUNT ON;

    SELECT * from Car where ColorId=@colorId

    select c.Color,ca.* from Color c join Car ca on ca.ColorId=c.ID where c.ID=@colorId

END

above SP return two result set, one is car table and second join from car and color table.

.NET Part

1. Create DMBL file and drag car & color table into file, also drag SP into dbml file.

Suppose my DBML file name is Test.dbml

it will create class named TestDataContext including datacontract for car & color tables.

by default when we drag SP into DBML file it will create method with ISingleResult interface to get result from SP.

2. First create Data Contract for join tables result.

public class Mix

    {

        public string Color { get; set; }

        public int ID { get; set; }

        public string Make { get; set; }

        public string Model { get; set; }

        public Nullable<byte> NumOfDoors { get; set; }

        public Nullable<short> ModelYear { get; set; }

        public Nullable<int> ColorId { get; set; }    

    }

3. Now we need to create method that produce result of multiple query, DBML doesn't provide such type of method internally.

we have to create method to get multiple result set.

public partial class TestDataContext

    {

        [Function(Name = "dbo.GetBoth")]

        [ResultType(typeof(Car))]

        [ResultType(typeof(Mix))]

        public IMultipleResults GetBothMix([Parameter(Name = "colorId", DbType = "Int")] System.Nullable<int> colorId)

        {

            IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), colorId);

            //decimal res = ((decimal)(result.GetParameterValue(1)));

            return (IMultipleResults)result.ReturnValue;

        }

    }

In above snippet, i have create method that return multiple result set using IMultipleResults Interface.

I have used System.Data.Linq.Mapping namespace to map stored procedure,

like [Function(Name = "dbo.GetBoth")] - maps Stored Procedure named GetBoth.

[ResultType(typeof(Car))] - maps car table to first result set in stored procedure.

[ResultType(typeof(Mix))] - maps user defined generic type to second result set in stored procedure.
       


4. Now in CS file get result using DataContext object.

protected void Page_Load(object sender, EventArgs e)

        {

            TestDataContext context = new TestDataContext();

            context.ObjectTrackingEnabled = false;           

            IMultipleResults d = context.GetBothMix(1);

            IList<Car> cars = d.GetResult<Car>().ToList();

            IList<Mix> colors = d.GetResult<Mix>().ToList();

         }