The Repository Pattern with Linq to Fluent NHibernate and MySQL

I have heard a lot of good things about NHibernate, but have never had the opportunity to use it. In this post I will describe how to get started using Fluent NHibernate with Linq to NHibernate using MySQL for a database.

Code

The Back Story

To add a bit of concreteness to the design / code, let’s imagine that we need to build out the back-end for a delivery truck management system. The business goal is to strap each truck with internet (3G) GPS radios. The radios will report geo-location tracking information for the truck every 5 minutes. In addition, drivers will be scheduled / assigned to the trucks. Here is a simple database schema for our fictional example:

Each truck can have only one driver (one-to-one relationship), but each truck will have many locations (one-to-many relationship).

Installing MySQL

MySQL is a popular open source relational database management system (RDBMS). It is available in Windows and Linux flavors. In fact it is the ‘M’ in the ‘LAMP’ stack. MySQL is used in many high-profile, large-scale products including WIkipedia, Google, and Facebook.

I will be developing for a Windows-based server. You can download the MySQL bits from the developer download page. Be sure to download the following:

• MySQL Community Server – This is the database manager system.
• MySQL Workbench – This is a GUI tool database design and management.
• .NET Connector – This is a .NET based MySQL driver that you can use in your .NET project to interact with the database.

The site also provides a thorough guide to installing MySQL on Windows.

Create the Tables and Columns

Once the MySQL components are installed, start the MySQL Workbench to begin creating the database table. Click the ‘+’ to add a new database schema.

Enter a database name (TruckTracker), click ‘Apply’ and follow the instructions for adding the database.

To add a table double-click the ‘Add Table’ button.

On the ‘Table’ tab, enter a table name.

On the ‘Columns’ tab enter the columns for the table.

Click ‘Apply’, follow the directions and ‘Close’. Repeat these steps to enter the other two tables.

Notice that each table has an auto-indexing column called ‘Id’. This is the primary key for each table. The ‘locations’ and ‘drivers’ table also have a ‘TruckId’ column that will be used as the foreign key. Let’s hook those up.

Right-click the ‘drivers’ table and select the ‘Alter table…’ option. Click the ‘Foreign Keys’ tab and add a foreign key to look like the following:

Click ‘Apply’ and follow the directions to add the relationship between the ‘drivers’ and ‘trucks’ tables. Repeat for the ‘locations’ table.

Now we have completed the database schema.

Adopting a naming convention with your tables and columns makes your schema more clear. It also allows you to more easily write and use your data access code. In the above example I named the tables in the plural form (‘trucks’). When I create my entity I will name it ‘Truck’ since it is a single database row. In addition, I generally have an ‘Id’ as a primary key. Foreign keys columns are XyzId, where Xyz is the other table name in singular form. Foreign key relations ships are named FK_ABC_MNO where ABC is the current table and MNO is the referred table.

The Repository Pattern

There is always a lot of debate on databases (NoSQL vs Relational, SQL Server vs MySQL vs MongoDB) and data access patterns. This example will leverage a Repository pattern to implement this back-end design. The Repository pattern has the following benefits:

• Encapsulates the code that handles the impedance mismatch between the database and the entities. This will actually be handled by NHibernate.
• Hides the persistence implementation (MySQL, SQL Server…etc) details from other code layers. Again, this will be handled by NHibernate.
• Abstracts away the create, read update and delete (CRUD) operations.
• Facilitates DRY implementations by providing a generic implementation for common CRUD operations.

I have seen many twists or slight variations on the Repository pattern. They all eventually define a repository interface that can be used to create concrete implementations. Most of the variations involve factoring the CRUD operations into various interfaces. Here is what I have settled in on:

public interface IReadOnlyRepository<TEntity> where TEntity:class
{
    IQueryable<TEntity> All();
    TEntity FindBy(Expression<Func<TEntity, bool>> expression);
    IQueryable<TEntity> FilterBy(Expression<Func<TEntity, bool>> expression);
}

public interface IRepository<TEntity> : IReadOnlyRepository<TEntity> where TEntity:class
{
    bool Add(TEntity entity);
    bool Add(IEnumerable<TEntity> items);
    bool Update(TEntity entity);
    bool Delete(TEntity entity);
    bool Delete(IEnumerable<TEntity> entities);
}

public interface IIntKeyedRepository<TEntity> : IRepository<TEntity> where TEntity:class
{
    TEntity FindBy(int id);
}

I have separated the ability to ‘read’ from ‘write’. This allows certain repositories to provide a ‘read-only’ interface. Notice that the read methods either return the entity type or an IQueryable collection of entity types. This allows LINQ to be leverage to further refine the resulting set. In addition, we can also use LINQ’s delayed execution to provide optimized queries to the database (more later when discussing LINQ to NHibernate).

The read methods also include two methods that accept ‘Expression’ types. These allow the repository to provide flexibility by leveraging LINQ expressions for filtering. The ‘write’ CRUD operations are then provided by implementing the IRepository. The last interface provides a repository with integer type primary keys (like we have in our tables).

The above interfaces really provide re-usable application-level infrastructure and should be contained in their own assembly. Here is a screenshot of the project containing the Repository infrastructure.

Nothing in this project is specific to a database selection or to a particular application. In other words, this is all re-usable goodness. Later we will look at a NHibernate implementation of these interfaces.

Create the Application Entities

The entities are the code objects that represent the data in the database. Tables are generally mapped to classes and columns are generally mapped to properties. However, that rule can (and often is) broken to provide a more application specific entity that can be a combination of multiple data tables / columns.

The entities are passed around the application and will cross application layers (data access, business logic, presentation). To keep these objects re-usable in the various layers, they will also be contained in their own assembly.

Here is the implementation for the entity objects:

public class Driver
{
    public virtual int Id { get; private set; }
    public virtual string FirstName { get; set; }
    public virtual string LastName { get; set; }
    public virtual Truck Truck { get; private set; }
}

public class Location
{
    public virtual int Id { get; private set; }
    public virtual DateTime Timestamp { get; set; }
    public virtual double Latitude { get; set; }
    public virtual double Longitude { get; set; }
    public virtual Truck Truck { get; set; }
}

public class Truck
{
    public virtual int Id { get; private set; }
    public virtual string Name { get; set; }
    public virtual string Type { get; set; }
    public virtual string PlateNumber { get; set; }
    public virtual Driver Driver { get; private set; }
    public virtual IList<Location> Locations { get; private set; }

    public Truck()
    {
        Locations = new List<Location>();
    }

    public virtual void AddDriver(Driver driver)
    {
        driver.Truck = this;
        Driver = driver;
    }

    public virtual void AddLocation(Location location)
    {
        location.Truck = this;
        Locations.Add(location);
    }
}

Notice the almost direct mapping of the database column names and the entity properties. This is possible only with a good naming convention. All the property and method elements are ‘virtual’ because this is required by NHibernate. NHibernate uses ‘virtual’ elements for the ‘lazy loading’ implementation.

An important feature of these entities, is that they do not betray how the persistence mechanism is implemented. They are clean from CRUD. They are light-weight because they carry only the minimum properties and methods. The ‘Truck’ entity has two helper methods (‘AddDriver’ and ‘AddLocation’) that make some of data access layer code cleaner.

One final thing to notice is instead of mapping the ‘TruckId’ foreign key the ‘Truck’ entity itself is mapped. NHibernate supports lazily loading these relationships (object graph). Same is true for the ‘Locations’ data associated with each truck. A list of ‘Location’ elements will be lazy loaded by NHibernate when we request a ‘Truck’ object.

Installing NHibernate

NHibernate is an ORM, or Object-Relational-Mapper for the Microsoft .NET platform. ORM tools provide a framework for mapping object-oriented domain models to traditional relational database tables / columns.  The NHibernate framework can supply most of the common CRUD operations. You can download NHibernate on their Source Forge page. Be sure to download the following:

One of the best ways to improve your “mad code skillz” is to read code. The above image also shows the NHibernate source code is available.

This will get you the NHibernate and the LINQ to NHibernate assemblies. LINQ to NHibernate extends NHibernate to provide optimized queries based upon LINQ expressions that support LINQ’s delayed execution concept.

LINQ to NHibernate plays an important role in our Repository pattern implementation. It allows us to get away with the ‘All’ method and not worry about paging concerns. Obviously we will want to have paging. However, with LINQ we get paging essentially for free by using he ‘Take’ and ‘Skip’ extension methods. LINQ to NHibernate takes the incoming LINQ expression (any Take, Skip, Where…), generates a SQL query and  has the database execute this query the first time an element is requested.

Also hop on over to the Fluent NHibernate site and download the latest and greatest. Historically, NHibernate (and many other ORMs) are configured via XML. Fluent NHibernate lets you write mappings in strongly typed C# code. This allows for easy refactoring, improved readability and more concise code.

Entity Mapping with NHibernate

Create a new project to contain all the NHibernate specific code that we are about to generate. Here is a screenshot of my complete project.

The ‘Map’ folder contains all the mappings between my entities and the database tables / columns. This is done using the fluent interface provided by Fluent NHibernate. For more information on Fluent NHibernate visit their wiki. The following is the mapping code.

    public class LocationMap : ClassMap<Location>
    {
        public LocationMap()
        {
            Table("locations");
            Id(x => x.Id);
            Map(x => x.Timestamp).Column("DateTime");
            Map(x => x.Latitude);
            Map(x => x.Longitude);
            References(x => x.Truck).Column("TruckId");
        }
    }

    public class DriverMap : ClassMap<Driver>
    {
        public DriverMap()
        {
            Table("drivers");
            Id(x => x.Id);
            Map(x => x.FirstName);
            Map(x => x.LastName);
            References(x => x.Truck).Column("TruckId");
        }
    }

    public class TruckMap : ClassMap<Truck>
    {
        public TruckMap()
        {
            Table("trucks");
            Id(x => x.Id);
            Map(x => x.Name);
            Map(x => x.Type);
            Map(x => x.PlateNumber);
            HasOne(x => x.Driver).LazyLoad().Cascade.All();
            HasMany(x => x.Locations).LazyLoad().Inverse().Cascade.All();
        }
    }

The power of Fluent NHibernate is that the intent of the above code is fairly clear without knowing a thing about Fluent NHibernate. The only somewhat tricky stuff is mapping the one-to-one (truck to driver) and the one-to-many (truck to locations) relationships.

The NHibernate Repository Implementation

Now it is time to implement the Repository interface using NHibernate. Since the entities are integer keyed we will implement the ‘IIntKeyedRepository’ interface.

public class Repository<T> : NHibernateContext, IIntKeyedRepository<T> where T : class
{
    private readonly ISession _session;

    public Repository(ISession session)
    {
        _session = session;
    }

    #region IRepository<T> Members

    public bool Add(T entity)
    {
        _session.Save(entity);
        return true;
    }

    public bool Add(System.Collections.Generic.IEnumerable<T> items)
    {
        foreach (T item in items)
        {
            _session.Save(item);
        }
        return true;
    }

    public bool Update(T entity)
    {
        _session.Update(entity);
        return true;
    }

    public bool Delete(T entity)
    {
        _session.Delete(entity);
        return true;
    }

    public bool Delete(System.Collections.Generic.IEnumerable<T> entities)
    {
        foreach (T entity in entities)
        {
            _session.Delete(entity);
        }
        return true;
    }

    #endregion

    #region IIntKeyedRepository<T> Members

    public T FindBy(int id)
    {
        return _session.Get<T>(id);
    }

    #endregion

    #region IReadOnlyRepository<T> Members

    public IQueryable<T> All()
    {
        return _session.Linq<T>();
    }

    public T FindBy(System.Linq.Expressions.Expression<System.Func<T, bool>> expression)
    {
        return FilterBy(expression).Single();
    }

    public IQueryable<T> FilterBy(System.Linq.Expressions.Expression<System.Func<T, bool>> expression)
    {
        return All().Where(expression).AsQueryable();
    }

    #endregion

}

This is a generic implementation that can be used to provide the common CRUD operations for all entity types. This keeps our code DRY and is one of the benefits of the Repository pattern. If there is a need for entity specific logic (business logic), then the Decorator Pattern can be used to extend this generic implementation.

This implementation also inherits from the ‘NHibernateContext’ base class. This base class provides the LINQ context and enables the LINQ features (dynamically generated queries, delayed execution).  The above code uses the LINQ features to implement the ‘All’ method.

For the most part, the other methods use features on the NHibernate ISession object. This object is injected into the repository via constructor injection. This sets us up to use an IOC container to control object lifetimes. This is extremely important with the ISession instance.

Configure NHibernate for MySQL

The next process that is necessary is to configure NHibernate to connect to our MySQL database. Once again, Fluent NHibernate will allow us to write ‘fluent’ code to complete this task.

public class NHibernateHelper
{
    private readonly string _connectionString;
    private ISessionFactory _sessionFactory;

    public ISessionFactory SessionFactory
    {
        get { return _sessionFactory ?? (_sessionFactory = CreateSessionFactory()); }
    }

    public NHibernateHelper(string connectionString)
    {
        _connectionString = connectionString;
    }

    private ISessionFactory CreateSessionFactory()
    {
        return Fluently.Configure()
            .Database(MySQLConfiguration.Standard.ConnectionString(_connectionString))
            .Mappings(m => m.FluentMappings.AddFromAssembly(Assembly.GetExecutingAssembly()))
            .BuildSessionFactory();
    }
}

The code above creates an instance of NHibernate’s ISessionFactory. This factory is used to create an ISession instance which will be then be injected into our repository implementation.

A Simple NHibernate Unit of Work Implementation

Typical database operations are transactional. That is all the operations are committed if they all succeed or none of the operations are committed if any operations fails. They all go in or none go in. Operations could be stacked up (multiple adds, an update and a delete). Is is usually a good idea to have this transactional code (or unit of work code) factored out. There are many ‘unit of work’ implementations. Some are more complex. However, this one will meet our needs:

public interface IUnitOfWork : IDisposable
{
    ISession Session { get; }
    void Commit();
    void Rollback();
}

public class UnitOfWork : IUnitOfWork
{
    private readonly ISessionFactory _sessionFactory;
    private readonly ITransaction _transaction;

    public ISession Session { get; private set; }

    public UnitOfWork(ISessionFactory sessionFactory)
    {
        _sessionFactory = sessionFactory;
        Session = _sessionFactory.OpenSession();
        Session.FlushMode = FlushMode.Auto;
        _transaction = Session.BeginTransaction(IsolationLevel.ReadCommitted);
    }

    public void Dispose()
    {
        Session.Close();
    }

    public void Commit()
    {
        if(!_transaction.IsActive)
        {
            throw new InvalidOperationException("No active transation");
        }
        _transaction.Commit();
    }

    public void Rollback()
    {
        if(_transaction.IsActive)
        {
            _transaction.Rollback();
        }
    }
}

In addition to committing the transaction, this code allows the operations to be committed or rolled back.

Putting It All Together

I have put together a few ‘tests’ that demonstrate how to use the Fluent LINQ to NHibernate repository that has been built. These example not only show how to use the code, but also demonstrate the power of the design choices.

First an example of how to save data to the database.

[TestMethod]
public void Add_100_Trucks_With_1000_Location_Points_Each()
{
    NHibernateHelper helper = new NHibernateHelper(_connectionString);

    for (int i = 0; i < 100; i++)
    {
        // Notice the unit of work we are using is to commit
        //    one truck's data at a time.
        UnitOfWork unitOfWork = new UnitOfWork(helper.SessionFactory);

        Repository<Truck> repository = new Repository<Truck>(unitOfWork.Session);

        Truck truck = CreateTruck(string.Format("Truck {0}", i + 1), 1000);
        repository.Add(truck);

        unitOfWork.Commit();
    }
}

private static Truck CreateTruck(string name, int numberOfLocations)
{
    Truck truck = new Truck
                      {
                        Name = name,
                        PlateNumber = string.Format("ABC-{0}", name),
                        Type = string.Format("Type {0}", name)
                      };
    Driver driver = new Driver
                        {
                            FirstName = "Bob",
                            LastName = "Cravens"
                        };
    truck.AddDriver(driver);

    for (int j = 0; j < numberOfLocations; j++)
    {
        Location location = new Location
                                {
                                    Timestamp = DateTime.Now.AddMinutes(5*j),
                                    Latitude = 10.0f + j,
                                    Longitude = -10.0f - j
                                };
        truck.AddLocation(location);
    }
    return truck;
}

This test method is clear enough to stand on its own. Do note that what is being ‘Added’ is an object graph containing a truck, driver, and 1000 locations. NHibernate is hiding a lot of complexity by adding the individual entities and ensuring the foreign key relationships are maintained.

The next example shows how to count the total number of ‘Location’ rows in the database.

[TestMethod]
public void Count_The_Number_Of_Locations_In_The_DB()
{
    NHibernateHelper helper = new NHibernateHelper(_connectionString);
    UnitOfWork unitOfWork = new UnitOfWork(helper.SessionFactory);
    Repository<Location> repository = new Repository<Location>(unitOfWork.Session);

    // This call uses LINQ to NHibernate to create an optimized SQL query.
    //    So instead of pulling all the entities from the DB and then using
    //    LINQ to count them, it instead pushes the counting to the DB by
    //    generating the appropriate SQL. Much much much faster!
    int count = repository.All().Count();
}

Here LINQ to NHibernate is optimizing the SQL query to make the database count the rows.

Here are two more examples:

[TestMethod]
public void Given_A_Driver_Determine_The_Last_Known_Location()
{
    NHibernateHelper helper = new NHibernateHelper(_connectionString);
    UnitOfWork unitOfWork = new UnitOfWork(helper.SessionFactory);
    Repository<Driver> driverRepo = new Repository<Driver>(unitOfWork.Session);
    Driver driver = driverRepo.All().First();
    if(driver!=null)
    {
        // At this point LINQ to NHibernate has not loaded all the Location entities.
        //    Because of LINQ's delayed execution, the following query can be optimized
        //    to let the DB do the filtering.
        Location lastLocation = driver.Truck.Locations.OrderByDescending(x => x.Timestamp).First();
    }
}

[TestMethod]
public void Get_The_Last_10_Locations_Of_A_Given_Truck()
{
    NHibernateHelper helper = new NHibernateHelper(_connectionString);
    UnitOfWork unitOfWork = new UnitOfWork(helper.SessionFactory);
    Repository<Truck> repository = new Repository<Truck>(unitOfWork.Session);
    Truck truck = repository.All().First();
    if(truck != null)
    {
        // Again the power of LINQ to NHibernates optimized queries and delayed execution.
        IEnumerable<Location> pagedLocations = truck.Locations.OrderByDescending(x => x.Timestamp).Take(10);
    }
}

These also use the power of LINQ to NHibernate to optimize queries and delay the execution. The first shows how the full object graph can be navigated and how to use LINQ to sort the ‘Location’ entries by their timestamp. The second is an example of how we could use the LINQ ‘Take’ (we could also have the ‘Skip’) extension methods can be used to implement paging.

Summary

The NHibernate ORM eco-system provides a really powerful platform to implement your data access layer. Fluent NHibernate allows configuration to occur in your code and LINQ to NHibernate provides the optimized queries and delayed execution that we are used to having with LINQ to SQL. Although there is no visual tooling to create your maps like in LINQ to SQL, that is not a limiting factor. The mapping files are easy to generate and are very clean.