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

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.

Answer 2

I had this same problem, I found that the Execute method only seems to work properly to do the full drop and recreate if you call it like so:

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

I am not sure why the script parameter needs to be true, but setting it to true also solves the problem.

Answer 3

I too received this error message; however, it was because I had removed a class that had a many-to-one relationship with another class. As such, there was nothing telling NHibernate to drop the [now orphaned, but still referenced] table on the next test run.

I simply dropped it by hand and all was well.