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?
Best Answer
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:
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:
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/