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.

 

SQL Server is Crazy!

If you’ve ever used SQL server for a serious project, you’re already nodding your head at the title of this blog post.  There are many aspects of SQL server that is a bit quirky.  Overall I think SQL Server is a very good but expensive product.  I have experience with Oracle as well, and Oracle has it’s own quirks and it is also an expensive product.  But I’m not here to rate products, or decide which product I think you should use.  I’m going to talk about one of the quirks of SQL Server that you might run into if you’re attempting to work with a legacy product that was built around SQL Server.

Field Names

One of the problems with using an ORM with MS SQL Server is that SQL allows characters in its field names that C# cannot handle.  For example: ‘#’ and ‘@’ are allowed as a character in a field name (‘#’ is not allowed as the first character).  Double-quotes can be in a field name (even the first character can be a quote).  Fields can start with a number (Yikes!).  Don’t believe me?  Check this out:


Now you have to wonder, what would Entity Framework do with this mess?  Let’s find out…


It looks like EF puts a “C” in front to prevent numbers from being the first character and then it turns all unacceptable symbols into underscores.  That could cause a problem if another field name existed… let’s see what happens if another field is already named the name that EF wants to to rename a field name…

  
Now, let’s see what EF does…


OK, it appears that EF will make the second occurrence of “field_one” unique by adding an number to the end of the field name.  Keeping the names unique.


Plural Table Names

I have a table in my example database named “person”.  When you create a LINQ query of this table you end up with this:

using (var db = new sampledataEntities())
{
    var query = (from p in db.people
                             select p).ToList();
}

The name “people” is the DBSet of the “person” object that represents the table “person”.  Now, what if we named the table “persons”?  It will name it “persons”.  Now, let’s create a table named “people”, just to mess up EF. 

Yikes!  It named the record object “person” and it uses “people” as the table name.  That would be confusing if you were working with the person table and your query is actually looking at the people table.  Now let’s add “person”, “persons” and “people” into EF all at once.

The first thing that happened to me is that none of the tables show in the edmx model diagram.  It did, however, create three objects for the tables:

using (var db = new sampledataEntities())
{
    var query = (from p in db.people
                             select p).ToList();

    var query2 = (from p2 in db.people1
                                select p2).ToList();

    var query3 = (from p3 in db.persons
                                select p3).ToList();
}

One of the table objects is named “people”, one is named “people1” and the last is named “persons”.  In order to figure out which object points to which table, you’ll need to open your edmx file with an xml editor (right-click on the edmx file, select “open with”, then choose “automatic editor selector (XML)”).  Then you can search for people, people1 or persons and reverse engineer which table is being accessed.


Summary

Keep this little exercise in mind when you design a database and choose field and table names.  Avoid any symbols that are no acceptable in a C# variable name.  Also, avoid using a plural name on a table name.  Your goal is to choose a table name that will translate into an object name very similar to your table name.  Otherwise, any developer that maintains your code will have to deal with an extra layer of confusion.

 

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.