Lesser-Known NHibernate Features: Executable HQL

var records = session.CreateQuery("update Person p set p.Email = p.Email + p.Username + '@somedomain.com' where p.Email is null").ExecuteUpdate();

What happens when you need to bulk change a lot of records on the database? The unwary novice might be tempted to load data from the database into class instances, change them and then either rely on change tracking to eventually make the changes persistent or even worse, explicitly do an update on every possibly changed entity. The non-novice readers should now rolling their eyes.

It so happens that NHibernate offers a great alternative in the form of executable HQL. Basically, it is HQL for doing bulk changes: inserts, updates and deletes.

HQL Inserts have a small gotcha: they need to come from selects. Here’s an example:

var records = session.CreateQuery("insert into Account (Name, Email, Birthday) select p.Name, p.Email, p.Birthday from Person").ExecuteUpdate();

Here is an update:

var records = session.CreateQuery("update Person p set p.Email = p.Email + p.Username + '@somedomain.com' where p.Email is null").ExecuteUpdate();

Two problems arise:

  • Cannot do joins with updates;
  • Does not update version properties.

The second one is easy to solve:

var records = session.CreateQuery("update versioned Person p set p.Email = p.Email + p.Username + '@somedomain.com' where p.Email is null").ExecuteUpdate();

Noticed the versioned keyword? This tells NHibernate to do the right thing: update the version on each affected entity, of the entity is versioned.

The final one is deletes:

var records = session.CreateQuery("delete Product p where size(p.Sales) = 0").ExecuteUpdate();

The only problem with this is that it does not cascade. You need to find another solution.

A final word on this: you can, of course, specify parameters in your queries, like in the following example.

var records = session.CreateQuery("delete Product p where p.Price = :price").SetParameter("price", 0).ExecuteUpdate();

Entity Framework Pitfalls: DbConfiguration Classes Are Automatically Loaded

Entity Framework, since version 6, allows us to specify certain aspects of its configuration by code through a DbConfiguration-derived class. This configuration can be specified in one of three ways:

However, if there is a DbConfiguration-derived class in the same assembly as your DbContext, it will be automatically set as the configuration class, provided that:

  • It is public;
  • It is non-abstract;
  • It has a public parameterless constructor.

So, a DbConfiguration in the same assembly as the DbContext is automatically loaded. If there is more than one, the DbContext constructor will throw an exception.

Entity Framework Pitfalls: Using Stored Procedures for Basic Operations

Entity Framework, as of version 6, allows the usage of stored procedures for its CUD (Create, Update and Delete) operations. We just need to override the DbContext’s OnModelCreating method and call MapToStoredProcedures.

protected override void OnModelCreating(DbModelBuilder modelBuilder)




    //rest goes here




The problem is, this is all or nothing: you cannot use a stored procedure for just one of the operations, you need to use stored procedures for all operations. And, no, it is not possible (yet) to use custom SQL, just stored procedures.

Entity Framework Pitfalls: Lazy Loading and Proxy Creation

There are two properties that control the ability to lazy load navigation properties:

  • LazyLoadingEnabled: controls whether navigation properties marked as virtual will be lazy loaded, that is, loaded on demand when they are accessed;
  • ProxyCreationEnabled: Entity Framework will create dynamic proxy classes for unsealed types with virtual properties, whenever it loads an instance from the database or it is asked to create an instance (Create method).

The problem is: for the most cases, we need both properties to be true. If they are not, navigation properties are only loaded if:

  1. Eagerly loaded through the Include extension method;
  2. Explicitly loaded through the Load method (collections only).

There are cases, however, when you wouldn’t want to have proxies:

  • When exposing entities through WCF, because since it knows nothing about the dynamic generated proxy types;
  • When using, for example, AutoMapper, to convert from one type to another, for the same reason.

The one reason why you would want a proxy without lazy loading is for self-tracking changes, that is, the proxy detects changes made to it instead of having the DbChangeTracker class (the DbContext.ChangeTracker property) doing it.

Sessão Netponto: Entity Framework 7

A minha apresentação sobre Entity Framework 7 feita na 6ª reunião presencial da Netponto em Coimbra está disponível aqui.

Os tópicos cobertos foram:

  • Novidades do Entity Framework 7
  • Entity Framework 7 vs NHibernate

Brevemente o video e a apresentação estarão disponíveis no site da Netponto.

Obrigado a todos os que estiveram presentes! Winking smile

Entity Framework Pitfalls: Table Valued Functions

As of Entity Framework 6.x, Table Valued Functions are not supported, which is really a pity.

For example, the following does not work:

CREATE FUNCTION [dbo].[GetCustomers]()



    Id INT,

    Email NVARCHAR(50),

    Phone NVARCHAR(50),

    Name NVARCHAR(50)




    INSERT @ReturnTable

    SELECT Id, Email, Phone, Name

    FROM dbo.Customer




//throws an exception complaining about TVF

var customers1 = ctx.Database.SqlQuery(typeof(Customer), "[dbo].[GetCustomers]");


var customers2 = ctx.Database.Customers.SqlQuery("[dbo].[GetCustomers]");

However, you can do this:

var customers3 = ctx.Database.SqlQuery(typeof(Customer), "SELECT * FROM [dbo].[GetCustomers]()");

And it works!

NHibernate Pitfalls: Sharing a Session in Multiple Threads

The NHibernate ISession is not thread-safe, that is, it shouldn’t be shared by multiple threads. The ISessionFactory, however, is thread-safe, and thus it should be used to create ISession instances as needed. Do not worry, creating sessions does not mean that connections to the database are immediately open and kept alive, the ISession implementation only opens database connections when they are necessary and closes them when they cease to be.

Lesser-Known NHibernate Features: Statistics

NHibernate makes available a number of statistics about its work; this includes, among others:

  • All of the queries executed;
  • Number of entities loaded, inserted, updated and deleted;
  • Number of optimistic concurrency misses;
  • Number of second level cache hits and misses;
  • Number of transactions started and committed;
  • Number of connections opened and closed;
  • etc.

This is available per session factory:

var statistics = sessionFactory.Statistics;

And all of these settings can be filtered per entity:

var entityStatistics = sessionFactory.Statistics.GetEntityStatistics("MyEntity");

Statistics can help us diagnose, for example, second level or query cache issues.

If we have no need for them, we can disable them before building the session factory:

cfg.SetProperty(NHibernate.Cfg.Environment.GenerateStatistics, Boolean.FalseString);

Or at runtime:

sessionFactory.Statistics.IsStatisticsEnabled = false;

An advice: do switch it off while on production, it does have an effect on performance.

Lesser-Known NHibernate Features: Dynamic Components

NHibernate, unlike others, has lots of ways to map columns from the database to and from classes. Normally, there is a 1:1 relation between columns and properties, but it doesn’t have to be so: there are, for example, components and dynamic components.

This time we will be looking at dynamic components. Dynamic components allow the usage of a dictionary, where one or several columns from the database will be stored, each in its own entry. This is pretty cool, if we need to add more columns at some point, and we do not want, or can, change the class!

Show me the code, I hear you say:

public class DataStore


    public DataStore()


        this.Data = new Dictionary<String, Object>();



    public virtual int Id { get; set; }


    public virtual IDictionary Data { get; set; }


Yes, it will be possible to use generic dictionaries (IDictionary<TKey, TValue>), when pull request for NH-3670 is merged, which should happen soon (NHibernate 4.1).

Now, the mappings:

var mapper = new ConventionModelMapper();

mapper.Class<DataStore>(x =>


        x.Id(y => y.Id, y => { });

        x.Component(y => y.Data, new


            A = 0,

            B = ""

        }, y =>


            y.Property(z => z.A);

            y.Property(z => z.B);



I am setting the template for whatever will be stored in the Data dictionary, in this case, an integer column (A) and a string one (B) as an anonymous object. In NHibernate 4.1, it will be possible to use a dictionary instead (see NH-3704), which will help in making it more dynamic. In the third parameter to Component, we can change the mapping, for example, the physical properties of each column; we can even use ManyToOne instead of Property, so that a particular entry in the dictionary will point to another entity!

A sample usage:

session.Save(new DataStore { Data = new Dictionary<String, Object> { { "A", 1 }, { "B", "two" } } });

Entity Framework Multitenancy Part 2 – Conventions

In my last post, I talked about different scenarios for achieving multitenancy with Entity Framework contexts. This time, I am going to show how to use conventions, wrapping the code I provided then.

First, a base convention class to serve as our root hierarchy of multitenant conventions:

public abstract class MultitenantConvention : IConvention



Only worthy of mention is the implementation of IConvention. This is a marker interface for letting Entity Framework know that this is a convention.

Next, a convention for the separate databases approach:

public class SeparateDatabasesConvention : MultitenantConvention


    public SeparateDatabasesConvention(DbContext ctx)


        var currentTenantId = TenantConfiguration.GetCurrentTenant();

        ctx.Database.Connection.ConnectionString = ConfigurationManager.ConnectionStrings[currentTenantId].ConnectionString;



This convention needs a reference to the DbContext, because it needs to change the connection string dynamically, something that you can’t do through the basic convention interfaces and classes – there’s no way to get to the context.

Now, shared database, different schemas. This time, we need to implement IStoreModelConvention<T>, using EntitySet as the generic parameter, so as to gain access to the Schema property:

public class SharedDatabaseSeparateSchemaConvention : MultitenantConvention, IStoreModelConvention<EntitySet>


    public void Apply(EntitySet item, DbModel model)


        var currentTenantId = TenantConfiguration.GetCurrentTenant();

        item.Schema = currentTenantId;



Finally, shared database, shared schema:

public class SharedDatabaseSharedSchemaConvention : MultitenantConvention


    public String DiscriminatorColumnName { get; private set; }

    private void Map<T>(EntityMappingConfiguration<T> cfg) where T : class


        var currentTenantId = TenantConfiguration.GetCurrentTenant();



    public SharedDatabaseSharedSchemaConvention(DbModelBuilder modelBuilder, String discriminatorColumnName = "Tenant")


        this.DiscriminatorColumnName = discriminatorColumnName;

        var modelConfiguration = modelBuilder.GetType().GetProperty("ModelConfiguration", BindingFlags.NonPublic | BindingFlags.Instance).GetValue(modelBuilder, null);

        var entities = modelConfiguration.GetType().GetProperty("Entities", BindingFlags.NonPublic | BindingFlags.Instance).GetValue(modelConfiguration, null) as IEnumerable<Type>;

        foreach (var entity in entities)


            var entityTypeConfiguration = modelBuilder.GetType().GetMethod("Entity").MakeGenericMethod(entity).Invoke(modelBuilder, null);

            var mapMethod = entityTypeConfiguration.GetType().GetMethods().First(m => m.Name == "Map");

            var localMethod = this.GetType().GetMethod("Map", BindingFlags.Instance | BindingFlags.NonPublic).MakeGenericMethod(entity);

            var delegateType = typeof(Action<>).MakeGenericType(localMethod.GetParameters().First().ParameterType);

            var del = Delegate.CreateDelegate(delegateType, this, localMethod);

            mapMethod.Invoke(entityTypeConfiguration, new Object[] { del });




And a nice way to wrap all this using extension methods:

public static class DbModelBuilderExtensions


    public static DbModelBuilder UseSeparateDatabases(this DbModelBuilder modelBuilder, DbContext ctx)



        modelBuilder.Conventions.Add(new SeparateDatabasesConvention(ctx));

        return modelBuilder;


    public static DbModelBuilder UseSharedDatabaseSeparateSchema(this DbModelBuilder modelBuilder)



        modelBuilder.Conventions.Add(new SharedDatabaseSeparateSchemaConvention());

        return modelBuilder;


    public static DbModelBuilder UseSharedDatabaseSharedSchema(this DbModelBuilder modelBuilder, String discriminatorColumnName = "Tenant")



        modelBuilder.Conventions.Add(new SharedDatabaseSharedSchemaConvention(modelBuilder, discriminatorColumnName));

        return modelBuilder;



Usage: just uncomment one of the Use calls.

protected override void OnModelCreating(DbModelBuilder modelBuilder)


    //uncomment one of the following lines



    //modelBuilder.UseSharedDatabaseSharedSchema(discriminatorColumnName: "Tenant");