Northwind Repository Pattern

I like to use Entity Framework and the Repository pattern so here’s a simple example of setting one up. I’d also like to use the UnitOfWork pattern and I’ll do a post on that later.

We need to make sure we have separation of concerns as part of our SOLID principles.

Make sure you have the Northwind database installed in your local instance of SQL. You can grab it from here… http://northwinddatabase.codeplex.com/ and do database, Restore Database or follow the instructions from somewhere like here… http://www.codeproject.com/Articles/42837/HowTo-Install-the-Northwind-and-Pubs-Sample-Data

Start out with a new Visual Studio project… a Test project.

New Test Project

New Test Project

Then we’ll add a new project of class library to the solution. Give it a Data.Repository name.

Repository Class
Repository Class Library

Then we’ll need another new project of class library to the solution. Give it a Data name.

Data Class
Data Class

Then we’ll need to add an ADO.NET Entity Data Model to the Repository project.

ADO.NET Entity Data Model
ADO.NET Entity Data Model

When clicking add, we’ll be asked for the type of model. Select Generate from Database.

Generate from database
Generate from database

Click next and setup the database connection. Call it NorthwindEntityContext.

Database connection
Database connection

Then choose the database objects we need. Just tables. Give it the repository namespace.

Database Objects
Database Objects

… and click Finish.

We should then have the edmx file which is an xml representation of the Northwind data model.

edmx file
edmx file

Just to tidy things up, we can add a solution folder called Data and drag and drop our Data and Data.Repository projects into it.

Data Solution Folder
Data Solution Folder
Screenshot
Drag drop Data and Data.Repository

Now we have all of our entities in our Repository project. We don’t really want them there, so let’s create another new project four our entities (another Class Library) and call it ClearOak.Northwind.Entity

Add another solution folder and call it Domain.  Add our new project to the Domain folder.

Domain folder
Domain folder

Then add a text template to the Entity project. This will generate our entity classes for us from the edmx data model file.

T4 Template
T4 Template

Copy the contents of the NorthwindDataModel.tt file to the Entity.tt file.

Copy contents to new template
Copy contents to new template

Save it. Click ok to any questions about running templates.

It will probably complain about not finding the edmx file. Go to the top of the file and modify the reference from

const string inputFile = @"NorthwindDataModel.edmx";

to

const string inputFile = @"..\ClearOak.Northwind.Data.Repository\NorthwindDataModel.edmx";

and hit save. Again, click ok to any questions about running templates. This time the error should disappear. We should now have our entities in the Domain Entity project.

Domain Entity project
Domain Entity project

Then delete the NorthwindDataModel.tt file from the Repository project. We don’t need that anymore.

Add references in the Repository project to the ClearOak.Northwind.Data and ClearOak.Northwind.Entity projects.

Add a using statement to the Repository NorthwindDataModel.Context.tt file to reference the ClearOak.Northwind.Entity library.

using statement
using statement

Save and build the solution. It should build at this point. If it doesn’t, review some of the steps above.

Now move over to our unit testing project. Rename UnitTest1.cs to NorthwindTests.cs    Also update the name of the class in the code to NorthwindTests.

Update the TestMethod to give it an accurate name for the test being performed. Let’s call it Query_GetAllCustomers()

Start typing to get the list of Customers using the following code:

var list = _repository.All<Customer>

In order for this to compile we need a reference to the Entity project. Resharper will add this for you with its hints. This will give us the Customer object. We will create the _repository in a second.

Resharper
Resharper

Complete the line of code like this:

var list = _repository.All<Customer>();

Resharper will then allow us to extract the field name.

Extract Field
Extract Field

Then we have this:

private object _repository;

We want to create an interface to the repository so we change its type from object to IRepository.

private IRepository _repository;

Again, Resharper allows us to create the interface ‘IRepository’.

    internal interface IRepository
    {
    }

Resharper allows us to move this to its own file.

namespace ClearOak.Northwind.Data.Test
{
    internal interface IRepository
    {
    }
}

We are going to copy this file to our data project. Also delete it from the test project. Update the namespace and change from internal to public. Like this:

IRepository
IRepository

So, now we’ve got a test that doesn’t care where our data is. It only knows there’s an interface to a repository where it can get stuff.

Add references in our Test project to the Data and Entity projects.

Back in our Test Method:

[TestMethod]
        public void Query_GetAllCustomers()
        {
            var list = _repository.All<Customer>().ToList();
        }

Click on the red ‘All’ and use Resharper to add the method to our interface.

namespace ClearOak.Northwind.Data
{
    public interface IRepository
    {
        void All<T>();
    }
}

We also want to turn that from void into a generic IQueryable like this:

IQueryable<T> All<T>();

Back in our Test Method we can now iterate over a list of Customer objects returned from our repository.

        [TestMethod]
        public void Query_GetAllCustomers()
        {
 
            var list = _repository.All<Customer>().ToList();
 
            foreach (var item in list)
            {
                Trace.TraceInformation("Company Name : {0}",item.CompanyName);
            }
        }

Our _repository is still null, so we need to set it to a NorthwindRepository()

Create a TestInitialize method

        [TestInitialize]
        public void Initialise()
        {
            _repository = new NorthwindRepository();
        }

Use Resharper to extract a type for NorthwindRepository. Create as a class. Move to an external file and then copy it to the Repository project. Delete the NorthwindRepository.cs file in the Tests project.

In the newly copied file, use Resharper to implement the missing members.

Implement Members
Implement Members

We then get

using System.Linq;
 
namespace ClearOak.Northwind.Data.Repository
{
    public class NorthwindRepository : IRepository
    {
        public IQueryable<T> All<T>()
        {
            throw new System.NotImplementedException();
        }
    }
}

If we hit CTRL-R+T on our Test Method (to debug the current test) then we see that the Exception is hit in the Repository.

Exception
Exception

We are missing the NorthwindEntityContext. This is the piece that joins up our database to our Entity model. We need to create an instance of the Context.

Use ctor <tab><tab> to create the constructor. Then replace the exception and return something from the Context. We will see the list of objects from the database in our Context.

Context
Context

We need to return a generic type and not just Orders so we need to code for the generic result set.

The generic type is a Set of T. This complains it’s a reference type.

return _context.Set<T>();

So we have to add the class constraint to our method. Resharper can do this for us. We get this:

        public IQueryable<T> All<T>() where T : class
        {
            return _context.Set<T>();
        }

This then complains about the Type parameter T in All<T>. We will need to update the IRepository to have the same class constraint.

namespace ClearOak.Northwind.Data
{
    public interface IRepository
    {
        IQueryable<T> All<T>() where T : class;
    }
}

If we run our test now, we get a different exception where it can’t find a connection string. Copy the Repository app.config to the Test app.config. Now re-run the test and see we have the 91 Customers and all their associated data.

Customers
Customers

Phew! We’ve implemented an abstract generic repository. We can also make the Repository inherit from IDisposable so we can clean up after ourselves.

Now instead of getting all the data we might want to be a bit more selective. Let’s say we wanted to only get the Customers and their Orders. We can create a generic method like the All() method to return just what we need.

Start out with a new Test Method.

        [TestMethod]
        public void Query_GetAllCustomersIncludingOrders()
        {
 
            var list = _repository.AllIncluding<Customer>().ToList();

Use Resharper on AllIncluding to add the method to the Repository.

We have to turn it into an IQueryable and also add the class constraint. Also we need to tell it to expect a delegate – the Func in our method which is basically a pointer to a method.

    public interface IRepository: IDisposable
    {
        IQueryable<T> All<T>() where T : class;
        IQueryable<T> AllIncluding<T>(Func<T,object> include ) where T : class;
    }

Our AllIncluding in the NorthwindRepository starts out like this:

public IQueryable<T> AllIncluding<T>(Func<T, object> include) where T : class
        {
            var retVal = _context.Set<T>();
 
            return retVal;
        }

We need to get our generic data (Customer in our Test Method) but then tell it what to include.

We continue like this, telling it to expect an array of params but the Include statement wants a string. Item is not a string.

        public IQueryable<T> AllIncluding<T>(params Func<T, object>[] include) where T : class
        {
            var retVal = _context.Set<T>();
 
            foreach (var item   in include)
            {
                retVal = retVal.Include(item);
            }
            return retVal;
        }

We need to wrap the Func in an Expression. Func<T> denotes a delegate which is pretty much a pointer to a method and Expression<Func<T>> denotes a tree data structure for a lambda expression. For more see this stackoverflow question.

It also has issues converting a DbSet into an IQueryable so we need to define our retVal a bit more clearly.

        public IQueryable<T> AllIncluding<T>(params Expression<Func<T, object>>[] include) where T : class
        {
            IQueryable<T> retVal = _context.Set<T>();
 
            foreach (var item   in include)
            {
                retVal = retVal.Include(item);
            }
            return retVal;
        }

Then update our IRepository to have the same signature with the Expression.

So with a bit of a leap we can have many Test Methods using the Repository:

[TestClass]
    public class NorthwindTests
    {
        private IRepository _repository;
 
        [TestInitialize]
        public void Initialise()
        {
            _repository = new NorthwindRepository();
        }
 
        [TestMethod]
        public void Query_GetAllCustomers()
        {
 
            var list = _repository.All<Customer>().ToList();
 
            foreach (var item in list)
            {
                Trace.TraceInformation("Company Name : {0}",item.CompanyName);
            }
        }
 
        [TestMethod]
        public void Query_GetAllCustomersIncludingOrders()
        {
            // Use a Lambda expression to return the Customer's Orders 
            var list = _repository.AllIncluding<Customer>(p=>p.Orders).ToList();
 
            foreach (var item in list)
            {
                Trace.TraceInformation("Company Name : {0}, Orders Count : {1}", item.CompanyName, item.Orders.Count);
            }
        }
 
        [TestMethod]
        public void Query_GetAllCustomerNamesIncludingOrderCount()
        {
            // Use a Lambda expression to return the Customer's Name and Order count (but no more).
            // new gives us an anonymous object
            var list = _repository.AllIncluding<Customer>().Select(s=> new {s.CompanyName, s.Orders.Count}).ToList();
 
            foreach (var item in list)
            {
                Trace.TraceInformation("Company Name : {0}, Orders Count : {1}", item.CompanyName, item.Count);
            }
        }
 
 
        [TestMethod]
        public void Query_GetAllCustomerNamesIncludingOrderCountWithMoreThanThreeOrders()
        {
            // Use a Lambda expression to return the Customer's Name and Order count (but no more) who have more than 3 orders.
            // new gives us an anonymous object
            var list = _repository.AllIncluding<Customer>().Where(p=>p.Orders.Count>3).Select(s => new { s.CompanyName, s.Orders.Count }).ToList();
 
            foreach (var item in list)
            {
                Trace.TraceInformation("Company Name : {0}, Orders Count : {1}", item.CompanyName, item.Count);
            }
        }

These Test Methods can be seen converted to SQL in SQL Profiler.

 

 

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *