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.

 

Entity Framework Core 1.0 Smackdown!

Summary

In this post I’m going to compare EF 7 or EF Core 1.0 with the previous version of Entity Framework.  I’m also going to throw in the scores for Fluent NHibernate just for kicks.

I’ve done a few of these head to head comparisons, mostly for my own curiosity, but also to provide myself with a chance to learn a new technology that I know I’ll be using for years to come.  Core is the beginning of a whole new paradigm with Microsoft I I’m happy with their decisions.  First, I have to commend them on their choice to include dependency injection right into their new .Net objects.  In addition to that, they have a new in-memory Entity Framework that makes it so much quicker and easier to design unit tests.  

Setup

To get started, I have the free version of Visual Studio 2015 (2015 Community).  Previous Visual Studios came with a free version, but they have always been limited to a web version or a desktop version.  Community 2015 seems to have all the features of 2013 Professional.  The Professional version of 2015 has a lot of nice features, but the Community version is a great way to get students “hooked” on Visual Studio.  

You’ll need to make sure you have all the updates for VS 2015 before you start.  You might also need to install PowerShell 3.0.  You can search Google (or Bing) for PowerShell and download the latest version for your OS.  I’m currently using Windows 7 on this machine.

To create a Core 1.0 application, your best resource is Microsoft’s on-line documentation.  I would start here, and go to the .Net Core download site.  Follow the directions from there.  I’m currently using the Tooling Preview 2.

EF 6.0 vs. EF 7.0 Core

I used my previous smackdown code to test the performance of EF 6.  I have a new machine so I know the timing will be different to start with and I needed a base-line.  Then I copied the performance object and all the POCO and context stuff I used in the EF 6 project to my new .Net Core project.  That’s when I discovered something fishy.  EF 7 was performing very bad for selects.  Inserts, updates and deletes were faster under EF7, but a basic select was 100’s of times slower.  As I dug around with SQL profiler, I discovered that there was no EF performance problem.  The problem was in the .ToList() conversion.  Not sure why this has become such a slug, but I don’t want the .ToList() to skew my performance measurements.

Another problem I discovered was my logic involving the best of 5 runs.  I had coded this incorrectly as thus:

double smallest = -1;
for (int i = 0; i < 5; i++)
{
    double result = TestUpdate();

    if (smallest == -1)
    {
        smallest = result;
    }
    else
    {
        if (result < smallest)
        {
            result = smallest;
        }
    }
}
WriteLine(“UPDATE:” + smallest);

Currently, I’m using the Resharper ultimate add-on by JetBrains.  This tool quickly identified that “result = smallest;” was unused.  That’s because it’s backwards, and should have been “smallest = result;”.  This bug doesn’t throw the result, since the results can vary by a small amount anyway, but it was not intended, so I fixed it in the old version as well as the new.  I also dragged out the old NHibernate test code and refactored to make it mimic the Core 1.0 code as close as possible and ran that to get a base-line for NHibernate.

Here are the results:

As you can see, Microsoft has done a good job of improving the performance of Entity Framework.  I’m impressed.  NHibernate still beats the overall performance, but it’s very tiny and real-world differences could go either way.

Update

I have revisited this problem and re-tested all three scenarios (EF6, Core, NHibernate) with the .ToList() method added to the select section.  One issue I ran into was a bug in my update, select and delete tests.  The first test was an insert test and I looped 5 times to get the best insert times.  That was inserting 10,000 records each time.  Therefore, the update, select and delete times were based on 50,000 records.  Not what I intended.  So I inserted code to delete the records for each loop of the insert and I also deleted and inserted 10,000 records in-between each test (in case I ever run one test without the others).  The above list was updated to reflect the times without using the .ToList() on the select statement.  The below list is with the .ToList():

This indicates that Entity Framework 6 selects and brings in the list of data faster than Core or NHibernate.  I have scrutinized the code endlessly.  If anyone can find anything that looks odd in my code, be sure to leave a message and I’ll look into it and update this post to reflect any bug fixes.

Test for yourself!

I would recommend downloading my code and trying this on your own system.  First, you can download the .Net Core solution by going to GitHub here and downloading the source.  There is a SQL script file in the source that you can run against your local MS SQL server.  The NHibernate speed test code can also be downloaded from my GitHub account by clicking here.  The Entity Framework 6 code can be found by clicking here.  You can ignore the MySQL part, I didn’t use that for this test.

 

Entity Framework 6.1.3 vs. Linq2SQL vs. NHibernate Smackdown!

Summary

I’ve done these ad-hoc ORM performance tests in the past.  In this test I’m going to re-test the latest Entity Framework (version 6.1.3) against the latest Linq-to-SQL version.  I’ll be using EF code-first this time to make sure there isn’t any extra overhead.  I’ll also be using Visual Studio 2013.

The Hardware and Software

This test was executed on an older PC with an Intel Core2 Duo CPU running at 3Ghz (E8400).  I’m using 8 Gigabytes of Ram and a solid-state hard drive.  This machine has Windows 10 (64 bit) and I’m using Visual Studio 2013 and Microsoft SQL Server 2014.

The Tests

I ran the tests 5 times each and chose the shortest time for each section (insert, update, delete and select).  I also added the NHibernate ORM just to make this a 3-way smack-down.  I performed the basic 4 CRUD operations (insert, update, select, delete) and compared results for each operation.

The Results

It appears that EF has been improved since the last time I’ve performed this test.  Entity Framework seems to be a bit faster than Linq-to-SQL this time.  However, NHibernate still outperforms both Entity Framework and Linq-to-SQL by a large margin (between 2x and almost 3x).

Here are the results:



Usual Disclaimer

I have to warn you that this experiment is unscientific.  I’m using a PC that I use for general purpose day-to-day operations.  What this means is that I have a virus checker and a backup program that can kick in at any time.  Not to mention the fact that I have automatic updates set to on.  To make this experiment more accurate, I would need to setup a stripped down PC and run comparison tests with no other programs running.  In other words, your results may vary.

So my advice is to download the source from this blog post, compile and run this test on your machine or the expected destination setup (i.e. against your company SQL server).  You can also increase the size of the data set to simulate your expected data volume.


Download the Code

You can go to my GitHub account by clicking here.  Download the zip file and unzip it to your visual studio project directory.  Don’t forget to search the code for the connection string (“My Connection String”) and replace with your own connection string.  This occurs in 3 files: SampleDataContext.cs, App.config and NHibernateSessionFactory.cs.

 

NHibernate Mapping Utility Update

Summary

For those who have followed my blog, I have a long-term project called the NHibernate Mapping Generator.  This application generates ORM table mapping C# code for the Fluent NHibernate ORM.  In this blog post I’m going to describe a few new features to this application.


ADO.Net Unit Testing

There is an ADO.Net Context that was created in the unit test helpers of this solution.  I have added a condition where this can be called form a unit test causing the connection to override and use the SQLLocalDB database.  If you use the ADODatabaseContext in your project, then you can connect to your normal database by passing a connection string.  If you create unit tests and include the start and end code for the SQLLocalDB database, then this context will use that database whenever your code is executed from the unit test assembly.  When using the context from a unit test, you will not need a connection string at all, but you might want to specify which database to default to.  In order to do that, you can pass a blank connection string and pass in the database name, or you can pass the database name in the connection string and the constructor will copy the database into the new unit test context (see ADODatabaseContext.cs source for more details).


Session Wrappers

I have added two session wrappers to the project.  To accommodate all the default methods and properties, I used partial classes with all the repeated code in the Wrappers directory.  A method for each table is created inside the other half of the partial class for each database that is generated.  This code is contained inside the database directory and Wrappers sub-directory.  This will allow you to change your code from:

var query = (from d in db.Query<Department>() select d);

To:

var query = (from d in db.Department select d);


Future Enhancements Planned

One enhancement that will be necessary is the ability to access databases that do not show up in the drop-down list.  The SQL Server Management Studio has the ability to type in the name of a SQL Server and I intend to mimic that feature.

Next, I need to store the SQL servers discovered in the registry so that the next time this utility is run, it just reads those entries.  If the user desires, they will be able to refresh their list just like Management Studio does.  This will cause the startup time to be quicker.

Next, I need to have the ability to use a user id and password instead of just integrated security.  Management Studio has a drop-down to select one or the other, I’ll probably mimic this and only show the database list when the user clicks a button.

Next, I would like to have check boxes for the ability to generate code for non-NHibernate scenarios.  That way the NHibernate mappings will not be created, but the views, stored procedures, table creation code and the constraint code will be created.  That will make this utility handy for people who want to setup unit tests for projects that use EF, LINQ-to-SQL or just plain ADO.Net.


Where to Find the Code

You can download the entire solution including the unit test helpers, sample unit tests, sample projects and the NHibernate mapping generator at my GitHub account here.

 

Fluent NHibernate Session Magic

Summary

If you’ve spent any time with NHibernate or Fluent NHibernate, you’ll know how annoying it is to use the .Query<Tablename>() method in every LINQ query.  It’s especially a problem for situations where you must convert Entity Framework or LINQ-to-SQL to NHibernate, since you have to convert every LINQ query in your code.  In this blog post, I’m going to show how to get rid of this construct.  In the future I’ll add this to the NHibernate mapping generator to allow this syntax to be used any time you auto-generate your table mappings.


NHibernate.ISession

I’m going to mention up-front that I was not the brains behind this discovery.  My colleague Samer Adra (Blog, Linked-In, Twitter, Stack Overflow) played around with this code to get it to work.  So I’m going to blog about it so everybody can start using this technique and get rid of the annoying .Query<Tablename>() syntax.

So the first that that needs to be changed is the ISession interface.  To do that, you need to default all the methods and properties that are used by the ISession object.  It starts like this:

public class SessionWrapper : ISession
{
  private readonly ISession innerSession;

  public SessionWrapper(ISession innerSession)
  {
    this.innerSession = innerSession;
  }

  public void Dispose()
  {
    innerSession.Dispose();
  }

  … more
}

And it goes on and on (download the sample code).

I setup the project with two tables in it.  These table mappings connect to tables in MS SQL Server.  If you’ve followed this blog you know how to get the mappings, if not, then you’ll need to download the NHibernate Mapping Generator from my GitHub account and generate the mappings automatically.  Then you can go into the data project and find the table mapping cs file and copy the code.  You can get the mapping generator here.

Anyway, I added these two lines of code to the bottom of the SessionWrapper object shown above:

// Table mappings follow
public IQueryable<DataLayer.Department> Department
{
  get { return innerSession.Query<DataLayer.Department>(); }
}

public IQueryable<DataLayer.Store> Store
{
  get { return innerSession.Query<DataLayer.Store>(); }
}


Next, I had to modify the context to handle the session wrapper.  That only required me to change the OpenSession method:

public static SessionWrapper OpenSession()
{
  return new SessionWrapper(SessionFactory.OpenSession());
}



Now for the final code inside the Program.cs file:

using (var db = MSSQLSessionFactory.OpenSession())
{
  var query = (from d in db.Department select d).ToList();

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

  Console.ReadKey();
}


That’s it.  Obviously, creating a method for each table inside the SessionWrapper is the most tedious aspect of this process.  That’s why I’m going to add it to the NHibernate table generator project and generate this session automatically.  Also, you’ll need to support the stateless session (IStatelessSession) if you intend to use stateless sessions in your projects.  So the same code will need to go into a stateless session wrapper as well.


Cross Database Support

One other thing to note is that the database that this table is located in is the sampledata database.  If there was a Department table in another database and we wanted to be able to access both tables in one query, then we’ll need a unique name inside the SessionWrapper object.  One solution is to create a naming convention that uses the database name with the table name.  I may use a scheme for that in the mapping generator or I might leave it to the end user to come up with a scheme of their own.  Just be aware of that possible issue.


Download the Code

You can download the code at my GitHub account here.  Don’t forget to go into the MSSQLSessionFactory.cs file and change the server instance (search for “YOURSERVERINSTANCE”).



 

Entity Framework Unit Testing with SQLLocalDB

Summary

I’ve published a few blog posts on the usage of SQLLocalDB with NHibernate.  Now I’m going to demonstrate how easy it is to use with EF.  In fact, SQLLocalDB can be used with ADO queries and LINQ-To-SQL.  If you’re dealing with legacy code and your methods use a lot of database access operations, it would be beneficial to get your code in a test harness before converting to your target ORM.


Modifying the EF Context

The first thing I’m going to do is create a helper to deal with the Entity Framework context.  If you generate a new Entity Framework database, you’ll get a context that is configured using the App.config file.  This is OK, if your application is always going to use the same database.  If you need to switch databases, and in this case, we’ll need to use a different database when under unit testing, then you’ll need to control the context parameters.  Here’s the helper class that I wrote to handle multiple data sources:


using System.Data.Entity.Core.EntityClient;
using System.Data.SqlClient;

namespace Helpers
{
    public static class EFContextHelper
    {
        public static string ConnectionString(string connectionName, string databaseName, string modelName, string userName, string password)
        {
            bool integratedSecurity = (userName == “”);

            if (UnitTestHelpers.IsInUnitTest)
            {
                connectionName = “(localdb)\” + 
                    UnitTestHelpers.InstanceName;
                integratedSecurity = true;
            }

            return new EntityConnectionStringBuilder
            {
                Metadata = “res://*/” + modelName + “.csdl|res://*/” + modelName + “.ssdl|res://*/” + modelName + “.msl“,
                Provider = “System.Data.SqlClient“,
                ProviderConnectionString = new  
                     SqlConnectionStringBuilder
                {
                    MultipleActiveResultSets = true,
                    InitialCatalog = databaseName,
                    DataSource = connectionName,
                    IntegratedSecurity =
integratedSecurity,
                    UserID = userName,
                    Password = password
                }.ConnectionString
            }.ConnectionString;
        }
    }
}


The helper class builds the connection string and replaces the one that is defined in the App.config file.  There is a check to see if the calling assembly is a unit test assembly, I will use this to override the server name and point to the SQLLocalDB that is defined in the UnitTestHelpers.InstanceName.  This will ensure that if an EF context is called inside a method of your program, it will connect to the unit test database if a unit test calls your program.

Once you have created the helpers class. you’ll need to use it inside your context code.  To do that, you’ll need to add the code into the Context.tt file (it should be named something like “Model1.Context.tt”).  This file (also known as a T4 file), is used to generate the Model1.Context.cs file.  Modify the code to look something like this:

#>
using System;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
using Helpers;
<#
if (container.FunctionImports.Any())
{
#>
using System.Data.Objects;
using System.Data.Objects.DataClasses;
using System.Linq;
<#
}
#>

<#=Accessibility.ForType(container)#> partial class <#=code.Escape(container)#> : DbContext
{
    public <#=code.Escape(container)#>()
        : base(EFContextHelper.ConnectionString(“SQLSERVERNAME”, “sampledata”, “Model1”, “”, “”))
    {

First, you’ll need to add the “using Helpers;” line, then you’ll need to add in the EFContextHelper.ConnectionString method as the base class initialization parameter.
Change the SQLSERVERNAME to match your actual MS SQL Server name.  Change sampledata to match your actual database name.  Change “Model1” to match the name of the edmx file.  Finally, you can use a user name and password if you’re not using integrated security on MS SQL Server.  In my example, I’m using integrated security. 
Once you change the tt file, save it and check the matching cs file to make sure there are no syntax errors.


Using the Mapping Generator

Currently the mapping generator is used to generate NHibernate database mappings.  If you don’t want the extra code, you can download my project and strip out all the Fluent Nhibernate code (or just leave it for now).  I have added a new section that will generate code to create tables that match the tables defined in your database.  But first you must run the generator to create the table definitions.

Go to my GitHub account and download the NHibernateMappingGenerator solution.  You can find it here.  Make sure that the NHibernateMappingGenerator project (which is a Windows Forms project) is set to the startup project.  Then run the program and select your SQL Server and database:


If your server doesn’t use integrated security (i.e. you need a user name and password to access your SQL Server), you’ll have to do some open heart surgery (sorry, I haven’t added that feature to this program yet).  Once you click the “Generate” button, this program  will generate code that can create tables, stored procedures, views and constraints in your SQLLocalDB database instance for unit testing purposes.  You should note that your test database will match your production database from the time that you generated this code.  If you make database changes to your production system, then you’ll need to regenerate the table definitions.


The Unit Test

I’ll demonstrate one simple unit test.  This test isn’t really going to be useful, except it will test the fact that you can insert data into SQLLocalDB and query the data using Entity Framework and it will not corrupt your production data.  Here’s the code:

[TestClass]
public class EntityFrameworkTests
{
  [TestCleanup]
  public void Cleanup()
  {
    UnitTestHelpers.TruncateData();
  }

  [TestMethod]
  public void TestEntityFrameworkContext()
  {
    using (var db = new sampledataEntities())
    {
      Store store = new Store
      {
        Name = “Test Store“,
      };
 
      db.Stores.Add(store);
      db.SaveChanges();

      var resultQuery = (from s in db.Stores 
                         select s).ToList();

      Assert.AreEqual(1, resultQuery.Count());
      Assert.AreEqual(“Test Store“, resultQuery[0].Name);
    }
  }
}

As you can see, using EF from a unit test is just like using it inside your application.  Just open a context, insert data, query data, done.  The magic is occurring inside the context itself.  If you put a break-point inside the EFContextHelper.ConnectionString method, you can see where the connection string is built to point to the SQLLocalDB instance instead of your production database instance (you should always test this to verify that it is functioning correctly).


Generating the Tables

One detail that is buried in this sample code that I haven’t covered yet is how the tables are generated inside the SQLLocalDB instance.  In previous blog posts I always used the Fluent NHibernate database generate feature to generate all the tables of the database in the unit test assembly initialize method.  If you open the AssemblyCommon.cs file (inside the SampleUnitTests project) and look at the ClassStartInitialize method, you’ll see where I call another UnitTestHelpers method called CreateAllTables.  Dig into this method and you’ll see ADO code that creates MS SQL tables for a specified database.  This makes the entire unit test helper project agnostic to the type of database you might want to test.  


Using This Technique for Other Database Types

The unit test helpers can currently handle EF as well as Fluent NHibernate, but technically, any ORM or direct data access can use this set of helpers.  Make sure to create a database connection helper for the database type you will use in your project (or already use in your project).  Then you can apply the same UnitTestHelpers.IsInUnitTest check to set the instance to the SQLLocalDB instance.  Finally, you can add unit tests for each method in your application, no matter which database context it is using.  

As I mentioned in the summary, it would ease your transition to another ORM, assuming that is your goal.  Your first step would be to create unit tests around your current objects and methods, using your current database technique, then convert to your target ORM and make sure the unit tests pass.


Getting the Code

You can download the entire solution at my GitHub account: https://github.com/fdecaire/NHibernateMappingGenerator.




 

Turning off Log4Net Logging for NHibernate

Short Post!

In one of my previous posts I showed how to setup Log4Net to log errors and warnings.
One of the nice features of NHibernate is that it directly interfaces with Log4Net and generates all kinds of information.  If you use Log4Net, you’ll see pages of information about what your NHibernate ORM is doing when it runs.  The problem with this information is that it’s too much raw data and rapidly turns into spam rather than helpful information.  So I’m going to demonstrate how to turn off some of that output so you can see your Errors, without all the extra information.


Sample of full error reporting from NHibernate as a default:



Right after the <root> tag you can paste this into your app.config (or web.config) file:

   <logger name="NHibernate">
<level value="ERROR" />
</logger>
<logger name="NHibernate.SQL">
<level value="ERROR" />
</logger>
 
 

This will cause NHibernate to only output errors and reduce the amount of information that goes into your log file.  If your program runs without bugs, you will not see anything in your log files.

You can also use “WARN” and “DEBUG” like this:

   <logger name="NHibernate">
<level value="WARN" />
</logger>
<logger name="NHibernate.SQL">
<level value="DEBUG" />
</logger>
 

This will cause NHibernate to output the SQL that it generates, but keep other output to warning level and above.  

Here’s an example of a log file using the above configuration:

Notice that only the SQL information was sent to the log file (unless your NHibernate queries or mappings fail).

Which  brings me to the fact that NHibernate uses two loggers.  You can independently control the level of logging for NHibernate and the logging for the SQL that NHibernate generates.  This is helpful when you want to verify your LINQ or you notice a performance issue that might be caused by NHibernate generating a bad query.

OK, so maybe this wasn’t a short post after all.  You might want to tuck this information away for future use.  It could save a lot of time when trying to debug your database application.

Sample Code

You can download the sample code here: nhibernatelog4net.zip

 

Fluent NHibernate Mapping Generator

Summary

I finally did it.  I completed my new Fluent NHibernate mapping generator.  It has several features that make it more powerful than the hacked together console application that I did earlier this year.  Included in this package are objects that use the generated code to create unit tests around SQLLocalDB.


The Mapping Generator

The mapping generator has an interface allowing you to choose which SQL server to create mappings from.  Once you select your server, a check list of databases will be shown allowing the selection to the database level.  Once one or more databases are selected, click the “Generate” button and your database mappings will be generated in the project named “NHibernateDataLayer”.  You can change the location of this directory to any directory or project file.  If you choose a directory, your mappings will be created inside the directory and you will need to manually add your cs files to a project to use them.  If you designate a project, the mapping cs files will be generated in the project directory and the project file xml will be updated so they show up in the Visual Studio project list.

The generator will create a directory with the name of the database.  Inside that directory will be four directories for tables, views, stored procedures and constraints.  The tables are used by Fluent NHibernate to become the mappings for the ORM.  The views, stored procedures and constraints are definitions that will allow your unit tests to generate these objects in a SQLLocalDB before running a unit test.  If you change your stored procedure in your database, you can re-run the generator to update the stored procedure that will be used in your unit tests.


The Unit Testing Package

This unit test package has the ability to insert data defined in an XML file or a JSON file.  To use this feature you will need to create a file like the ones in the sample package, and make sure the file is set as embedded.  Then you can use the UnitTestHelpers.ReadData() command to read all the xml data into the database.  If you specify a primary key data point, then any identity columns will be overridden while inserting this data.  If you don’t specify a key in your xml data and the column is setup as an identity column, then a new primary key will be generated.  You can leave out optional fields (i.e. nullable fields) from your xml definition and they will be ignored by the insert command in the ReadData() method.  

The UnitTestHelpers.CreateConstraint() method allows you to create a constraint that has been defined in your database between two specified tables.  This allows you to create only the constraints you’ll need for the tables that you will be running your unit tests against.  I did it this way to cut down on the number of constraints to create, otherwise you would have to pre-populate every table that had a constraint on it, instead of a sub-set of tables you wish to tests.  

The UnitTestHelpers.ClearConstraints() method will clear all constraints.  Make sure you call this at the end of your unit test, otherwise the TruncateData() method used in the cleanup method of your unit tests will fail.


What can you do with this Source Code?

Anything you want.  Extract the code you wish to add to your own projects.  Download the code and add the features you desire.  I hope to some day make this into a real application, but for now, I just want to get this posted so other people can use it.  No warranties are implied and you’ll have to use this code at your own risk.  If you find any bugs, you can drop a comment here, email me or create an issue on GitHub (you’ll need to sign up for a free GitHub account to post issues).


Where to get the Source Code

I used GitHub to check in my source code.  That will allow me to fix any bugs and add any features in the future.  You can click here to find the source.

 

Using Oracle with Fluent NHibernate

Summary

So far I’ve written a lot of posts about Fluent NHibernate using MS SQL Server.  I wanted to demonstrate how to connect to Oracle and what the differences were between SQL and Oracle.  Getting the connection to work is probably the most difficult part.  After that, the mappings are a bit different, but not complicated.


Installing Oracle

Oracle has a free version of it’s database for development purposes.  This is an excellent strategy for a company to get it’s product into smaller companies.  One of the strongest influences on which database engine is used at a company is based on what the developers are familiar with.  Once a database server has been established, it will be there for a long time (it’s very difficult and expensive to change database servers once a system goes live).  As a developer, I like to keep my options open, so I’ve experimented with many databases in my career.  I’ve also used Oracle and SQL Server on major projects, and I converted a major project from Oracle to SQL Server.

Back to the free version of Oracle that I was talking about…  If you go to Oracle’s website and create an account, you can download the windows version of Oracle XE (Express Edition).  This is an easy to use version of Oracle that you can install on your PC and experiment with.  I’m currently working with 11g Express:


Just download the x64 (I’m assuming you are on a 64 bit machine) and install it.  Once Oracle is installed you’ll have to create your own workspace and create some tables.  Go to the “Get Started” application.  It should startup a browser and show an interface like this:


Next, you’ll need to click on “Application Express”.  This will allow you to setup a new workspace.  You’ll need to use your “SYSTEM” id and password to get to the express application (which you have to set when you install the application).  You should now see this screen:

This is where you’ll setup your workspace.  You can choose a name for your “Database Username” which is not used in the connection string for this sample.  Your Application Express Username and password is important.  You’ll need these for the NHibernate connection string.  Once you have created a workspace you will have access to a lot of tools to administer your database.  

The easiest way to create your tables is to go to the SQL workshop section under Object Browser.  There is a “Create” button that can be used to create new tables.  Click that button, and select “Table”:


Now you can create one table at a time.  For this demo, you’ll need three tables: store, product and producttype.  Start with producttype and use this:


Make sure you set the primary key on each of these tables to populate from new sequence.  This is the same as setting an identity on a primary key in MS SQL:

Then add product:

Then add store:

If you’re feeling brave, you can add in the relational integrity constraints (foreign keys) while you create your tables.  Otherwise, you can dig around for the raw queries to create such constraints.




The Oracle Database Connection

You’ll need the same 3 dlls used in any of my previous NHibernate samples: FluentNHibernate.dll, Iesi.Collections.dll and NHibernate.dll.  You can get these using the NuGet package manager (or just download my sample and grab them from the 3rdPartyDLLs directory.

I created a session factory to connect to my default Oracle.Instance.  Here’s the code:

public class OracleSessionFactory
{
  private static ISessionFactory _sessionFactory;
  private static ISessionFactory SessionFactory
  {
    get
    {
      if (_sessionFactory == null)
      {
        _sessionFactory = Fluently.Configure()
        .Database(OracleClientConfiguration.Oracle10
        .ConnectionString(“DATA SOURCE=XE;USER ID=username;PASSWORD=pass;“)
        .Driver<NHibernate.Driver.OracleClientDriver>())
        .Mappings(m => m.FluentMappings.Add<ProductTypeMap>())
        .Mappings(m => m.FluentMappings.Add<ProductMap>())
        .Mappings(m => m.FluentMappings.Add<StoreMap>())
        .ExposeConfiguration(config =>
        {
          SchemaExport schemaExport = new SchemaExport(config);
        })
        .BuildSessionFactory();
      }
      return _sessionFactory;
    }
  }
  public static ISession OpenSession()
  {
      return SessionFactory.OpenSession();
  }
}


You’ll need to modify your mappings to something like this:

public class ProductType
{
  public virtual int Id { get; set; }
  public virtual string Description { get; set; }
}

public class ProductTypeMap : ClassMap<ProductType>
{
  public ProductTypeMap()
  {
    Table(“ProductType“);
    Id(u => u.Id).GeneratedBy.Sequence(“PRODUCTTYPE_SEQ“).Not.Nullable();
    Map(u => u.Description).Length(50).Nullable();
  }
}

The “Sequence” attribute is necessary to declare that the “Id” field is a sequence field.  This is similar to the SQL Server identity declaration.

If you were to build this program in a console application, set your user name and password correct and run the program, you’ll get an error like this:


This is an annoying side affect that can be solved by setting the bitness of the project itself.  Right-click on your project and select “Properties”.  Choose “Build” and change the “Platform Target” from “Any” to “x64” (if you’re using the 64 bit version of Oracle).  Now you can successfully run your application.

From this point on, everything about Fluent NHibernate is the same.  I have included a select, insert, delete and update query in my sample application so you can see all the CRUD operations against an Oracle server.


Testing the Connection Outside Visual Studio

If you’re having difficulty getting your Oracle connection to work you can use the “Data Link Properties” window to test your connection parameters.  Create a text file on your desktop and name it “conn.udl”, then double-click on that file and you’ll see this window:


Select “Oracle Provider for OLE DB” and click the “Next >>” button.  Now fill in your connection information in this window.  Oracle uses “XE” as the default for the Data Source:


Once you click the “Test Connection” button you should get confirmation of a good connection.  Otherwise, you’ll get an error.  If you can get your settings to work in this window, then you’re one step closer to making your Oracle session work.


Sample Application

You can download this sample application and run it on your PC (after you install Oracle XE).  Be sure and set your user name and password in the SessionFactory.cs file.


FluentNHibernateOracleDemo.zip



 

NHibernate 4000 Character Limit, Sigh…

Summary

NHibernate has its share of issues.  One of it’s most notorious issues is the 4000 character limit.  That limit reveals itself when you attempt to write to text or VARCHAR fields in your database that exceed that limit.  In this post, I’m going to describe some solutions to this problem.


Mappings

One issue shows up in the mappings and I’ve mentioned this before.  To get around the mapping issue for VARCHAR(MAX) fields you’ll need to add some attributes:


Map(u => u.ExtraData).CustomType(“StringClob“)
.CustomSqlType(“varchar (MAX)“).Length(Int32.MaxValue);

This sample will setup the field named “ExtraData” with a custom type of StringClob, the SQL field type of VARCHAR(MAX) and set the length to the max int value.


Stored Procedure Parameters

Another crazy place where this problem shows up is in the parameters passed to a stored procedure or a SQL call:

db.CreateSQLQuery(“MyStoredProc :myparameter“)
    .SetParameter(“myparameter“, data, NHibernate.NHibernateUtil.StringClob)
    .ExecuteUpdate();



Data is the string that exceeds 4000 characters.  The NHibernate.NHibernateUtil.StringClob allows the parameter to handle a large string.

There are other methods of dealing with the mapping issue which I have not tried, here’s one interesting example on stack overflow:

string-unlimited-still-limited-to-4000-characters