NHibernate SchemaExport failing to drop a table .... sometimes

Question

I'm using NHibernate for the DAL of my application, and in particlular NHibernate's SchemaExport function to drop/recreate my database schema before the execution of unit tests. The issue I'm having is that when I run the unit tests and execute SchemaExport one of my tables fails to drop every second time. This would indicate to me that there is some kind of foreign key issue preventing SchemaExport dropping my table - but I can't figure it out. My schema is very simple - A person table, an Address table and a PersonAddress table to support the many-to-many relationship between the two.

public class Person
{
    public virtual int Id { get; set; }
    public virtual string Name { get; set; }
    public virtual IList<Address> Addresses {get;set;}

    public Person()
    {
        this.Addresses = new List<Address>();
    }

}

public class Address
{
    public virtual int Id { get; set; }
    public virtual string Street1 { get; set; }
    public virtual string Street2 { get; set; }
    public virtual string Postcode { get; set; }

}

and my NHibernate mapping files...

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
               assembly="MyHibernate"
               namespace="MyHibernate"
               >
<class name="Person" table="Person.Person">
    <id name="Id" column="Id">
        <generator class="native" ></generator>
    </id>
    <property name="Name" column="Name" length="50"></property>
    <bag name="Addresses" table="[Person].[PersonAddress]" lazy="false" cascade="all">
        <key column="PersonId" foreign-key="FK_Person_Person_Id"></key>
        <many-to-many class="Address" column="AddressId"></many-to-many>
    </bag>
</class>

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
               assembly="MyHibernate"
               namespace="MyHibernate"
               >
<class name="Address" table="Person.Address">
    <id name="Id" column="Id">
        <generator class="native" ></generator>
    </id>
    <property name="Street1" column="Street1" length="50"></property>
    <property name="Street2" column="Street2" length="50"></property>
    <property name="Postcode" column="Postcode" length="50"></property>
</class>

and when I run `var cfg = new Configuration(); cfg.Configure(); cfg.AddAssembly(typeof(Person).Assembly);

        new SchemaExport(cfg).Execute(false, true, false, false)

I get a SQL exception saying:

MyHibernate.Tests.GenerateSchemaFixture.Can_Generate_Schema: NHibernate.HibernateException : There is already an object named 'Person' in the database. ----> System.Data.SqlClient.SqlException : There is already an object named 'Person' in the database.

Any ideas?

Answer 1

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/

Answer 2

Found a way around this problem. I just broke out the use of SchemaExport into two calls. The first to drop an existing schema, the second to re-create it.

 var cfg = new Configuration();
 cfg.Configure();
 cfg.AddAssembly(typeof(Person).Assembly);

 SchemaExport se = new SchemaExport(cfg);
 //drop database 
 se.Drop(true, true);
 //re-create database
 se.Create(true, true);

Using the above code in the [TestFixtureSetUp] of my test class works well. I now have a clean database schema to use for integration tests.