Using an Entity Framework Designer for Stored Procedures Returning Multiple Result Sets in a Code First Data Access Web API

• Download source - 608.3 KB

Introduction

In applications developed with the Entity Framework (EF) Code First, we may use the SqlQuery or SqlCommand function to execute stored procedures within the data context. If a stored procedure returns multiple result sets, the only choice for now is to call the SqlCommand.ExecuteReader() with the ObjectContext.Translate() method. In my projects, however, using the Translate() method caused a serious performance issue when such a stored procedure returned a large number of records due probably to costly on-the-fly type mappings. I then tried to add an EF designer and generate an additional data context for stored procedures returning multiple result sets in Code First based Web API applications. Here are the full implementation outlines:

• Build a well-structured Web API application with EF Code First.
• Add an EF designer into the data access layer (DAL) to create an additional data context and Unit of Work module.
• Edit items in the EF designer and EDMX file to set up the stored procedure data type and function import mappings.
• Share the business logic layer (BLL) class to access multiple DAL repositories.
• Configure connection strings with Unity dependency injection and Unit of Work settings.
• Test the data retrieval from the Web API resource that calls the stored procedure using the Fiddler, AngularJS Web pages, and Web API client library.

You need the Visual Studio 2012/2013 with built-in IIS Express and access to the NuGet for running the sample application locally.

Web API Projects

The solution and projects from the downloaded source are shown in this screenshot:

Some key points of the application structure are outlined below:

1. The Web API controllers are in the standalone library project, SM.Store.Api, separated from the host project. Although the Web API is hosted by using the IIS Express in this sample, switching to OWIN host or OWIN-based self host can be easily done without altering the main API project.

2. The Web API is configured to use the attribute route mapping, which is only supported by the Web API 2.0 and above.

3. The entity and custom model objects are also in the standalone projects, SM.Store.Entities and SM.Store.Models, respectively. The compiled assemblies can be shared by the client applications for matched model types if needed.

4. The main data context object is created with the standard EF Data First approach. The database initializer is coded in the DAL project and executed through settings in the Web.config of the host project.

5. The dependency injection pattern with the Microsoft Unity is used to access DAL repository, Unit of Work, and BLL objects. The design time configurations are set in the Unity.config file from the host project whereas the runtime instance resolving logic is in the main API project.

6. In addition to hosting the Web API, the SM.Store.Api.Web project also contain pages with the pure client-side code written in AngularJS, Bootstrap, and HTML 5 for accessing the Web API resources.

7. The TestApiClientConsole project uses the Web API Client library as a Web API client application.

Preparing Stored Procedures in Code First Database

In the Code First, we can add stored procedures to the database with these options:

• Write an SQL string in the Seed() method of the data initializer:

  protected override void Seed(StoreDataContext context)
  {            
      //DB table data initialized here...
  	
      //Add stored procedures.
      context.Database.ExecuteSqlCommand(
         @"CREATE PROCEDURE dbo.GetAllCategorisAndProducts 
           AS 
           BEGIN
             --Main content here...
           END "
      );
  }

• Executing SQL scripts or using UI with any SQL data tool, such as SQL Server Management Studio or Visual Studio Server Explorer, to creating stored procedures. Before using this option, you need to complete all projects with the Code First and successfully run any of the data access processes to create the database that is defined in the connection string.

The sample application uses the seeding option for adding the stored procedures during database initialization. Similar to adding the data into tables, this operation does not change the database schema. Any SQL code change here won’t update the stored procedure in the database. To update the stored procedure, you need to either change it directly in the existing database or edit the code in the Seed() method, drop and then re-initialize a new database.

Adding EF Designer and Data Context to Existing Code First DAL

The Code First DAL can be created using all standard approaches documented everywhere. After completion of all associated projects and starting the application with one of the data access calls, you can then add an EF designer and additional data context object to the DAL by right-clicking the DAL project name, selecting Add, New Item…, Visual C# Items, Data, and ADO.NET Entity Data Model. On the subsequent wizard screens, use all default settings except these two:

1. Select or enter the database that is previously created by the Code First on the Choose Your Data Connection screen.

2. Select the stored procedures in that database on the Choose Your Database Objects and Settings screen.

You may change the default values in those name input boxes but need to well consider the final names this time. Unlike project and class names, renaming some existing items related to the EF designer afterwards could become a nightmare.

• Name box on the Add New Item screen: This is for the EDMX and main parts of related file names in the entire EDMX group. If you rename it later everywhere in the code, the application will work fine but some file names will not be changed unless manually altering the physical file names and carefully editing the project XML file. The value in the sample application is StoreDataSp.

• Save connection settings in App.config as box on the Choose Your data Connection screen: This is for data context class name and connection string name. The value can be changed later without affecting functionality. It’s the StoreDataContext in the sample application.

• Model Namespace box on the Choose Your Database Objects and Settings screen. The value is extensively used for the namespace in the EDMX file and metadata definitions in the connection string. Renaming it later will cause possible metadata errors unless you replace the existing EF designer and data context with new ones. The sample application uses the name StoreDataSpModal.

Mapping Stored Procedures with EF Designer

When the EF designer is ready, function import mappings of any stored procedure can be done the same as for the Database First scenario. See my previous article for how to use the workaround for mapping the stored procedures returning multiple result sets with the EF designer and EDMX file.

After doing the mapping, you may move the designer-generated T4 template, [model-name].tt, and dependent files, to the standalone entity or custom data model class library project. Here is how I did for moving the model type template group from SM.Store.Api.DAL to the SM.Store.Api.Models project in the sample application.

1. Open the Windows Explorer and find the SM.Store.Api.DAL folder.

2. Select the StoreDataSp.tt, then drag and drop it to the SM.Store.Api.Models project on the Solution Explorer of the Visual Studio.

3. Open the new StoreDataSp.tt from the Visual Studio and edit the line to point the input file to the original EDMX file.

   Before editing:

   const string inputFile = @"StoreDataSp.edmx";

   After editing:

   const string inputFile = @"../SM.Store.Api.DAL/StoreDataSp.edmx";

4. Save the updated StoreDataSp.tt file. This will auto repopulate the new template group with mapped type files, the Category_Result.cs and Product_Result.cs for the stored procedure in our case. If any change is made by using the designer later, auto updates of model type files can be performed by right-clicking the StoreDataSp.tt in the SM.Store.Api.Models project on the Solution Explorer and select the Run Custom Tool command.

5. Delete the StoreDataSp.tt group in the SM.Store.Api.DAL project from the Visual Studio Solution Explorer.

The DAL and models projects before moving the StoreDataSp.tt group:

The DAL and models projects after moving the StoreDataSp.tt group:

The benefit of this re-structuring is that the model type files in a standalone assembly can easily be accessed by all referencing projects or applications. You will see an example in the test Web API Client library project described later.

Connection Strings

The database connection string for the designer-generated data context looks differently from that for the Code First data context since the former contains additional metadata definitions. In the sample application, settings of two connection strings are placed in the Web.config file of the Web API host project.

<connectionStrings>
    <!--Code First data context connection string-->
    <add name="StoreDataContext" connectionString="Data Source=(LocalDb)\v11.0;
     Initial Catalog=StoreCF6;Integrated Security=SSPI;
     AttachDBFilename=|DataDirectory|\StoreCF6.mdf;integrated security=True;
     MultipleActiveResultSets=True" providerName="System.Data.SqlClient" />

    <!--Designer based data context connection string-->
    <add name="StoreDataSpContext" 

     connectionString="metadata=res://*/StoreDataSp.csdl|res://*/StoreDataSp.ssdl|
     res://*/StoreDataSp.msl;provider=System.Data.SqlClient;
     provider connection string=&quot;data source=(localdb)\v11.0;
     initial catalog=StoreCF6;integrated security=True;MultipleActiveResultSets=True;
     App=EntityFramework&quot;" providerName="System.Data.EntityClient" />
</connectionStrings>

In the Unity.config file, connection string placeholders are set within the Unit of Work constructor nodes. There are also two similar sets of the configurations and related items targeted for Code First and designer-based data context objects, respectively. The configuration setting in the Unity.config file for the designer-based context connection string is like this:

<register type="SM.Store.Api.DAL.IStoreDataSpUnitOfWork" 

 mapTo="SM.Store.Api.DAL.StoreDataSpUnitOfWork">
   <lifetime type="singleton" />
   <constructor>
      <!--Set placeholder for value attribute and replace it at runtime-->
      <param name="connectionString" value="{connectionString_SP}" />
   </constructor>
</register> 

In the constructor of the Unit of Work for the designer based data context, the connection string value is retrieved from the Web.config file based on the passed placeholder name. The real connection string value will then be further injected into the designer based data context constructor.

public StoreDataUnitOfWork(string connectionString)
{           
    if (connectionString == "{connectionString_SP}")
    {
        connectionString = ConfigurationManager.ConnectionStrings
                           ["StoreDataSpContext"].ConnectionString;
    }
    this.context = new StoreDataSpContext(connectionString);
}

Accessing Multiple Repositories from BLL

Add the EF designer to the Code First results in multiple programming structures. In the sample application, I keep the multiple data context objects, Unit of Work items, and repositories in the DAL but use shared BLL object to call the multiple repositories. This type of operation is achieved by overloading the BLL object constructors. The ProductBS class can be instantiated by passing the IProductRepository or IProductSpRepository, or both.

public class ProductBS : IProductBS
{
    private IProductRepository _productRepository;
    private IProductSpRepository _productRepository_SP;
    
    public ProductBS(IProductRepository productRepository)
    {
        if (productRepository != null)
            this._productRepository = productRepository;            
    }
    public ProductBS(IProductSpRepository productRepository_SP)
    {            
        if (productRepository_SP != null)
            this._productRepository_SP = productRepository_SP;
    }
    public ProductBS(IProductRepository productRepository, 
                     IProductSpRepository productRepository_SP)
    {
        if (productRepository != null)
            this._productRepository = productRepository;
        if (productRepository_SP != null)
            this._productRepository_SP = productRepository_SP;
    }
        
    //- - - Other code    
} 

Retrieve Data Using Mapped Stored Procedure

Here are the code examples of calling the GetCategoriesAndProducts stored procedure for the data retrieval in the sample application.

In the SM.Store.Api.ProductsController:

[Route("/api/getmultiplesets")]
public CategoriesProducts GetCategoriesAndProducts()
{
    var resp = new CategoriesProducts();            
    IProductBS bs = DIFactoryDesigntime.GetInstance<IProductBS>();
    resp = bs.GetCategoriesAndProducts();            
    return resp;
} 

In the SM.Store.Api.BLL.ProductBS:

public CategoriesProducts GetCategoriesAndProducts()
{    
    Return this._productRepository_SP.GetCategoriesAndProducts(); 
}

In the SM.Store.Api.DAL.ProductSpRepository:

public CategoriesProducts GetCategoriesAndProducts()
{
    CategoriesProducts categProd = new CategoriesProducts();
    categProd.Categories = new List<Category_Result>();
    categProd.Products = new List<Product_Result>();

    //Call stored procedure and get all result sets
    var results = this.UnitOfWork_SP.Context.GetAllCategorisAndProducts();

    //Get first enumerate result set
    categProd.Categories.AddRange(results);

    //Get second result set
    var products = results.GetNextResult<Product_Result>();
    categProd.Products.AddRange(products);

    //Return all result sets
    return categProd;
}

In the SM.Store.Api.DAL.StoreDataSpContext:

public virtual ObjectResult<Category_Result> GetAllCategorisAndProducts()
{
    return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction<Category_Result>
           ("GetAllCategorisAndProducts");
}

Starting Web API Host

Before starting the Web API and running the sample application, compile the Visual Studio solution. This also downloads all needed library files from the NuGet.

The IIS Express web host can be started in non-debugging mode by executing the line in the Command Prompt:

C:\Program Files (x86)\IIS Express\iisexpress.exe" /site:SM.Store.Api.Web 

Or executing the SM.Store.WebApi_SiteStart.bat file included in the downloaded source.

The sample application sets the dummy index2.html as the start page as default for starting the web host in the debugging mode. Press F5 to start the IIS Express Web API host for a debugging session.

Test Web API Using Fiddler

If you execute the "http://localhost:5611/api/getmultiplesets" on the Composer tab of the Fiddler immediately after rebuid the downloaded source and start the IIS Express, you will get the "500 Internal Server Error…The underlying provider failed on open" error messages.

This occurs because the Web API call connects to the EF designer-generated data context for which the underlying database has not yet been created. The database file and the SQL Server database specified in connection string will be generated at the time when a data access method in the Code First data context is called. Thus, let’s firstly call an API resource, "http://localhost:5611/api/products/2", which is related to the Code First data context.

As long as the database, tables, and stored procedures exist, we can then successfully call the Web API associated with the designer-based data context.

Pure AngularJS MVC Client Website

The sample application includes a simple pure client-side code website written in AngularJS, Bootstrap, and HTML 5 to consume the Web API. Although it shares website for the Web API hosing, you can separate the index.html in the root and all files in the Content, Pages, and Scripts folders, to form a new standalone website if needed.

The site uses the standard AngularJS MVC structure and MVVM pattern without any server-side code (such as Razor) syntax. Below are the code lines in the controllers.js for directly calling the Web API and getting the data from the stored procedure returning two result sets:

$http({
    url: 'api/getmultiplesets',
    method: "GET"
}).
  success(function (data, status, headers, config) {        
    $scope.model.categories = data.Categories;
    $scope.model.products = data.Products;
}).
  error(function (data, status, headers, config) {
    $scope.model.errorMessage = "Error occurred status:" + status; 
});

Here the client-side model types for multiple result sets are auto mapped based on the structures of the data object and child objects deserialized from the response content. No further effort is needed. These data models are then bound to the HTML elements in the views (HTML files) for the display.

You need to set the index.html page as the start page in the SM.Store.Api.Web project and start it in the debugging mode or simply run the index.html in non-debugging mode using the View in Browser context menu command. When clicking the Category and Product Lists menu link, both Category and Product grids will be displayed on the page.

Test with Web API Client Library

The TestApiClientConsole project in the sample application shows how to use the Web API Client library to obtain the data from the same method for processes with the stored procedure returning two result sets. The project uses this local method to call the Web API and display the data:

private static void GetCategoriesAndProducts()
{
    var uri = apiBaseUri + "getmultiplesets";
    CategoriesProducts resp = default(CategoriesProducts);

    using (HttpClient client = new HttpClient())
    {
        HttpResponseMessage result = client.GetAsync(uri).Result;
        resp = result.Content.ReadAsAsync<CategoriesProducts>().Result;
    }

    //Display data using custom genetic List extension ToString<>.
    var strResult = resp.Categories.ToString<Category_Result>() + "\n\n";
    strResult += resp.Products.ToString<Product_Result>
                 (include:"ProductName,CategoryId,UnitPrice");
    Console.Write(strResult);
}  

Unlike accessing the Web API by direct AJAX calls from a website as shown in the previous example, using the Web API Client library needs all required model types that are defined in the client-side and match those in the API server. You can certainly create the model classes in the client applications but it’s efficient and reliable to use shared assemblies for model types. As described previously in the article, the Category_Result and Product_Result class files are moved from the SM.Store.Api.DAL to the standalone SM.Store.Api.Models project. The client application just needs to include and reference the SM.Store.Api.Models project or complied assembly for calling the getmultiplesets API resource.

To see this console application in action, firstly make sure that the Code First generated database already exists and the IIS Express web host is started by any mean mentioned above. Then right-click the TestApiClientConsole project on the Visual Studio Solution Explorer and select Debug, Start new instance.

Summary

We can certainly mix the EF designer-based and Code First data context objects in the same application such as a data access Web API. This is particularly useful when we need to interact with stored procedures returning multiple result sets unless any new version of the EF or development tool supports programmatically code mappings for this type of stored procedures.

History

• 8^th August, 2014: Initial version