Using Adapter Pattern on Entity Framework Database First stored procedures results

Introduction

In this article we will show one approach to decouple the Entity Framework generated code for stored procedures from the higher layers of code by using the Adapter Pattern. This will result in better structured indeed easily maintainable code.

Background

Since Visual Studio 2008/.NET Framework 3.5 Microsoft has introduced a new technology for data acces: the Entity Framework (EF).

While EF is a tool for object-relational mapping in the trend of Java based Hibernate and JPA, for the .Net developer in practice it translates into an alternative to using the DataSets which by the way born with the .Net platform.

When talking about EF generally it is emphasized the abilities to translate the relational model of the database to the object oriented model which is lingua franca of the modern programming languages. However EF also supports the use of database stored procedures which is a must if we consider the popularity of the stored procedures in current commercial database management systems. Recently I found myself upgrading an old web application which implemented the database access through DataSets built from the result sets returned by stored procedures. DataSets also have wide support for direct access to database tables but it was tabu for the client. He only permitted access to the backend database by using stored procedures. Personally I think this is not a bad idea, especially if all the client’s business rules were implemented in stored procedures in the backend.

The web application to be replaced used DataSets as data transfer objects between the business layer (implemented as stored procedures inside the database) and the presentation layer. I always thought that this way of using DataSets is inefficient. It is a lot of functionality built inside DataSets just for direct database table access which makes them “too heavy” for my taste to be used as data transfer objects. So for the new web app I decided replace DataSets with lightweight data transfer objects. And by the way give the new Entity Framework a chance to perform.

The web application to be replaced used DataSets as data transfer objects between the business layer (implemented as stored procedures inside the database) and the presentation layer. I always thought that this way of using DataSets is inefficient. It is a lot of functionality built inside DataSets just for direct database table access which makes them “too heavy” for my taste to be used as data transfer objects. So for the new web app I decided replace DataSets with lightweight data transfer objects. And by the way give the new Entity Framework a chance to perform.

Looking at the EF generated code

First thing I like about the EF was the ability to generate automatically glue code between C# and stored procedure. No more banging on the keyboard long lists of SqlCommand / SqlParameter and adapting Sql types to C# types. Just select the stored procedure you want to invoke y voila: the code is automagically created for you. The stored procedure call is wrapped in such a way that you invoke the stored procedure code a-la C# function. Parameters are directly mapped to nullable types. The result is returned as a collection of some complex type… You guess: the complex type is derived from the resulting result set metadata. The question about what happens if the stored procedure returns more than one result sets will not be reviewed here. Just say that at least up to the EF 5 it was not automatically supported. It is useful to know that EF extracts result set metadata by calling the stored procedure after setting all the parameters to null and setting Set FMTONLY On. You can use this to debug possible issues with the EF code generation.

Well, finally you get your priceless database data as a collection of C# objects… Not so easy. The resulting collection can be traversed only once. For example If you call the collection Count property first, then you cannot retrieve the actual data because the internal implementation of the Count property already iterated the collection... So it makes sense to move the returned objects into a standard widely used List<> collection. The other problem arises from the dependencies the EF code generator injects into your code. The generated objects code is under the control of the generator. You should not modify this because this code will be deleted the next time EF decides to refresh the code. Then not only your higher level code result dependent on lower level objects violating the dependency inversion principle but furthermore you have no control at all over this lower level code.

The dependency inversion principle promotes decoupling of the higher level layers from the lower levels by making both depend on abstractions. To return our code to the good design practices I propose to provide interfaces to data transfer objects which will contain the stored procedure resulting data sets, and use the Adapter Pattern to translate the EF generated data types to our interfaces. Such a way we can create a thin layer between the EF and the higher level layers that decouples them from the EF specific code

Hands on the code

Lets start by creating the Sql database stored procedures. We will use the Northwind database available here. For our purposes we will add the following basic stored procedures:

CREATE PROCEDURE CustomersByCountry
	@sCountry Varchar(max)
AS
BEGIN
	Select customers.*
	From Customers customers
	Where Country = @sCountry
END
GO

CREATE PROCEDURE CustomersWhoBoughtProduct
	@nProductId Int
AS
BEGIN
	Select Distinct customers.*
		From [Order Details] orderDetails
			Inner Join Orders orders
				On orders.OrderId = orderDetails.OrderId
			Inner Join Customers customers
				On customers.CustomerId = orders.CustomerId
	Where orderDetails.ProductId = @nProductId
END
GO

Note both stored procedure returns similar data sets from the Customers’ table.

Now lets work with the C# code. I used Visual Studio 2012.

• Create a console application project.
• Install Entity Framework by using “Manage NuGet Packages”. In my case it downloaded EF version 6.1. Since EF 6 is not deployed out of the box bu VS2012, I should download and install EF6 tools for VS2012.
• Add the database connection (localhost server, Northwind database)
• Add the Entity Framework by clicking Project, Add New Item, ADO.NET Entity Data Model (name it MyModel), Empty EF Designer model
• At the generated file MyModel.edmx right click, Update model from database
• Add Stored procedures and functions
• Check CustomersByCountry, CustomerWhoBoughtProduct
• Save the MyModel.edmx file before look for any generated code.
• In the Solution Explorer select the MyModel.edmx file. Click anywhere at the left panel. It should appear a Model Browser tab at the right panel, just to the left of the Solution Explorer tab.
• Select the Model Browser. The generated model is displayed.
• Look inside MyModel, Complex Types: note there are two types generated from the output result sets of the selected stored procedures: CustomersByCountry_Result and CustomerWhoBoughtProduct_Result.
• Now select Solution Explorer. Search for CustomersByCountry_Result and CustomerWhoBoughtProduct_Result. That will point us to the generated code.

(Note, you should always save any changes to the .edmx file before looking for the generated code. The code generator is invoked just when this file is saved to disk.)

Inspecting the code unsurprisingly it results the EF generated functions CustomersByCountry and CustomerWhoBoughtProduct which are the C# mirrors of their stored procedure parents. The generated CustomersByCountry_Result and CustomerWhoBoughtProduct_Result data types are a C# translation of the Customers database metadata. Note EF lacks the intelligence to merge both CustomersByCountry and CustomerWhoBoughtProduct into the same type: Customers. It is good that EF does not do that. Otherwise what if somebody changes the resulting set of one of the stored procedures…? That could broke all our code. It is another reason for abstracting the generated classes. Also note the usage of the ObjectResult collection as the result containers. This is the above mentioned collection you can traverse just once. In addition to that ObjectResult is a class too in the EF mood to my taste. I prefer widely used List<> container.

Then lets outline what we will do:

• Create a Customer interface to abstract both CustomersByCountry_Result and CustomerWhoBoughtProduct_Result
• Create adapters that will convert CustomersByCountry_Result and CustomerWhoBoughtProduct_Result objects into the new Interface
• Wrap the EF created CustomersByCountry and CustomerWhoBoughtProduct functions into a new ones which return List<Customer> instead ObjectResult<>

Lets create a folder called dao (for data access object) to separate our code from the rest of the application.

First we will declare the interface which will abstract the EF generated complex types. I named it  ICustomer. His members will be the same from the CustomersByCountry_Result. Just apply cut_and_paste_programming and convert the generated class into an interface with read-only properties. We will adhere to the philosophy that data extracted from database is read-only. If you want to modify the data, call another stored procedures who will do the create, update and delete jobs. Thus we will make our data transfer objects immutable. There are a lot of advantages dealing with immutable objects so lets improve our code by using them.

Then we will copy the generated from the EF generated code

public partial class CustomersByCountry_Result
{
    public string CustomerID { get; set; }
    public string CompanyName { get; set; }
    ...
}

public interface ICustomer
{
    string CustomerID { get; }
    string CompanyName { get; }
    ...
}

Next implement the adapters. Note we will need two of them to convert from both  CustomersByCountry_Result and CustomerWhoBoughtProduct_Result to our ICustomer interface. Name the adapters CustomersByCountry_Result_Adapter and CustomerWhoBoughtProduct_Result_Adapter  

public class CustomersByCountry_Result_Adapter : ICustomer
{
    private CustomersByCountry_Result adaptee = null;
    public CustomersByCountry_Result_Adapter(CustomersByCountry_Result adaptee)
    {
        this.adaptee = adaptee;
    }
    public string CustomerID
    {
        get { return adaptee.CustomerID; }
    }
    public string CompanyName
    {
        get { return adaptee.CompanyName; }
    }
    ...
}

The code for CustomerWhoBoughtProduct_Result_Adapter is similar.

And to finally decouple us from EF lets encapsulate the EF imported functions in our data access class.

public static class CustomerDao
{
    public static List<ICustomer> CustomersByCountry(string country)
    {
        List<ICustomer> result = new List<ICustomer>();
        using (MyModelContainer ctx = new MyModelContainer())
        {
            var entityList = ctx.CustomersByCountry(country).ToList();
            foreach (var entity in entityList)
            {
                result.Add(new CustomersByCountry_Result_Adapter(entity));
            }
        }
        return result;
    }
        
    public static List<ICustomer> CustomersWhoBoughtProduct(int productId)
    {
        List<ICustomer> result = new List<ICustomer>();
        using (MyModelContainer ctx = new MyModelContainer())
        {
            var entityList = ctx.CustomersWhoBoughtProduct(productId).ToList();
            foreach (var entity in entityList)
            {
                result.Add(new CustomersWhoBoughtProduct_Result_Adapter(entity));
            }
        }
        return result;
    }
}

(All this copy-and-paste coding is crying out for a macro that automates the process!)

Now lets write some example code on how to use our data access code:

class Program
{
    static void Main(string[] args)
    {
        var customers = CustomerDao.CustomersByCountry("USA");
        foreach (var customer in customers)
            Console.WriteLine(customer.ContactName);
        
        var moreCustomers = CustomerDao.CustomersWhoBoughtProduct(1);
        foreach (var otherCustomer in moreCustomers)
            Console.WriteLine(otherCustomer.ContactName);
        Console.ReadLine();
    }
}

Ta dam…! Note that our higher layer of the application will know just about ICustomer and List<ICustomer>, totally decoupled from the EF oddities. 

Let me know what do you think about this approach.

Conclusions

In this article we decoupled the Entity Framework generated code for stored procedure calling from the higher layers of an application by adhering to current design practices using the Adapter Pattern.

References

• Head First Design Patterns. Eric Freeman, Elisabeth Robson, Bert Bates, Kathy Sierra (O'Reilly Media 2004)
• Effective Java (2nd Edition) by Joshua Bloch, (Addison-Wesley, 2008)