NHibernate SchemaExport does not delete the table ... sometimes

Question

I use NHibernate for the DAL of my application and in the special NHibernate SchemaExport function to delete / recreate my database schema before doing unit tests. The problem I am facing is that when I run unit tests and run SchemaExport, one of my tables cannot fall out every second time. This indicates that there is some kind of foreign key problem that prevents SchemaExport from exiting my table, but I cannot figure it out. My scheme is very simple: a people table, an address table, and a PersonAddress table to support many-to-many relationships between them.

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 you run `var cfg = new Configuration (); cfg.Configure (); cfg.AddAssembly (TypeOf (Person) .assembly);

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

I get an SQL exception:

MyHibernate.Tests.GenerateSchemaFixture.Can_Generate_Schema: NHibernate.HibernateException: the database already has an object named "Man". ----> System.Data.SqlClient.SqlException: the database already has an object named "Person".

Any ideas?

Answer 1

Found a way to solve this problem. I just started using SchemaExport for two calls. The first is to delete the existing scheme, 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 [TestFixtureSetUp] of my test class works well. Now I have a clean database schema for integration testing.

Answer 2

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

After searching the NHibernate source code, I found the source of the problem. NHibernate uses hash codes to store foreign key names in the database. The problem with hash codes is that they change over time, the clr version and appdomain. You cannot rely on hash codes for equality. (ref: http://blogs.msdn.com/b/ericlippert/archive/2011/02/28/guidelines-and-rules-for-gethashcode.aspx ) This is why NHibernate cannot always delete foreign keys, so it cannot drop tables.

This is a snapshot of the NHibernate source code that 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")); } 

Therefore, this problem will not be resolved by NHibernate, you must do it yourself. I solved the problem by running the following method before creating the circuit. The method removes all foreign keys only from tables mapped to 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(); } } 

Great for me. I hope someone else can use it. This is where I grabbed the sql script to remove all foreign keys: http://mafudge.mysite.syr.edu/2010/05/07/dropping-all-the-foreign-keys-in-your-sql-server-database /

Answer 3

I also received this error message; however, this happened because I deleted the class that had a one-to-one relationship with another class. Thus, nothing was said to let NHibernate drop the table [now orphaned, but still referenced] on the next test run.

I just threw it by hand, and everything was fine.

Answer 4

I had the same problem, I found that the Execute method only works correctly to make a complete crash and recreate if you call it like this:

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

I'm not sure why the script parameter should be true, but setting it to true also solves the problem.