NHibernate and Linq

Summary

In my last post, I showed how to get NHibernate up and running.  This article will talk about using Linq with NHibernate.  I’m also going to add another table to the mix and show a sample query that joins these two tables together.


Using Linq

If you typed in the project from my last post or you downloaded the project (download it here), then you can continue on with this part of the task.  One of the static objects provided in the IProductRepository.cs file was the NHibernateHelper class.  I’m going to use the OpenSession() method available from this class to create a context for my test query.  In the Program.cs file (the starting method of your program), add some headers:

using System;
using System.Linq;
using NHibernate;

using NHibernate.Linq;

Now add the following code to your “Main” method:

using (ISession session = NHibernateHelper.OpenSession())
{
    var query = from p in session.Query<Product>() select p;

    foreach (var item in query)
    {
        Console.WriteLine(item.Name);
    }
}

Console.ReadKey();

Now, when you execute your program, you’ll see a list of all the product names in the console window (as well as the query that was sent).  The key to making this work is to include the NHibernate.Linq (as well as the normal System.Linq).  Then you can use Linq similar to Entity Framework.  One difference in the query you might have noticed is the “session.Query<Product>()” syntax.  “Query” is NHibernate version 3.  Versions 2 and earlier used “Linq” in the syntax.  Entity Framework only uses the table name (like “session.Product”).


Adding a Second Table

Now, let’s add a second table.  First, I’m going to create the table in MS SQL Server, called “Store”.  This will be the table that contains a list of stores where the products are located.  You can use this SQL script to create the new table:

USE [sampledata]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Store](
[id] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Address] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[City] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[State] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Zip] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [PK_Store] PRIMARY KEY CLUSTERED 
(
[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

Add a foreign key field to your product table:

ALTER TABLE dbo.Product ADD Store2 int NULL

Then populate the store table with two records:

INSERT INTO store
(
Name
)
VALUES
(
Shop X
)
GO
INSERT INTO store
(
Name
)
VALUES
(
East Market
)

GO

Then doctor up the product foreign key to point to these two stores:

UPDATE product SET store=1

UPDATE product SET store=2 WHERE id > 2 

OK, one more SQL step to get the data in synch with what I’m doing here.  Create a relational integrity constraint between the product foreign key and the store table:

ALTER TABLE product ADD FOREIGN KEY (store) REFERENCES Store(Id)

On to the Code

Now we need to add a “store.hbm.xml” file and a “store.cs” file, just like we did with product.  Add the store.hbm.xml file to the mappings directory.  Make sure the “Build Action” property is set to “Embedded Resource”.  Add this code to the file:

<?xml version=”1.0encoding=”utf-8” ?>
<hibernate-mapping xmlns=”urn:nhibernate-mapping-2.2assembly=”NHibernateTestBlognamespace=”NHibernateTestBlog“>

  <class name=”Storetable=”Store“>
    <id name=”Idcolumn=”Idtype=”Int32“>
      <generator class=”identity” />
    </id>
    <property name=”Namecolumn=”Name” />
    <property name=”Addresscolumn=”Address” />
    <property name=”Citycolumn=”City” />
    <property name=”Zipcolumn=”Zip” />
  </class>


</hibernate-mapping>

Now create the store.cs file inside your domain folder, and copy this into it:

namespace NHibernateTestBlog
{
    public class Store
    {
        public virtual int Id { get; set; }
        public virtual string Name { get; set; }
        public virtual string Address { get; set; }
        public virtual string City { get; set; }
        public virtual string Zip { get; set; }
    }

}

At this point, we can write a query to join these two tables together.  Go to “Program.cs” and change your query to look like this:

var query = from p in session.Query<Product>() 
            join s in session.Query<Store>() on p.Store equals s.Id
            where s.Id == 2

            select p;

When you run your program, you’ll see the three products that belong to store number 2.

Summary

At this point I’ve only covered an example where multiple tables are joined using Linq in NHibernate.  One minor issue to note is that I did not set the foreign key (product.store) to a not null field.  It’s best to do this to prevent someone from attempting to insert a record with a null for this field (the constraint in SQL will complain).

There is also a method to setup many-to-one list objects in NHibernate that I’m not going to dive into yet.  For an example of this technique I will refer you to this article: Playing with NHibernate – Inverse and Cascade mapping attributes.




 

NHibernate

Introduction

Some time ago I talked a little about NHibernate.  This is the .Net version of the Hibernate ORM.  I’ve also made several failed attempts to make NHibernate work in Visual Studio with MS SQL server.  I worked my way through this example several times: Your first NHibernate based application.  Needless to say, NHibernate has a bit of a learning curve and I have finally achieved a successful setup of NHibernate with MS SQL server 2005.  So here’s how I did it…

Setting up

First, create a new console application.  Now add in the NHibernate dlls using NuGet.  I don’t particularly like NuGet because I think it’s very bloated, but this is a start.  I will be stripping out the NuGet package stuff before I upload the sample code to this website.  So you won’t see any package stuff in the project.  Instead, you’ll see a directory called “3rdparty” in the project where I’ll stick a copy of the necessary dll’s and xsd files.

Create a directory in your project called “mappings”, another directory called “domain” and you can optionally create a “design” directory if you want to create an object map (I’m not going to bother with this setup).

The Database Setup

For the database, I’ll be using MS SQL server 2005.  I created a database called “sampledata” and I created one table in this database called “Product”.  You can use this SQL script to generate the table inside your database:

USE [sampledata]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Product](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Category] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Discontinued] [bit] NULL,
PRIMARY KEY CLUSTERED 
(
[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]


Time to get Serious

Create an xml file in the project named “hibernate.cfg.xml” and paste this code into it:

<?xml version=”1.0encoding=”utf-8” ?>
<hibernate-configuration xmlns=”urn:nhibernate-configuration-2.2“>
  <session-factory>
      <property name=”connection.provider”>NHibernate.Connection.DriverConnectionProvider</property>
    <property name=”dialect“>NHibernate.Dialect.MsSql2005Dialect</property>
    <property name=”connection.driver_class“>NHibernate.Driver.SqlClientDriver</property>
    <property name=”connection.connection_string“>Server=sqlserverdatabasename;Initial Catalog=sampledata;Integrated Security=True</property>
    <property name=”show_sql“>true</property>
  </session-factory>

</hibernate-configuration>

First of all, I like the idea of creating a separate file containing just the hibernate database configuration information.  That keeps things tidy and grouped according to function.  You can also put this xml code inside your web.config or app.config file.

Update:

Make sure you set the properties of your config file to “Copy Always”:




Table Mappings

In your mappings directory add another xml file and name it “product.hbm.xml”.  This will be the xml mapping for one table called product and it will match the table defined earlier in the SQL server.  Once you create this file, you must change its build-action property to make it an “Embedded Resource”.  This is a critical step.



Copy this code into your xml file:

<?xml version=”1.0encoding=”utf-8” ?>
<hibernate-mapping xmlns=”urn:nhibernate-mapping-2.2assembly=”NHibernateTestBlognamespace=”NHibernateTestBlog“>

  <class name=”Producttable=”Product“>
    <id name=”Idcolumn=”Idtype=”Int32“>
      <generator class=”identity” />
    </id>
    <property name=”Namecolumn=”Name” />
    <property name=”Categorycolumn=”Category” />
    <property name=”Discontinuedcolumn=”Discontinued” />
  </class>
  
</hibernate-mapping>

Domain Stuff

In your domain folder, you’ll need to add a cs file with a class defining the table that we just created in xml above.  Name your file the same name as the table “product.cs” and paste this code into it (there are no usings):

namespace NHibernateTestBlog
{
    public class Product
    {
        public virtual int Id { get; set; }
        public virtual string Name { get; set; }
        public virtual string Category { get; set; }
        public virtual bool Discontinued { get; set; }
    }
}

There is also a repository file that is used for this sample.  This file contains the basic CRUD type stuff and looks somewhat redundant.  I will not deep-dive into this code in this post (especially since I haven’t completely analyzed it myself), but I’ll post it here so you can just copy and paste it.  Create a cs file in your domain folder named “IProductRepository.cs” and copy this code into it:

using System.Collections.Generic;
using NHibernate;
using NHibernate.Cfg;
using NHibernate.Criterion;

namespace NHibernateTestBlog
{
    public interface IProductRepository
    {
        void Add(Product product);
        void Update(Product product);
        void Remove(Product product);
        Product GetById(int productId);
        Product GetByName(string name);
    }

    public class NHibernateHelper
    {
        private static ISessionFactory _sessionFactory;
        private static ISessionFactory SessionFactory
        {
            get
            {
                if (_sessionFactory == null)
                {
                    var configuration = new Configuration();
                    configuration.Configure();
                    configuration.AddAssembly(typeof(Product).Assembly);
                    _sessionFactory = configuration.BuildSessionFactory();
                }
                return _sessionFactory;
            }
        }

        public static ISession OpenSession()
        {
            return SessionFactory.OpenSession();
        }
    }

    public class ProductRepository : IProductRepository 
    {
        public void Add(Product product)
        {
            using (ISession session = NHibernateHelper.OpenSession())
            using (ITransaction transaction = session.BeginTransaction())
            {
                session.Save(product); 
                transaction.Commit();
            }
        }
        
        public void Update(Product product) 
        {
            using (ISession session = NHibernateHelper.OpenSession()) 
            using (ITransaction transaction = session.BeginTransaction()) 
            { 
                session.Update(product); 
                transaction.Commit(); 
            }
        }

        public void Remove(Product product) 
        {
            using (ISession session = NHibernateHelper.OpenSession()) 
            using (ITransaction transaction = session.BeginTransaction()) 
            { 
                session.Delete(product); 
                transaction.Commit(); 
            }
        } 
        
        public Product GetById(int productId) 
        {
            using (ISession session = NHibernateHelper.OpenSession())
            {
                return session.Get<Product>(productId);
            }
        }

        public Product GetByName(string name)
        {
            using (ISession session = NHibernateHelper.OpenSession())
            {
                Product product = session
                    .CreateCriteria(typeof(Product))
                    .Add(Restrictions.Eq(“Name“, name))
                    .UniqueResult<Product>();
                
                return product;
            }
        }
    }
}
On with the Program

Now we’re going to create a CRUDTests class just to see how this all works.  This is not a unit test, it’s just a class that I created in the main project.  Create a cs file named “CRUDTests.cs” and paste this code into it:

using NHibernate;
using NHibernate.Cfg;

namespace NHibernateTestBlog
{
  public class CRUDTests
  {
    private ISessionFactory _sessionFactory;
    private Configuration _configuration;

    private readonly Product[] _products = new[]
      {
        new Product
          {
            Name = “Melon“,
            Category = “Fruits
          },
        new Product
          {
            Name = “Pear“,
            Category = “Fruits
          },
        new Product
          {
            Name = “Milk“,
            Category = “Beverages
          },
        new Product
          {
            Name = “Coca Cola“,
            Category = “Beverages
          },
        new Product
          {
            Name = “Pepsi Cola“,
            Category = “Beverages
          },
      };

    public CRUDTests()
    {
      _configuration = new Configuration();
      _configuration.Configure();
      _configuration.AddAssembly(typeof (Product).Assembly);
      _sessionFactory = _configuration.BuildSessionFactory();
    }

    public void CreateInitialData()
    {
      using (ISession session = _sessionFactory.OpenSession())
      using (ITransaction transaction = session.BeginTransaction())
      {
        foreach (var product in _products)
        {
          session.Save(product);
        }
        transaction.Commit();
      }
    }

    public void UpdateTest()
    {
      IProductRepository repository = new ProductRepository();
      var product = repository.GetById(1);
      product.Name = “Yellow Pear“;
      repository.Update(product);

      // use session to try to load the product            
      using (ISession session = _sessionFactory.OpenSession())
      {
        var fromDb = session.Get<Product>(product.Id);
      }
    }

    public void GetByIdTest()
    {
      IProductRepository repository = new ProductRepository();
      var fromDb = repository.GetById(1);
    }

    public void GetByNameTest()
    {
      IProductRepository repository = new ProductRepository();
      var fromDb = repository.GetByName(“Yellow Pear“);
    }
  }
}
Now it’s time to do something useful…

Open your Program.cs file and add the following crud test object calls to it:

namespace NHibernateTestBlog
{
    class Program
    {
        static void Main(string[] args)
        {
            CRUDTests crudTests = new CRUDTests();
            crudTests.CreateInitialData();
            //crudTests.UpdateTest();
            //crudTests.GetByNameTest();
        }
    }
}
You’ll notice that I commented out two lines of code.  The first thing you’ll need to do is run this with the “CreateInitialData()” method first.  You’ll have to fix any miscellaneous configuration stuff if an error occurs until you get a successful run.  Once you have a successful run, you should see the data in your table.
Now comment the line “crudTests.CreateInitialData();” and uncomment the second and third lines.  You can put a breakpoint inside the “GetByNameTest()” method and see the results that are read.  If you are having difficulties in connecting to your database, examine your hibernate.cfg.xml settings.  This is where I had the most difficulty in getting this thing to work.

Here’s the code if you would like to download the project and modify to your own needs: NHibernateTestBlog.zip
Summary

In this blog post I showed the minimal code necessary to get NHibernate up and running.  I tried to keep this as simple as possible.  If I missed something, I’ll update this article to make sure that it’s accurate.  I found it frustratingly difficult to get this to work from the original article due to differences in the configuration file, so I hope this implementation helps others as an alternate reference point.  So far, this is only the minimum CRUD functionality.  In order to properly evaluate NHibernate over EF and Linq-to-SQL I’m going to have to attempt a few complex queries, some unit testing and mocking, plus another Smackdown!  I intend to do a performance test between EF, Linq-to-SQL and NHibernate side-by-side.  I might even do a more thorough performance test with larger sets of tables.

Until next time…


 

It’s a Love/Hate Relationship

I’ve been using Entity Framework for quite some time now.  I’ve built enough enterprise-level software to know what type of real-world limitations exist.  So I’m going to talk about what I love about Entity Framework that other ORMs don’t do, then I’m going to talk about the problems with EF that I hate.  I’ll be talking specifically about EF-6, version 6.0.2.

What I love about EF-6

First, and foremost is the visual interface.  It’s very easy to use and you can setup a complex ORM in seconds.  Just create a new edmx file (by right-clicking on the project and selecting Add -> New Item -> Data -> ADO.NET Entity Data Model), select your data server and database, then select tables/stored procedures, and save.  The visual tool shows relational integrity constraints and it rolls up many-to-many relationships to reduce clutter.  The visual interface feature is a big bonus.

The update model from database feature is nice.  That’s the feature that Linq-to-SQL lacks.  That is the number one reason our team switched from Linq-to-SQL to EF, because it was a nightmare adding or changing a field in a table.

I like the fact that all the T4 script files and their accompanying auto-generated cs files are folded up under the edmx file.  This makes the project neat and tidy looking and it keeps new programmers from poking around in source files they don’t need to mess with.  This is a technique that is commonly used in some of the best Graphical User Interfaces: Show the basic tools, hide the complex.

What I hate about EF-6

Now it’s time to get into the ugly side of EF-6.  It seems that EF-6 was designed from the ground up with no knowledge of Linq-to-SQL.  I’m sure the designer(s) behind EF have used Linq-to-SQL, but maybe they have never used an ORM in a real-world application.  Because the shortcomings that I’ve run into are obvious and right in your face.

First, there’s the problem of only one database connection per context (or edmx file).  This is a huge issue since you can’t join tables across databases.  I’ve stumbled across a couple articles that talk about hacking around this, but there should not be any hacking required.  I could possibly live without this feature and not get too up in arms if it wasn’t for the next flaw that I’m going to talk about.

The second issue, and it really ranks at the top with the first issue: EF ignores name space conventions.  This is a problem because I can’t just create a small context containing just the tables I need for a class in my project.  I would prefer to have the ability to create a sub-directory in a project containing a class cs file and an edmx file all related to each other in functionality.  This would keep the scope of the problem limited to that sub-directory.  I discovered early on that this does not work with EF.  Once I drag the same table into two different edmx files, it gets ugly.

The next problem which is made worse by the fact that I can’t get around it because of problem two involves the size of the edmx file.  As this file grows, my queries take longer at start up time. That’s because all the xml is contained in one large monolithic file that must be loaded by EF first.  This file contains all the xml definitions of the tables that were included in the edmx file, plus all the mapping information that is shown when using the visual interface.  If I were a designer working for Microsoft, I would have broken each table into it’s own xml file and kept the visual stuff in its own file.  Then EF could have loaded only the xml data that it needed for the tables being joined in the query.

The last problem I’m going to mention involves the xml file and the fact that this is computer generated.  Computer generated files are difficult to merge when using version control.  Even under TFS (Team Foundation Server) this is a mess when more than one person is modifying the edmx file and attempting to check in their changes.  Again, separation of the visual attributes from the table definitions could have reduced this problem.

Other issues with ORMs

There are other issues with EF, but these issues are found in Linq-to-SQL and I’m betting they exist in NHibernate (though I can’t speak to NHibernate much since I don’t have any real-world experience, yet).  

Probably the biggest thorn in my side about ORMs is the lack of unit test mocking ability.  My issue is that there should be an entire mocking class built right into the ORM itself, or an add-on mock class that imitates what the ORM does using in-memory tables.  EF comes close and I like a lot of the mocking capabilities of EF.  Unfortunately, EF can’t mock certain things that annoy me like crazy.  One issue involves the use of the SqlFunctions.StringConvert() method.  This method cannot be mocked.  There are some work arounds on the internet, but they’re not pretty.  Another not-mockable “feature” of ORMs is the stored procedure.  This I can understand, since the stored procedure is dependent on the database itself and not the ORM.  But wouldn’t it be cool….

Summary

So you’re probably wondering why I’m whining about all this.  I’m putting this information out there so future developers can weigh the issues “before” they start building an enterprise level application.  My project is at the point where it will be difficult and expensive to switch ORMs.  So now, I have to do a lot of research on another product and weigh the cost of “switching the ORM” with what we will gain as we move forward.  

So here are the attributes that must be achieved in order for an ORM to pass the enterprise-level acceptance test:

1. It must be fast, even with a large number of tables with complex integrity constraints.

2. It must be built on a solid foundation.  Adding a field to one table should not cause hours worth of rework because of auto-generated code problems.

3. It must play nice with source control.  All of the productivity gains I get from the ORM are worthless if my development team is spending more time trying to re-merge their changes.

4. It must be unit testable.  In today’s development environment, unit testing is required.  It’s no longer an option.  ORM developers need to design with unit test-ability in mind.

If you are building a project that contains only one database with 20 or 30 tables, then EF will do you good.  In most other circumstances, I would warn you off of Entity Framework for now.  Maybe version 7 will fix some of the issues I’ve listed.  In the back of my mind, I’m wondering how EF made it to version 6 with all these flaws (OK, Microsoft skipped over versions 2 and 3, but it’s still a lot of versions to get through without fixing these issues).

 

Unit Testing – Breaking Dependencies

In this post I’m going to talk a bit about object dependencies and how to break them so you can isolate your object under test.  In a couple of my previous posts I used a class with one method that demonstrated how to unit test using a mock or fake EF-6 database context.  One detail that I failed to mention (er, I left it out to simplify the sample…yeah, that’s what I did), is that you need to close your connection in the object when your done.  Technically the object I used in the previous examples will work just fine if you open the context and close the context outside your object.  That, however, is ugly code to deal with.  We really want our object to handle the context creation and disposal automatically.  It’s disappointing that we have to destroy all of our “usings” just to break the dependency on the context to begin with.  There’s no need to make it even uglier.

So here’s the object I defined in my last unit testing post:


public class PersonnelPerDepartment
{
    private DepartmentContext _DeptContext;

    public PersonnelPerDepartment(DepartmentContext deptContext)
    {
        _DeptContext = deptContext;
    }

    public int TotalPersonnel()
    {
        var personnelDeptQuery = (
            from in _DeptContext.departments
            join in _DeptContext.people on d.id equals p.department
            select p).ToList();

        return personnelDeptQuery.Count();
    }
}

What I want to do is only pass the context in if it’s used under testing, but make the object keep track of creating and disposing of the context on it’s own if not under test.  So here’s what I’m going to change it to:

public class PersonnelPerDepartment
{
    private DepartmentContext _DeptContext;

    public PersonnelPerDepartment()
    {
        _DeptContext = new DepartmentContext();
    }

    ~PersonnelPerDepartment()
    {
        if (_DeptContext is IDisposable)
        {
            ((IDisposable)_DeptContext).Dispose();
        }
    }

    public PersonnelPerDepartment(DepartmentContext deptContext)
    {
        _DeptContext = deptContext;
    }

    public int TotalPersonnel()
    {
        var personnelDeptQuery = (
            from d in _DeptContext.departments
            join p in _DeptContext.people on d.id equals p.department
            select p).ToList();

        return personnelDeptQuery.Count();
    }

}

If your object has a base class with a dispose method, then you’ll have to something similar to this:

    protected override void Dispose(bool disposing)
    {
        if (_DeptContextis IDisposable)
        {
            ((IDisposable)_DeptContext).Dispose();
        }
        base.Dispose(disposing);

    }

The purpose of this code is to ensure that your dbcontext has been properly disposed of when the object is disposed.  Normally your dbcontext will be disposed at the end of the “using” clause.  We can’t use the “using” clause because we need to inject a fake database context into this object when we unit test it.

Another dependency to watch out for is if the object under test calls another object that uses it’s own database context.  You’ll have to pass the database context variable to the object being called so that it can be faked or mocked under tests as well.  This technique can get ugly if you have many nested or dependent objects.  If you need to fake or mock an object being called from this object, you can use the same pattern as above.  Pass a pointer to the object under question in the constructor.  Call the method when needed and dispose of the object when the current object is disposed of.  Then you can mock the called object and pass it into this object.

Here’s a very simple example:

public class SecondObject
{
    ~SecondObject()
    {
        // dispose allocated stuff here
    }

    public void SomeMethod()
    {
    }
}

public class FirstObject
{
    private SecondObject _secondObject;

    public FirstObject()
    {
        _secondObject = new SecondObject();
    }

    public FirstObject(SecondObject secondObject)
    {
        _secondObject = secondObject;
    }

    ~FirstObject()
    {
        if (_secondObject is IDisposable)
        {
            ((IDisposable)_secondObject).Dispose();
        }
    }

    public void FirstObjectMethod()
    {
        _secondObject.SomeMethod();
    }
}

There is a good book on breaking dependencies.  It’s called “Working Effectively With Legacy Code” and I’ve read it twice.  It has a lot of good examples of difficult dependencies and how to break them for unit testing:


Another good book is “Professional Test Driven Development in C#”:


Summary

I’ve only touched on the subject of breaking dependencies in this blog post.  My intention was to introduce you to the concept and make you aware that it’s a task that needs to be done in order to effectively unit test an object.  if you can’t isolate an object for unit testing, then you can’t properly unit test it.  Eventually, you’ll get used to the pattern and you’ll start to incorporate this technique into your objects before you start unit testing.  Or, if you get really good at this, you’ll write your unit tests first, then create your objects to work under testing and finally making your object work for your real environment (i.e. Test Driven Development).  The ultimate goal is to unit test every behavior that your objects are expected to perform.  Then your unit tests become the documentation of your code and how it was designed to behave in the first place.

 

EF-6 Unit testing (Part 2)

Introduction

I’ve covered some unit testing in a previous blog post.  This time I want to expand on my earlier post and show how to mock two or more tables.  I will also demonstrate a weakness I discovered in mocking a database using the mock and a method to get around it.


Two or more Tables

You might remember this code:

using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using Moq;


namespace DatabaseTestConsole
{
    [TestClass]
    public class UnitTests
    {
        [TestMethod]
        public void TestQuery()
        {
            var data = new List<account>
            {
                new account { username = “test“,pass=”testpass1” },
                new account { username = “ZZZ“,pass=”testpass2” },
                new account { username = “AAA“,pass=”testpass3” },
            }.AsQueryable();


            var mockSet = new Mock<DbSet<account>>();
            mockSet.As<IQueryable<account>>().Setup(m => m.Provider)

                   .Returns(data.Provider);
            mockSet.As<IQueryable<account>>().Setup(m => m.Expression)

                   .Returns(data.Expression);
            mockSet.As<IQueryable<account>>().Setup(m => m.ElementType)

                   .Returns(data.ElementType);
            mockSet.As<IQueryable<account>>().Setup(m => m.GetEnumerator())

                   .Returns(data.GetEnumerator());

            var mockContext = new Mock<DatabaseContext>();
            mockContext.Setup(c => c.accounts).Returns(mockSet.Object);


            UserRights rights = new UserRights(mockContext.Object);

            Assert.AreEqual(“testpass1”, rights.LookupPassword(“test“),

                  “password for account test is incorrect“);
            Assert.AreEqual(“testpass2”, rights.LookupPassword(“ZZZ“),

                  “password for account ZZZ is incorrect“);
            Assert.AreEqual(“testpass3”, rights.LookupPassword(“AAA“),

                  “password for account AAA is incorrect“);
        }
    }
}


I used it to demonstrate how to mock a table and unit test it.  Now I’m going to add a table to the mix.  First, I’m going to add an object that uses two tables (departments and accounts).  Then I’ll show how to mock the database that it uses:

public class PersonnelPerDepartment
{
    private DepartmentContext _DeptContext;

    public PersonnelPerDepartment(DepartmentContext deptContext)
    {
        _DeptContext = deptContext;
    }

    public int TotalPersonnel()
    {
        var personnelDeptQuery = (
            from d in _DeptContext.departments
            join p in _DeptContext.people on d.id equals p.department
            select p).ToList();

        return personnelDeptQuery.Count();
    }
}

I specifically setup the object to require two tables to query from.  Then I altered the unit test code as thus:

[TestMethod]
public void TestTwoTables()
{
    var deptData = new List<department>
    {
        new department {id=1, name=”Operations“},
        new department {id=2, name=”Sales“}
    }.AsQueryable();

    var deptMockSet = new Mock<DbSet<department>>();
    deptMockSet.As<IQueryable<department>>().Setup(m => m.Provider)
        .Returns(deptData.Provider);
    deptMockSet.As<IQueryable<department>>().Setup(m => m.Expression)
        .Returns(deptData.Expression);
    deptMockSet.As<IQueryable<department>>().Setup(m => m.ElementType)
        .Returns(deptData.ElementType);
    deptMockSet.As<IQueryable<department>>().Setup(m => m.GetEnumerator())
        .Returns(deptData.GetEnumerator());

    // department table
    var persData = new List<person>
    {
        new person {id=1, first=”Joe“,last=”Smith“,department=1},
        new person {id=2, first=”Jane“, last=”Summers“,department=1},
        new person {id=2, first=”Bob“, last=”Anders“,department=1},
    }.AsQueryable();

    var personMockSet = new Mock<DbSet<person>>();
    personMockSet.As<IQueryable<person>>().Setup(m => m.Provider)
        .Returns(persData.Provider);
    personMockSet.As<IQueryable<person>>().Setup(m => m.Expression)
        .Returns(persData.Expression);
    personMockSet.As<IQueryable<person>>().Setup(m => m.ElementType)
        .Returns(persData.ElementType);
    personMockSet.As<IQueryable<person>>().Setup(m => m.GetEnumerator())
        .Returns(persData.GetEnumerator());

    var mockContext = new Mock<DepartmentContext>();
    mockContext.Setup(c => c.departments).Returns(deptMockSet.Object);
    mockContext.Setup(c => c.people).Returns(personMockSet.Object);

    PersonnelPerDepartment persDept = new PersonnelPerDepartment(mockContext.Object);
    int total = persDept.TotalPersonnel();

    Assert.AreEqual(3, total);
}
Each table will need it’s own mockSet, which I admit, is ugly.  The mockContext has two setups, one for each table (and you can add more tables to the context as needed).  Now the test runs and returns the three personnel listd in the person table.  You can experiment with different data sets and queries to test this for yourself.  I wanted to give an example of how the code would look for two tables.
I’ve been writing unit tests for a real application that I’m working on for DealerOn.  This application has dozens of tables and, as you might have guessed, this method gets lengthy.  Of course, you can setup all this stuff in an initialization method before running your tests, but there are other problems.  One problem I discovered was that you cannot add to your tables in your methods under test.  This mock object doesn’t support adding or deleting records.  For that, we need a different technique.
Using Test Doubles
This article at microsoft (Testing with your own test doubles (EF6 onwards)) demonstrates how to use test doubles with EF-6.  The advantages to using this technique is that you can add and delete from your tables and the unit testing code is more compact (most of the code is in the fake object).  
How to do it

First, create a solution with a console application project and and a test project.  Make sure you include your console app in your references inside your unit test project and include your using statement so you can unit test objects in your console application.
Next create an EF-6 edmx file in your console application and add your tables (my example uses my demo database that I’ve been using for quite a few blog posts.  This MS SQL server sample data has a department and person table in it).
Go to the link above (testing doubles) and copy the code under the header “Creating the in-memory test doubles”, then paste it into it’s own cs file in your unit testing project.  You’ll need to change the top class to match your EF-6 database:
public class TestContext : ISampleDataContext
{
    public TestContext()
    {
        this.people = new TestDbSet<person>();
        this.departments = new TestDbSet<department>();
    }

    public DbSet<person> people { get; set; }
    public DbSet<department> departments { get; set; }
    public int SaveChangesCount { get; private set; }
    public int SaveChanges()
    {
        this.SaveChangesCount++;
        return 1;
    }
}
You’ll need to add a bunch of usings to make the errors go away.  You should end up with these:
using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
using System.Linq;
using System.Linq.Expressions;
using System.Threading;
using System.Threading.Tasks;
using EF6UnitTestingUsingDoubles; //your console project
In your console project, create an interface.  It’ll look something like this:
using System.Data.Entity;

namespace EF6UnitTestingUsingDoubles
{
    public interface ISampleDataContext
    {
        DbSet<person> people { get; set; }
        DbSet<department> departments { get; set; }
        int SaveChanges();
    }
}
You’ll need to add this interface to your EF context.cs file constructor:
public partial class sampledataEntities : DbContext, ISampleDataContext
{
    public sampledataEntities()
        : base(“name=sampledataEntities“)
    {
    }
    
}
It’s best to put the “ISampleDataContext” (and the comma) in the T4 (“tt”) script file, so auto-generated code will not wipe out your changes:
<#=Accessibility.ForType(container)#> partial class <#=code.Escape(container)#> : DbContext, ISampleDataContext


Next create your object in the main console app:
using System.Linq;

namespace EF6UnitTestingUsingDoubles
{
    public class PersonnelPerDepartment
    {
        private ISampleDataContext _DeptContext;

        public PersonnelPerDepartment(ISampleDataContext deptContext)
        {
            _DeptContext = deptContext;
        }

        public int TotalPersonnel()
        {
            var personnelDeptQuery = (
                from d in _DeptContext.departments
                join p in _DeptContext.people on d.id equals p.department
                select p).ToList();

            return personnelDeptQuery.Count();
        }
    }
}

One thing to note: You will be passing the interface definition to your classes instead of the Entity Framework context object like normal.  When your program executes normally, it will use the interface just like the fake context does when performing your unit tests.

Last, but not least, your unit test:

[TestMethod]
public void TestMethod1()
{
    var context = new TestContext();

    var deptTable = new department()
    {
        id=1,
        name=”Sales
    };
    context.departments.Add(deptTable);
    context.SaveChanges();

    var personTable = new person()
    {
        id=1,
        first = “Joe“,
        last = “Smith“,
        department = 1
    };
    context.people.Add(personTable);
    personTable = new person() 
    { 
        id = 2, 
        first = “Jane“, 
        last = “Summers“, 
        department = 1 
    };
    context.people.Add(personTable);
    context.SaveChanges();

    PersonnelPerDepartment persDept = new PersonnelPerDepartment(context);
    int total = persDept.TotalPersonnel();

    Assert.AreEqual(2, total);
}
Notice how much cleaner the test method is.  The first line creates a test context, then one record is added to the department table.  Next two records are added to the person table and that leaves the remaining lines to call the method under test (persDept) and do an assert.  The fake context object and the interface should be setup to contain all the tables in your context.  You will then be able to add data to your tables inside your unit tests as needed.  Your methods under test can perform queries on all the tables in the context without an issue when under tests.
Downloadable Sample Project
You can download the entire sample project here: EF6 Unit Testing Doubles Project.  Be aware that this project does not include the database and you’ll need to alter the connection to your database and possibly manipulate the edmx file to make it work with your database.  If you have an MS SQL server setup on your PC, then you can use these two scripts to generate the tables used in this sample (after creating a database called “sampledata”):
USE [sampledata]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[person](
[id] [int] IDENTITY(1,1) NOT NULL,
[first] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[last] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[department] [int] NOT NULL,
 CONSTRAINT [PK_person] PRIMARY KEY CLUSTERED 
(
[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
USE [sampledata]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[department](
[id] [int] IDENTITY(1,1) NOT NULL,
[name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [PK_department] PRIMARY KEY CLUSTERED 
(
[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF