Хранимые процедуры с несколькими результирующих наборами

Иногда при использовании хранимых процедур необходимо вернуть несколько результирующих наборов. Этот сценарий обычно используется для уменьшения количества обходов базы данных, необходимых для создания одного экрана. До EF5 Entity Framework разрешает вызывать хранимую процедуру, но возвращает только первый результирующий набор вызывающему коду.

В этой статье показано два способа, которые можно использовать для доступа к нескольким результирующих наборам из хранимой процедуры в Entity Framework. Тот, который использует только код и работает как с кодом, так и с конструктором EF, и только с конструктором EF. Поддержка инструментов и API для этого должна улучшиться в будущих версиях Entity Framework.

Модель

В примерах этой статьи используется базовая модель блога и записей, в которой блог содержит много записей, а запись принадлежит одному блогу. Мы будем использовать хранимую процедуру в базе данных, которая возвращает все блоги и записи, примерно так:

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

Доступ к нескольким результирующих наборам с помощью кода

Мы можем выполнить код для выдачи необработанной команды SQL для выполнения хранимой процедуры. Преимущество этого подхода заключается в том, что он работает как с кодом, так и с конструктором EF.

Чтобы получить несколько результирующих наборов, необходимо удалить в API ObjectContext с помощью интерфейса IObjectContextAdapter.

После получения объекта ObjectContext мы можем использовать метод Translate для перевода результатов хранимой процедуры в сущности, которые можно отслеживать и использовать в EF как обычно. В следующем примере кода показано это в действии.

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

Метод Translate принимает средство чтения, полученное при выполнении процедуры, имени EntitySet и MergeOption. Имя EntitySet будет совпадать со свойством DbSet в производном контексте. Перечисление MergeOption определяет, как обрабатываются результаты, если та же сущность уже существует в памяти.

Здесь мы итерируем коллекцию блогов перед вызовом NextResult, это важно, учитывая приведенный выше код, так как первый результирующий набор необходимо использовать перед переходом к следующему результирующем набору.

После вызова двух методов перевода сущности Blog и Post отслеживаются EF так же, как и любая другая сущность, поэтому ее можно изменить или удалить и сохранить как обычное.

Несколько результирующих наборов с настроенным в EDMX

Если вы используете конструктор EF, вы также можете изменить модель, чтобы она знала о различных результирующих наборах, которые будут возвращены. Перед рукой следует знать, что инструмент не является несколькими результирующих наборов, поэтому вам потребуется вручную изменить edmx-файл. Изменение edmx-файла, подобного этому, будет работать, но также приведет к прерыванию проверки модели в VS. Поэтому при проверке модели всегда будут возникать ошибки.

• Чтобы сделать это, необходимо добавить хранимую процедуру в модель, как и для одного запроса результирующих наборов.

• После этого необходимо щелкнуть модель правой кнопкой мыши и выбрать "Открыть с.". Затем xml

  

После открытия модели в формате XML необходимо выполнить следующие действия:

• Найдите сложный тип и импорт функций в модели:

    <!-- 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>

• Удаление сложного типа
• Обновите импорт функции таким образом, чтобы оно сопоставлялось с сущностями, в нашем случае это будет выглядеть следующим образом:

    <FunctionImport Name="GetAllBlogsAndPosts">
      <ReturnType EntitySet="Blogs" Type="Collection(BlogModel.Blog)" />
      <ReturnType EntitySet="Posts" Type="Collection(BlogModel.Post)" />
    </FunctionImport>

Это указывает модели, что хранимая процедура вернет две коллекции, одну из записей блога и одну из записей записи.

• Найдите элемент сопоставления функций:

    <!-- 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>

• Замените сопоставление результатов на одно для каждой возвращаемой сущности, например следующее:

    <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>

Кроме того, можно сопоставить результирующие наборы с сложными типами, например созданными по умолчанию. Для этого вы создаете новый сложный тип, а не удаляете их и используете сложные типы везде, где вы использовали имена сущностей в приведенных выше примерах.

После изменения этих сопоставлений можно сохранить модель и выполнить следующий код для использования хранимой процедуры:

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

Итоги

Здесь показаны два разных метода доступа к нескольким результирующих наборам с помощью Entity Framework. Оба из них одинаково допустимы в зависимости от вашей ситуации и предпочтений, и вы должны выбрать тот, который кажется лучшим для ваших обстоятельств. Планируется, что поддержка нескольких результирующих наборов будет улучшена в будущих версиях Entity Framework и что выполнение шагов в этом документе больше не потребуется.