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/
Personally I like to use a small console application which loads all the mappings and executes a SchemaExport as below:
new SchemaExport(config).Execute(ddlScript => {
using (var writer = new StreamWriter(fileName, true))
{
writer.Write(ddlScript);
writer.Flush();
}
}, false, false);
This console app runs as a step of the build script and the DDL script file then gets picked up by WiX and is included in the MSI package (which generates the whole database at install time).
Best Answer
Try using NHibernate.ISession.CreateSQLQuery(string queryString)
Here is the documentation: Chapter 16. Native SQL