Stored procedure con più set di risultati

  • Articolo

In alcuni casi, quando si usano stored procedure, sarà necessario restituire più set di risultati. Questo scenario viene comunemente usato per ridurre il numero di round trip del database necessari per comporre una singola schermata. Prima di EF5, Entity Framework consente di chiamare la stored procedure, ma restituisce solo il primo set di risultati al codice chiamante.

Questo articolo illustra due modi per accedere a più set di risultati da una stored procedure in Entity Framework. Uno che usa solo il codice e funziona sia con Code first che con Ef Designer e uno che funziona solo con Entity Framework Designer. Gli strumenti e il supporto api per questo dovrebbero migliorare nelle versioni future di Entity Framework.

Modello

Gli esempi in questo articolo usano un modello di blog e post di base in cui un blog include molti post e un post appartiene a un singolo blog. Verrà usata una stored procedure nel database che restituisce tutti i blog e i post, come illustrato di seguito:

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

Accesso a più set di risultati con codice

È possibile eseguire il codice per eseguire un comando SQL non elaborato per eseguire la stored procedure. Il vantaggio di questo approccio è che funziona sia con Code first che con Entity Framework Designer.

Per ottenere più set di risultati funzionanti, è necessario passare all'API ObjectContext usando l'interfaccia IObjectContextAdapter.

Dopo aver creato objectContext, è possibile usare il metodo Translate per convertire i risultati della stored procedure in entità che possono essere rilevate e usate in Ef come di consueto. L'esempio di codice seguente illustra questa operazione in azione.

    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();
        }
    }

Il metodo Translate accetta il lettore ricevuto durante l'esecuzione della procedura, un nome EntitySet e un oggetto MergeOption. Il nome di EntitySet corrisponderà alla proprietà DbSet nel contesto derivato. L'enumerazione MergeOption controlla la modalità di gestione dei risultati se la stessa entità esiste già in memoria.

In questo caso si scorre la raccolta di blog prima di chiamare NextResult, questo è importante dato il codice precedente perché il primo set di risultati deve essere utilizzato prima di passare al set di risultati successivo.

Dopo aver chiamato i due metodi di conversione, le entità Blog e Post vengono rilevate da EF allo stesso modo di qualsiasi altra entità e quindi possono essere modificate o eliminate e salvate come di consueto.

Nota

Ef non tiene conto di alcun mapping quando crea entità usando il metodo Translate. Corrisponderà semplicemente ai nomi delle colonne nel set di risultati con i nomi delle proprietà nelle classi.

Nota

Se è stato abilitato il caricamento differita, l'accesso alla proprietà post in una delle entità del blog verrà quindi connesso al database per caricare in modo differita tutti i post, anche se sono già stati caricati tutti. Ciò è dovuto al fatto che EF non è in grado di sapere se tutti i post sono stati caricati o se sono presenti altri post nel database. Se si vuole evitare questo problema, sarà necessario disabilitare il caricamento differita.

Più set di risultati con configurato in EDMX

Nota

È necessario impostare come destinazione .NET Framework 4.5 per poter configurare più set di risultati in EDMX. Se la destinazione è .NET 4.0, è possibile usare il metodo basato sul codice illustrato nella sezione precedente.

Se si usa Entity Framework Designer, è anche possibile modificare il modello in modo che conosca i diversi set di risultati che verranno restituiti. Una cosa da sapere prima della mano è che gli strumenti non sono più consapevoli del set di risultati, quindi sarà necessario modificare manualmente il file edmx. La modifica del file edmx come questa funzionerà, ma interromperà anche la convalida del modello in Visual Studio. Pertanto, se si convalida il modello, si otterranno sempre errori.

  • A tale scopo, è necessario aggiungere la stored procedure al modello come si farebbe per una singola query del set di risultati.

  • Dopo aver ottenuto questa operazione, è necessario fare clic con il pulsante destro del mouse sul modello e selezionare Apri con.. quindi Xml.

    Open As

Dopo aver aperto il modello come XML, è necessario eseguire la procedura seguente:

  • Trovare il tipo complesso e l'importazione di funzioni nel modello:
    <!-- 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>

 

  • Rimuovere il tipo complesso
  • Aggiornare l'importazione della funzione in modo che eseprima il mapping alle entità, nel nostro caso avrà un aspetto simile al seguente:
    <FunctionImport Name="GetAllBlogsAndPosts">
      <ReturnType EntitySet="Blogs" Type="Collection(BlogModel.Blog)" />
      <ReturnType EntitySet="Posts" Type="Collection(BlogModel.Post)" />
    </FunctionImport>

Questo indica al modello che la stored procedure restituirà due raccolte, una delle voci di blog e una delle voci di post.

  • Trovare l'elemento di mapping delle funzioni:
    <!-- 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>
  • Sostituire il mapping dei risultati con uno per ogni entità restituita, ad esempio quanto segue:
    <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>

È anche possibile eseguire il mapping dei set di risultati a tipi complessi, ad esempio quello creato per impostazione predefinita. A tale scopo, creare un nuovo tipo complesso, anziché rimuoverli e usare i tipi complessi ovunque fossero stati usati i nomi delle entità negli esempi precedenti.

Dopo aver modificato questi mapping, è possibile salvare il modello ed eseguire il codice seguente per usare la stored procedure:

    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

Se si modifica manualmente il file edmx per il modello, questo verrà sovrascritto se il modello viene rigenerato dal database.

Riepilogo

Qui sono stati illustrati due diversi metodi per accedere a più set di risultati usando Entity Framework. Entrambi sono ugualmente validi a seconda della vostra situazione e preferenze e si dovrebbe scegliere quello che sembra migliore per le vostre circostanze. È previsto che il supporto per più set di risultati verrà migliorato nelle versioni future di Entity Framework e che l'esecuzione dei passaggi in questo documento non sarà più necessaria.