Sql – How to generate “migration” DDL from NHibernate mapping files

This has been a recurring problem for me for a long time. The problem was not solved by executing drop first or using the execute method (the drop method is a shortcut method which executes the Execute method).

After looking in the NHibernate source code I found the source of the problem. NHibernate uses hashcodes to store foreign key names in the database. The problem with hashcodes however is that they change over time, clr-version and appdomain. You can't rely on hashcodes for equality. (ref: http://blogs.msdn.com/b/ericlippert/archive/2011/02/28/guidelines-and-rules-for-gethashcode.aspx) This is why NHibernate can't always remove the foreignkeys, hence cannot drop the tables.

This is a snapshot from the NHibernate sourcecode which is used to create unique foreign key names:

public string UniqueColumnString(IEnumerable iterator, string referencedEntityName)
{
    // NH Different implementation (NH-1339)
    int result = 37;
    if (referencedEntityName != null)
    {
        result ^= referencedEntityName.GetHashCode();
    }
    foreach (object o in iterator)
    {
        result ^= o.GetHashCode();
    }
    return (name.GetHashCode().ToString("X") + result.GetHashCode().ToString("X"));
}

So this problem isn't going to be solved by NHibernate, you have to do it yourself. I solved the problem by executing the following method before the schema is created. The method removes all foreign keys only from the tables that are mapped with NHibernate:

private static void DropAllForeignKeysFromDatabase()
{
  var tableNamesFromMappings = Configuration.ClassMappings.Select(x => x.Table.Name);

  var dropAllForeignKeysSql =
    @"
      DECLARE @cmd nvarchar(1000)
      DECLARE @fk_table_name nvarchar(1000)
      DECLARE @fk_name nvarchar(1000)

      DECLARE cursor_fkeys CURSOR FOR
      SELECT  OBJECT_NAME(fk.parent_object_id) AS fk_table_name,
              fk.name as fk_name
      FROM    sys.foreign_keys fk  JOIN
              sys.tables tbl ON tbl.OBJECT_ID = fk.referenced_object_id
      WHERE OBJECT_NAME(fk.parent_object_id) in ('" + String.Join("','", tableNamesFromMappings) + @"')

      OPEN cursor_fkeys
      FETCH NEXT FROM cursor_fkeys
      INTO @fk_table_name, @fk_name

      WHILE @@FETCH_STATUS=0
      BEGIN
        SET @cmd = 'ALTER TABLE [' + @fk_table_name + '] DROP CONSTRAINT [' + @fk_name + ']'
        exec dbo.sp_executesql @cmd

        FETCH NEXT FROM cursor_fkeys
        INTO @fk_table_name, @fk_name
      END
      CLOSE cursor_fkeys
      DEALLOCATE cursor_fkeys
    ;";

    using (var connection = SessionFactory.OpenSession().Connection)
    {
      var command = connection.CreateCommand();
      command.CommandText = dropAllForeignKeysSql;
      command.ExecuteNonQuery();
    }
  }

Works perfect for me. I hope someone else will be able to use it as well. This is where I grabbed the sql script to drop all foreign keys: http://mafudge.mysite.syr.edu/2010/05/07/dropping-all-the-foreign-keys-in-your-sql-server-database/

Ad 2. If I understand you correctly, you don't want to use hbm mappings, right? Have you considered using Fluent NHibernate? (http://fluentnhibernate.org/)