LINQ To SQL – Executando Stored Procedures com vários Result Sets

Recentemente me deparei com um problema nos fóruns do MSDN relacionado com stored procedures mapeadas com o LINQ To SQL. Após uma pesquisa em alguns blogs na internet encontrei uma solução e gostaria de compartilhar com vocês.

É comum existirem stored procedures que retornam mais de um result set tal como o exemplo abaixo.

CREATE PROCEDURE [dbo].[ListCustomersAndEmployees]
AS
BEGIN

    SET NOCOUNT ON;

    SELECT * FROM dbo.Customers
    SELECT * FROM dbo.Employees    

END

Obs: Estou considerando que todos conhecem o banco de dados Northwind da Microsoft. Para fazer o download do mesmo veja no final do post.

O LINQ To SQL realiza o mapeamento da stored procedure da seguinte maneira.

[Function(Name="dbo.ListCustomersAndEmployees")]
public ISingleResult<ListCustomersAndEmployeesResult> ListCustomersAndEmployees()
{
    IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())));
    return ((ISingleResult<ListCustomersAndEmployeesResult>)(result.ReturnValue));
}

O problema é que no código acima só conseguimos adquirir os campos retornados pela primeira consulta, ou seja, os campos da tabela Customers.

Para resolver esse problema podemos utilizar o mecanismo de partial classes para criar um novo método que irá mapear a stored procedure. Esse mecanismo nos permite dividir a declaração de uma classe em múltiplos arquivos e isso é interessante, já que não é recomendado alterar o código gerado automaticamente pelo Visual Studio.

A idéia então é criar um novo arquivo que adiciona um novo método ao contexto. No exemplo abaixo o meu contexto possui o nome NorthwindDataContext.

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

Assim para utilizarmos o método acima e executarmos a procedure o código seria assim:

// contexto
NorthwindDataContext dataContext = new NorthwindDataContext();

// usando a interface IMultipleResults
IMultipleResults results = dataContext.ListMultipleCustomersAndEmployees();

// result set Customers
IEnumerable<Customer> customers = results.GetResult<Customer>();

// result set Employees
IEnumerable<Employee> employees = results.GetResult<Employee>();

...

O Visual Studio provavelmente não consegue fazer esse mapeamento corretamente porque podem existir stored procedures que dependendo de uma condição retornam entidades diferentes. A própria definição da interface IMultipleResults diz o seguinte:

Represents the results of mapped functions or queries with variable return sequences.

Espero que isso ajude vocês.

Links:

Northwind and pubs Sample Databases for SQL Server 2000
http://bit.ly/cWQNiL

IMultipleResults Interface
http://bit.ly/cFzS27

Stored Procedures (LINQ To SQL)
http://bit.ly/9wnZZg