There always be case where one procedure returns more then one result set. Getting those data in DataSet is lazy way of coding, best way to do that is using DataReader. DataReader having method call NextResult which allows us to read next result set if any.

First we will see how we were doing this in ADO.NET using DataReader and then we will see how we can achieve this using LINQ to SQL.

SqlCommand cmd = new SqlCommand(); //Your command object
IDataReader idr = cmd.ExecuteReader();

if(idr.NextResult())
{
    while (idr.Read())
    {
        //Read the data reader
    }
}

Now let see how we can read multiple result set using LINQ. In general scenario when you drag and drop procedure in dbml its always returns ISingleResult and only consider first result, based of first result it will create data contracts.

Lets create procedure retuning multiple results.

CREATE PROCEDURE GetCustAndEmp
AS
BEGIN
	SELECT TOP 10 * FROM Customers
	SELECT TOP 10 * FROM Employees
END

When you drag and drop this procedure you can see function in designer.cs file which look like following

You can see it generates ISignleResult result and having only single entity which is GetCustAndEmpResult, and when we see the definition of GetCustAndEmpResult class we can see that it only consider customer results and exclude employee result.

To get  multiple results we have to modify methods and have to add some more decorations. First will remove GetCustAndEmp procedure from dbml by just deleting that form method panel as we are going to create that method manually. The method should be in same namespace and with same class, If we put it in designer.cs then each time we refresh the model we have to write our modified method again. We can use Partial Class to avoid this condition, partial class will be across file but same scope. You can read more here on Partial Class.

So will create new file having same class name and added partial keyword, this you can do by pressing F7 on our dbml.

We will now place our method with some modification to work with multiple results.

partial class NorthwindModelDataContext
{
    [FunctionAttribute(Name = "dbo.GetCustAndEmp")]
    [ResultType(typeof(Employee))]
    [ResultType(typeof(Customer))]
    public IMultipleResults GetCustAndEmp()
    {
        IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())));
        return (IMultipleResults)(result.ReturnValue);
    }
}

Few modification to this, we have added two decorations to specify type of result that method is expecting. We have added Employee and Customer as ResultType as our procedure is returning these two result sets, this will going to use by MethodInfo in order to return IMultipleResults. In addition to this we have to change return type, it was before ISingleResult now we have to make it IMultipleResults. Now our method is ready to return multiple results. Let’s see how we can get the results form IMultipleResults.

using (NorthwindModelDataContext context = new NorthwindModelDataContext())
{
    var result = context.GetCustAndEmp();
    List<Customer> customers = result.GetResult<Customer>().ToList();
    List<Employee> employees = result.GetResult<Employee>().ToList();

    Console.WriteLine("Customers : {0}, Employees : {1}", customers.Count, employees.Count);
}

And here is the output

If you see Class which are in ResultType you can find there are few decorations on property and some events are defined and lots of code. In our case we are having already exists class which is generated by dbml. What if you want to return some transfer object? Yes you have to create new class with those decoration and events!! Opps not good correct?

We have alternative which is nice and we don’t have to bother about all these decorations and properties. Well this is hack not proper solutions. As we know dbml is generating class for first result found in procedure, so we can generate dbml by keeping our required result first until we have all classes with us. We have to keep these classes separate so when you remove method each time it will not get destroy. At the end this process you will have bunch of new classes which you have to add in newly created file. I said file file not class, that means it should reside out of partial class not inside. We will see that quickly

Our changed procedure

CREATE PROCEDURE GetCustAndEmp
AS
BEGIN
	SELECT TOP 10 ContactName, CompanyName FROM Customers
	SELECT TOP 10 FirstName, LastName, Title FROM Employees
END

You can see now it’s returning transfer object not whole table. When we refresh dbml it will add our procedure it will add class having ContactName and CompanyName, will copy that class change name and add into class separate.

Now we will change the sequence of our SQL statements in procedure

CREATE PROCEDURE GetCustAndEmp
AS
BEGIN
	SELECT TOP 10 FirstName, LastName, Title FROM Employees
	SELECT TOP 10 ContactName, CompanyName FROM Customers
END

Will do same process to generate our own class.

Now we are all set to write our method which returns IMultipleResults. Let see the change on GetCustAndEmp method.

[FunctionAttribute(Name = "dbo.GetCustAndEmp")]
[ResultType(typeof(EmployeeDTO))]
[ResultType(typeof(CompanyDTO))]
public IMultipleResults GetCustAndEmp()
{
    IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())));
    return (IMultipleResults)(result.ReturnValue);
}

Calling of this will be same as older one.

using (NorthwindModelDataContext context = new NorthwindModelDataContext())
{
    var result = context.GetCustAndEmp();
    List<CompanyDTO> customers = result.GetResult<CompanyDTO>().ToList();
    List<EmployeeDTO> employees = result.GetResult<EmployeeDTO>().ToList();

    Console.WriteLine("Customers : {0}, Employees : {1}", customers.Count, employees.Count);
}