DotNet Core vs. NHibernate vs. Dapper Smackdown!

The Contenders

Dapper

Dapper is a hybrid ORM.  This is a great ORM for those who have a lot of ADO legacy code to convert.  Dapper uses SQL queries and parameters can be used just like ADO, but the parameters to a query can be simplified into POCOs.  Select queries in Dapper can also be translated into POCOs.  Converting legacy code can be accomplished in steps because the initial pass of conversion from ADO is to add Dapper, followed by a step to add POCOs, then to change queries into LINQ (if desired).  The speed difference in my tests show that Dapper is better than my implementation of ADO for select queries, but slower for inserts and updates.  I would expect ADO to perform the best, but there is probably a performance penalty for using the data set adapter instead of the straight sqlCommand method.

If you’re interested in Dapper you can find information here: Stack Exchange/Dapper.   Dapper has a NuGet package, which is the method I used for my sample program.

ADO

I rarely use ADO these days, with the exception of legacy code maintenance or if I need to perform some sort of bulk insert operation for a back-end system.  Most of my projects are done in Entity Framework, using the .Net Core or the .Net version.  This comparison doesn’t feel complete without including ADO, even though my smackdown series is about ORM comparisons.  So I assembled a .Net console application with some ADO objects and ran a speed test with the same data as all the ORM tests.

NHibernate

NHiberate is the .Net version of Hibernate.  This is an ORM that I used at a previous company that I worked for.  At the time it was faster than Entity Framework 6 by a large amount.  The .Net Core version of Entity Framework has fixed the performance issues of EF and it no longer makes sense to use NHibernate.  I am providing the numbers in this test just for comparison purposes.  NHibernate is still faster than ADO and Dapper for everything except the select.  Both EF-7 and NHibernate are so close in performance that I would have to conclude that they are the same.  The version of NHibernate used for this test is the latest version as of this post (version 4.1.1 with fluent 2.0.3).

Entity Framework 7 for .Net Core

I have updated the NuGet packages for .Net Core for this project and re-tested the code to make sure the performance has not changed over time.  The last time I did a smackdown with EF .Net Core I was using .Net Core version 1.0.0, now I’m using .Net Core 1.1.1.  There were not measurable changes in performance for EF .Net Core.

The Results

Here are the results side-by-side with the .ToList() method helper and without:

Test for Yourself!

First, you can download the .Net Core version by going to my GitHub account here and downloading the source.  There is a SQL script file in the source that you can run against your local MS SQL server to setup a blank database with the correct tables.  The NHibernate speed test code can also be downloaded from my GitHub account by clicking here. The ADO version is here.  Finally, the Dapper code is here.  You’ll want to open the code and change the database server name.

 

How to detect if your code was called from a unit test

I’ve done a few unit test blog posts, but this time I’m going to demonstrate how to detect if a method is running under a unit test.  This flag can be used to break dependencies in an object or run a test database instance instead of the production instance.  

The first article I stumbled across was this stack overflow article on just such a problem:

determine-if-code-is-running-as-part-of-a-unit-test

Answer #22 had my solution, using reflection to determine if one of the included DLL’s was the UnitTestFramework.


public static class UnitTestDetector
{
    public static bool IsInUnitTest()
    {
        string assemblyName = 
         “Microsoft.VisualStudio.QualityTools.UnitTestFramework“;
        return AppDomain.CurrentDomain.GetAssemblies().Any(a => 
                   a.FullName.StartsWith(assemblyName));
    }
}


So what’s the point?  The purpose of this class/method is to embed this into your context code and force your ORM to use a test database when you are running unit tests, but run your production database when the calling assembly is not a unit test assembly.  By using this code to switch your database at the context level, you no longer have to worry about breaking dependencies of your objects that will be under unit test.

Another advantage is that this can be used in multiple ORM’s (such as EF, NHibernate, etc.).  It can even be used in direct queries if you have a SqlConnection wrapper that feeds the connection string in one place.

In a future post, I’ll demonstrate how to use this class/method in Entity Framework and show how to connect EF to SQLLocalDB for unit testing purposes.

 

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…