Procedimientos almacenados con varios conjuntos de resultados

  • Artículo

A veces, al usar procedimientos almacenados, tendrá que devolver más de un conjunto de resultados. Este escenario se usa normalmente para reducir el número de recorridos de ida y vuelta de base de datos necesarios para componer una sola pantalla. Antes de EF5, Entity Framework permitiría llamar al procedimiento almacenado, pero solo devolvía el primer conjunto de resultados al código de llamada.

En este artículo se muestran dos maneras de acceder a más de un conjunto de resultados desde un procedimiento almacenado en Entity Framework. Uno que usa solo código y funciona con Code First y EF Designer y otro que solo funciona con EF Designer. La compatibilidad con herramientas y API en este escenario mejorará en versiones futuras de Entity Framework.

Modelo

En los ejemplos de este artículo se usa un modelo básico de blog y publicaciones en el que un blog tiene muchas publicaciones y una publicación pertenece a un único blog. Usaremos un procedimiento almacenado en la base de datos que devuelve todos los blogs y publicaciones, algo parecido a esto:

    CREATE PROCEDURE [dbo].[GetAllBlogsAndPosts]
    AS
        SELECT * FROM dbo.Blogs
        SELECT * FROM dbo.Posts

Acceso a varios conjuntos de resultados con código

Podemos ejecutar código para emitir un comando SQL sin formato para ejecutar nuestro procedimiento almacenado. La ventaja de este enfoque es que funciona con Code First y EF Designer.

Para conseguir que funcionen varios conjuntos de resultados tenemos que recurrir a la API ObjectContext usando la interfaz IObjectContextAdapter.

Una vez que tengamos un objeto ObjectContext, podemos usar el método Translate para convertir los resultados de nuestro procedimiento almacenado en entidades a las que se pueda realizar un seguimiento y que se puedan usar en EF como de costumbre. En el siguiente ejemplo de código se muestra este escenario en acción.

    using (var db = new BloggingContext())
    {
        // If using Code First we need to make sure the model is built before we open the connection
        // This isn't required for models created with the EF Designer
        db.Database.Initialize(force: false);

        // Create a SQL command to execute the sproc
        var cmd = db.Database.Connection.CreateCommand();
        cmd.CommandText = "[dbo].[GetAllBlogsAndPosts]";

        try
        {

            db.Database.Connection.Open();
            // Run the sproc
            var reader = cmd.ExecuteReader();

            // Read Blogs from the first result set
            var blogs = ((IObjectContextAdapter)db)
                .ObjectContext
                .Translate<Blog>(reader, "Blogs", MergeOption.AppendOnly);   


            foreach (var item in blogs)
            {
                Console.WriteLine(item.Name);
            }        

            // Move to second result set and read Posts
            reader.NextResult();
            var posts = ((IObjectContextAdapter)db)
                .ObjectContext
                .Translate<Post>(reader, "Posts", MergeOption.AppendOnly);


            foreach (var item in posts)
            {
                Console.WriteLine(item.Title);
            }
        }
        finally
        {
            db.Database.Connection.Close();
        }
    }

El método Translate acepta el lector que recibimos al ejecutar el procedimiento, un nombre EntitySet y un objeto MergeOption. El nombre de EntitySet será el mismo que el de la propiedad DbSet en el contexto derivado. La enumeración MergeOption controla cómo se gestionan los resultados si la misma entidad ya existe en la memoria.

Aquí se recorre en iteración la colección de blogs antes de llamar a NextResult; esto es importante dado el código anterior porque se debe consumir el primer conjunto de resultados antes de pasar al siguiente.

Una vez que se llama a los dos métodos de conversión, EF realiza el seguimiento de las entidades Blog y Publicación de la misma manera que cualquier otra entidad, por lo que se pueden modificar o eliminar y guardar como de costumbre.

Nota:

EF no tiene en cuenta ninguna asignación cuando crea entidades mediante el método Translate. Simplemente relaciona los nombres de columna del conjunto de resultados con los nombres de propiedad de las clases.

Nota:

Si tiene habilitada la carga diferida, al acceder a la propiedad posts en una de las entidades de blog, EF se conectará a la base de datos para cargar de forma diferida todas las publicaciones, aunque ya las hayamos cargado todas. Esto se debe a que EF no puede saber si ha cargado o no todas las publicaciones o si hay más en la base de datos. Si desea evitar esto, deberá deshabilitar la carga diferida.

Varios conjuntos de resultados configurados en EDMX

Nota:

Debe usar .NET Framework 4.5 para poder configurar varios conjuntos de resultados en EDMX. Si usa .NET 4.0, puede emplear el método basado en código que se muestra en la sección anterior.

Si usa EF Designer, también puede modificar el modelo para que conozca los distintos conjuntos de resultados que se devolverán. Algo que debe saber de antemano es que las herramientas no sean compatibles con varios conjuntos de resultados, por lo que deberá editar manualmente el archivo edmx. La edición de un archivo edmx como este funcionará, pero también interrumpirá la validación del modelo en VS. Por lo tanto, si valida el modelo, siempre recibirá errores.

  • Para ello, debe agregar el procedimiento almacenado al modelo como lo haría si fuera una consulta de un solo conjunto de resultados.

  • Una vez que lo haga, debe hacer clic con el botón derecho en el modelo y seleccionar Abrir con...Xml.

    Open As

Cuando tenga el modelo abierto como XML, deberá realizar los pasos siguientes:

  • Busque el tipo complejo y la importación de funciones en el modelo:
    <!-- CSDL content -->
    <edmx:ConceptualModels>

    ...

      <FunctionImport Name="GetAllBlogsAndPosts" ReturnType="Collection(BlogModel.GetAllBlogsAndPosts_Result)" />

    ...

      <ComplexType Name="GetAllBlogsAndPosts_Result">
        <Property Type="Int32" Name="BlogId" Nullable="false" />
        <Property Type="String" Name="Name" Nullable="false" MaxLength="255" />
        <Property Type="String" Name="Description" Nullable="true" />
      </ComplexType>

    ...

    </edmx:ConceptualModels>

 

  • Quite el tipo complejo.
  • Actualice la importación de funciones para que se asigne a las entidades, en nuestro caso tendrá un aspecto similar al siguiente:
    <FunctionImport Name="GetAllBlogsAndPosts">
      <ReturnType EntitySet="Blogs" Type="Collection(BlogModel.Blog)" />
      <ReturnType EntitySet="Posts" Type="Collection(BlogModel.Post)" />
    </FunctionImport>

Esto indica al modelo que el procedimiento almacenado devolverá dos colecciones, una de entradas de blog y otra de entradas de publicaciones.

  • Busque el elemento de asignación de funciones:
    <!-- C-S mapping content -->
    <edmx:Mappings>

    ...

      <FunctionImportMapping FunctionImportName="GetAllBlogsAndPosts" FunctionName="BlogModel.Store.GetAllBlogsAndPosts">
        <ResultMapping>
          <ComplexTypeMapping TypeName="BlogModel.GetAllBlogsAndPosts_Result">
            <ScalarProperty Name="BlogId" ColumnName="BlogId" />
            <ScalarProperty Name="Name" ColumnName="Name" />
            <ScalarProperty Name="Description" ColumnName="Description" />
          </ComplexTypeMapping>
        </ResultMapping>
      </FunctionImportMapping>

    ...

    </edmx:Mappings>
  • Reemplace la asignación de resultados por uno para cada entidad que se devuelve, como aquí:
    <ResultMapping>
      <EntityTypeMapping TypeName ="BlogModel.Blog">
        <ScalarProperty Name="BlogId" ColumnName="BlogId" />
        <ScalarProperty Name="Name" ColumnName="Name" />
        <ScalarProperty Name="Description" ColumnName="Description" />
      </EntityTypeMapping>
    </ResultMapping>
    <ResultMapping>
      <EntityTypeMapping TypeName="BlogModel.Post">
        <ScalarProperty Name="BlogId" ColumnName="BlogId" />
        <ScalarProperty Name="PostId" ColumnName="PostId"/>
        <ScalarProperty Name="Title" ColumnName="Title" />
        <ScalarProperty Name="Text" ColumnName="Text" />
      </EntityTypeMapping>
    </ResultMapping>

También es posible asignar los conjuntos de resultados a tipos complejos, como el creado de forma predeterminada. Para ello, cree un nuevo tipo complejo, en lugar de quitarlos, y use los tipos complejos en todas partes donde haya usado los nombres de entidad en los ejemplos anteriores.

Una vez que se han cambiado estas asignaciones, puede guardar el modelo y ejecutar el código siguiente para usar el procedimiento almacenado:

    using (var db = new BlogEntities())
    {
        var results = db.GetAllBlogsAndPosts();

        foreach (var result in results)
        {
            Console.WriteLine("Blog: " + result.Name);
        }

        var posts = results.GetNextResult<Post>();

        foreach (var result in posts)
        {
            Console.WriteLine("Post: " + result.Title);
        }

        Console.ReadLine();
    }

Nota:

Si edita manualmente el archivo edmx del modelo, se sobrescribirá si alguna vez vuelve a generar el modelo desde la base de datos.

Resumen

Aquí se muestran dos métodos diferentes de acceder a varios conjuntos de resultados mediante Entity Framework. Ambos son igualmente válidos en función de su situación y preferencias, y debe elegir el que parezca mejor dadas sus circunstancias. Está prevista la mejora de la compatibilidad con varios conjuntos de resultados en futuras versiones de Entity Framework, por lo que ya no será necesario realizar los pasos de este documento.