David Shifflet's Blog

.NET Core, Oracle and NHibernate

March 26, 2018 by David Shifflet -

Introduction

So NHibernate has been released with .NET Core support! NHibernate Announcement

Which means in theory we can use that as an ORM and combined with the .NET Core support from Oracle.ManagedDataAccess we can use it with Oracle Databases!

So in this example, we will use the previous Linq2Db examples and move those over to NHibernate! We will still maintain the Sqlite tests we had before.

This example references and builds off stuff contained in a previous example. When I am referring to Linq2Db I am referring to code in that example.

Let's Change Northwind

So for our Northwind domain when using NHibernate we are going to want three basic sections:

Models: Our .cs Models
Mappings: How we map our models to the database
A Factory: This will let us create the NHibernate Session Factory that will let us connect to our database.

And it's going to look like:

So using Sql Wrangler we are going to execute select * from northwind.products and generate some C# by clicking

. We are going to create a Product model in the Models folder, and a ProductMapping in the Mappings folder. The content for these comes from the output of clicking

in SqlWrangler. It will look like:

Models\Product.cs (Edit the name from Products to Product!)

namespace Northwind.Models
{
    public class Product
    {
        //PRODUCTS
        public int Id { get; set; } //PRODUCT_ID
        public string ProductName { get; set; } //PRODUCT_NAME
        public int SupplierId { get; set; } //SUPPLIER_ID
        public int CategoryId { get; set; } //CATEGORY_ID
        public string QuantityPerUnit { get; set; } //QUANTITY_PER_UNIT
        public double UnitPrice { get; set; } //UNIT_PRICE
        public int UnitsInStock { get; set; } //UNITS_IN_STOCK
        public int UnitsOnOrder { get; set; } //UNITS_ON_ORDER
        public int ReorderLevel { get; set; } //REORDER_LEVEL
        public string Discontinued { get; set; } //DISCONTINUED
    }
}

And then...

Mappings\ProductMapping.cs (Again edit the name from Products to Product, and add the schema NORTHWIND.)

using NHibernate.Mapping.ByCode.Conformist;
using Northwind.Models;

namespace Northwind.Mappings
{

    //NHIBERNATE MAPPING
    internal class ProductsMapping : ClassMapping<Product>
    {
        public ProductsMapping()
        {
            Schema("NORTHWIND");
            Table("PRODUCTS");
            Lazy(false);
            Id(prop => prop.Id, map =>
            {
                map.Column("PRODUCT_ID");
                //map.Generator(Generators.Sequence, gmap => gmap.Params(new {sequence = "PRODUCT_ID_SEQ"}));
            });
            Property(prop => prop.ProductName, map => map.Column("PRODUCT_NAME"));
            Property(prop => prop.SupplierId, map => map.Column("SUPPLIER_ID"));
            Property(prop => prop.CategoryId, map => map.Column("CATEGORY_ID"));
            Property(prop => prop.QuantityPerUnit, map => map.Column("QUANTITY_PER_UNIT"));
            Property(prop => prop.UnitPrice, map => map.Column("UNIT_PRICE"));
            Property(prop => prop.UnitsInStock, map => map.Column("UNITS_IN_STOCK"));
            Property(prop => prop.UnitsOnOrder, map => map.Column("UNITS_ON_ORDER"));
            Property(prop => prop.ReorderLevel, map => map.Column("REORDER_LEVEL"));
            Property(prop => prop.Discontinued, map => map.Column("DISCONTINUED"));
        }
    }
}

Finally for the Northwind project we need a static Factory to create the NHibernate SessionFactory! Thus a file called NhFactory.cs:

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;
using NHibernate;
using NHibernate.Cache;
using NHibernate.Cfg;
using NHibernate.Cfg.MappingSchema;
using NHibernate.Connection;
using NHibernate.Dialect;
using NHibernate.Driver;
using NHibernate.Mapping.ByCode;

namespace Northwind
{
    public static class NhFactory
    {
        public static HbmMapping[] HbmMappings { get; private set; }

        public static ISessionFactory CreateNhSessionFactory<TDialect, TDriver>(string connectionString, Assembly[] assemblies = null, bool showSql = true) 
            where TDialect : Dialect
            where TDriver : IDriver
        {
            if (assemblies == null)
            {
                assemblies = new[] { Assembly.GetExecutingAssembly() };
            }

            var nhConfiguration = new Configuration();
            nhConfiguration.Cache(properties => properties.Provider<HashtableCacheProvider>());

            nhConfiguration.DataBaseIntegration(dbi =>
            {
                dbi.Dialect<TDialect>();
                dbi.Driver<TDriver>();
                dbi.ConnectionProvider<DriverConnectionProvider>();
                dbi.IsolationLevel = IsolationLevel.ReadCommitted;
                dbi.ConnectionString = connectionString;
                dbi.Timeout = 60;
                dbi.LogFormattedSql = true;
                dbi.LogSqlInConsole = false;
            });
            if (showSql)
            {
                nhConfiguration.Properties["show_sql"] = "true";
            }


            if (HbmMappings == null || !HbmMappings.Any())
            {
                RegisterMappings(assemblies);
            }
            if (HbmMappings != null)
            {
                HbmMappings.ToList().ForEach(nhConfiguration.AddMapping);
                var assembly = Assembly.GetExecutingAssembly();
                nhConfiguration.AddAssembly(assembly);
                var sessionFactory = nhConfiguration.BuildSessionFactory();
                return sessionFactory;
            }
            throw new HibernateConfigException("Unable to find any mappings for NHibernate.");
        }

        public static void RegisterMappings(Assembly[] assemblies)
        {
            //Register things that are classes ending with Mapper and in the Mappings namespace
            var mapper = new ModelMapper();
            
            foreach (Type t in GetMappings(assemblies))
            {
                var method = typeof(ModelMapper).GetMethods().FirstOrDefault(
                        o => o.Name.Equals("AddMapping") &&
                        o.GetParameters().Length == 0 &&
                        o.ContainsGenericParameters
                );

                if (method == null)
                {
                    throw new InvalidOperationException("Could not retrieve AddMapping method");
                }

                method
                    .MakeGenericMethod(t).Invoke(mapper, null);
            }

            var mappings = new List<ModelMapper>
                           {
                               mapper
                           };
            var hibernateMappings = mappings.Select(map =>
            {
                var hbm = map.CompileMappingForAllExplicitlyAddedEntities();
                hbm.autoimport = false;
                return hbm;
            }).ToArray();
            HbmMappings = hibernateMappings;
        }

        public static IEnumerable<Type> GetMappings(IEnumerable<Assembly> assemblies)
        {
            var result = new List<Type>();
            foreach (var assembly in assemblies)
            {
                result.AddRange(assembly.GetTypes()
                    .Where(o => o.Name.EndsWith("Mapping") && o.Namespace != null &&
                                o.Namespace.EndsWith("Mappings"))
                    .ToArray());
            }
            return result;
        }
    }
}

There are some things I want to point out here:

We don't want dependencies on a specific Database Implementation. That is why we are using Generics when we construct the SessionFactory with CreateNhSessionFactory(string connectionString, Assembly[] assemblies = null, bool showSql = true)
We aren't hard coding what mappings we are using. We are using reflection and discovering them, those that inherit the ClassMapping and are in a namespace called *Mappings. This is what RegisterMappings(...) and GetMappings(...) is doing for us.
This factory is very generic, you can reuse it over and over again in future projects. Remember no dependencies on the Database Implementation.

In this example we have the models and mappings in the same project. It makes more sense for actual real life code to separate them. Have a Northwind.Models and a Northwind.DataAccess. That way you don't need to change your models if you decide to use something else for data access. Another benefit is if someone doesn't care about data access and only wants to use your models, less dependencies. Think... Separation of Concerns.

Let's Make the Console App Work Again

Our console application which displays a list of products will need to be changed. Those changes are going to look like this in Program.cs: (I added extra comments to explain what is happening)

using System;
using System.IO;
using System.Linq;
using Microsoft.Extensions.Configuration;
using NHibernate.Dialect;
using NHibernate.Driver;
using Northwind;
using Northwind.Models;

namespace OdpNetCoreExample
{
    class Program
    {
        public static IConfiguration Configuration { get; set; }

        static void Main()
        {
            var builder = new ConfigurationBuilder()
                .SetBasePath(Directory.GetCurrentDirectory())
                .AddJsonFile("appSettings.json");

            Configuration = builder.Build();

            GetData();
        }

        static void GetData()
        {
//Let's load a SessionFactory.  This includes the schemas and mappings.  And the database connection.
//Notice how we are passing in the dialect and driver?  That means Northwind is Agnostic per
//the Database engine.
            using (var sessionFactory =
                NhFactory.CreateNhSessionFactory(Configuration.GetConnectionString("NorthwindDb")))
            {
//Now let's open the connection to the database and do some things.
//Also we will create a transaction.  Transactions are important, even though this is readonly
//what if someone else comes along and changes it?  Wrap stuff in transactions!
                using (var session = sessionFactory.OpenSession())
                using (var txn = session.BeginTransaction())
                {
//Our Linq2Db code has changed from
//var query = db.Product.Where(o => o.Id > 25).OrderByDescending(o=>o.ProductName)  
//.ToList();
//to...
                    var query = session.Query<Product>().Where(o => o.Id > 25).OrderByDescending(o => o.ProductName)
                        .ToList();
//So it's very similar.
                    foreach (var item in query.ToList())
                    {
                        Console.WriteLine(item.ProductName);
                    }
//Finally we commit.  We don't need to commit cause at this point it's read only.  If we didn't commit the transaction would roll back.
                    txn.Commit();
                }
            }
        }
    }
}

And that is pretty much it for the Console application.

Fixing the Tests

Our tests will get a bit simpler now. A plus is NHibernate respects our schema naming with Sqlite aka NORTHWIND.PRODUCTS (from Oracle) is NORTHWIND_PRODUCTS (in Sqlite).

All we are going to need to do, is the following:

Change our NorthwindDbFixture to talk to NHibernate
Modify our tests to use the new session

So some files like NorthwindSetup.cs and SqliteDbSettings.cs will go away!

NorthwindDbFixture.cs

Let's look at the NorthwindDbFixture.cs:

using System;
using System.IO;
using NHibernate;
using NHibernate.Dialect;

namespace Northwind.Tests
{
    public class NorthwindDbFixture : IDisposable
    {
        public ISessionFactory SessionFactory { get; }
        private readonly string[] _buildCommands;

        public NorthwindDbFixture()
        {
            //DataSource=:memory: makes Sqlite use in memory     
            SessionFactory = NhFactory.CreateNhSessionFactory<SQLiteDialect, MicrosoftDataSqliteDriver> ("DataSource=:memory:");

            using (var sr = new StreamReader("build_northwind.sql"))
            {
                var s = sr.ReadToEnd();
                _buildCommands = s.Split(';');
            }
        }

        public ISession OpenSession()
        {
            var session = SessionFactory.OpenSession();
            foreach (var sql in _buildCommands)
            {
                using (var cmd = session.Connection.CreateCommand())
                {
                    cmd.CommandText = sql;
                    cmd.ExecuteNonQuery();
                }
            }
            return session;
        }

        public void Dispose()
        {
        }
    }
}

So what is happening here?

The constructor NorthwindDbFixture() is setting up our session factory so we can pass that down to our tests. It's also reading and splitting our build script (DDL) into the _buildCommands array so we can execute it later.
The method OpenSession() is opening our connection and building our DBOs and inserting the test data for us from the SQL in the build script. NHibernate can kind of execute raw sql, but if you need to do something interesting keep in mind you can get an IDbConnection from the session.Connection. In fact if you need to do something super crazy you can actually cast it and use it that way like var oracleCn = (OracleConnection) session.Connection this is one way to side step Nhibernate when needed. (e.g. OracleBulkCopy operations)

MicrosoftDataSqliteDriver.cs

So NHibernate doesn't have a driver included in the nuget package for the Microsoft.Data.Sqlite.

We need to create one! Which is super easy. I took the code from NHibernate.Driver.CsharpSqliteDriver and changed it for Microsoft.Data.Sqlite.

MicrosoftDataSqliteDriver.cs

using NHibernate;
using NHibernate.Driver;

namespace Northwind.Tests
{
    /// 
    /// NHibernate driver for the Community Microsoft.Data.Sqlite data provider.
    /// 
    public class MicrosoftDataSqliteDriver : ReflectionBasedDriver
    {
        /// 
        /// Initializes a new instance of <see cref="Microsoft.Data.Sqlite"/>.
        /// 
        /// <exception cref="HibernateException">
        /// Thrown when the Microsoft.Data.Sqlite assembly can not be loaded.
        /// </exception>
        public MicrosoftDataSqliteDriver()
            : base(
                "Microsoft.Data.Sqlite",
                "Microsoft.Data.Sqlite.SqliteConnection",
                "Microsoft.Data.Sqlite.SqliteCommand")
        {
        }
		
        public override bool UseNamedPrefixInSql => true;
        public override bool UseNamedPrefixInParameter => true;
        public override string NamedPrefix => "@";
        public override bool SupportsMultipleOpenReaders => false;
    }
}

We are getting closer...

Finally the Tests

using System.Linq;
using Northwind.Models;
using Xunit;
using Xunit.Abstractions;

namespace Northwind.Tests
{
    public class BasicTests : IClassFixture<NorthwindDbFixture>
    {
        //this is to display some output about the counts
        private readonly ITestOutputHelper _output;
        private readonly NorthwindDbFixture _fixture;

        public BasicTests(ITestOutputHelper output, NorthwindDbFixture fixture)
        {
            _output = output;
            _fixture = fixture;
        }

        [Fact]
        public void CanGetProductsViaLinq()
        {
            using(var session = _fixture.OpenSession())
            {
                var query = session.Query<Product>().Where(o => o.Id > 25).OrderByDescending(o => o.ProductName)
                    .ToList();
                _output.WriteLine("Products: {0}", query.Count);
                Assert.True(query.Any());
            }
        }

        [Fact]
        public void CanGetCheapProductsViaLinq()
        {
            using (var session = _fixture.OpenSession())
            {
                var query = session.Query<Product>().Where(o => o.UnitPrice < 20).OrderByDescending(o => o.ProductName)
                    .ToList();
                Assert.True(query.Any());
            }
        }
    }
}

So not a lot of changes here. We are basically using the fixture directly instead of the Setup like in the Linq2Db example.

Our basic querying has changed from db.Product.Where(o => o.Id > 25) in Linq2Db to session.Query<Product>().Where(o => o.Id > 25) with NHibernate. The contents of the where remains the same.

I am not using transactions here, because this is read only. But maybe I should be?

Finally

At this point everything should build. If you run the console app you should see the products. If you run the tests those should pass.

So with the new .Net Core support provided by Oracle.ManagedDataAccess and the new NHibernate with .Net Core support you can now create platform portable solutions that can use an ORM and work with the Oracle Database. You should also be able to create tests using an in memory Sqlite Database to test your stuff.

Example Code Here

Blog

Books I Have Read

Store

Archives

Elsewhere

David Shifflet's Snippets

.NET Core, Oracle and NHibernate

Introduction

Let's Change Northwind

Let's Make the Console App Work Again

Fixing the Tests

NorthwindDbFixture.cs

MicrosoftDataSqliteDriver.cs

Finally the Tests

Finally

About