Fluent NHibernate, HasManytoMany and Primary Key issue

Question

I think this should be straightforward, but with NHibernate simple things are always the most complicated.

I have two POCOs which reference each other through HasManyToMany:

public class Foo1
{
//other properties
public virtual IList<Foo2> Foo2s {get;set;}
}
public class Foo2
{
//other properties
public virtual IList<Foo1> Foo1s {get;set;}
}

And the mappings:

class Foo1Map : ClassMap<Foo1>
{
//other mappings
HasManyToMany(c => c.Foo2s);
}
class Foo2Map : ClassMap<Foo2>
{
//other mappings
HasManyToMany(c => c.Foo1s);
}

The join table is correctly created, it has 2 fields which are on foreign keys to the respective tables, all fine, the HUGE problem is that those 2 fields should be primary key (or at least unique) while they aren't. I tried playing around with various fluent methods chained to HasManyToMany but without any result.

How do I get the generated manytomany table to have primary key on the 2 foreign key fields without having to create a custom POCO and map it with composite key?

Thanks.

Answer 1

a unique constraint and also index

class Foo1Map : ClassMap<Foo1>
{
    //other mappings
    HasManyToMany(c => c.Foo2s)
        .ParentKeyColumns.Add("foo1_id", p => p.UniqueKey("unique_constraint1").Index("someIndex"))
        .ChildKeyColumns.Add("foo2_id", p => p.UniqueKey("unique_constraint1"));
}

Updated: or as a convention for HasMany (code like this i used in a project), not nice but working

public class IndexOneToManyColumnsConvention : IHasManyConvention
{
    public void Apply(IOneToManyCollectionInstance instance)
    {
        var mappingfield = instance.Key.Columns.First().GetType()
            .GetField("mapping", BindingFlags.Instance | BindingFlags.NonPublic);

        var columnMapping = (ColumnMapping)mappingfield.GetValue(instance.Key.Columns.First());

        if (!columnMapping.HasValue(c => c.Index))
        {
            var typename = instance.Member.DeclaringType.Name;

            columnMapping.Index = string.Format("index_{0}_{1}",
                typename.ToLower(), instance.Member.Name.ToLower());
        }
    }
}

i think there is the code in the trunk of FNH to get rid of this hack

Answer 2

I have been running into the limitations of fluent and have given up on setting up more advanced schema tweaks using it. I had some headaches setting up index's on lookup tables so just added the SQL to a post creation script.

We have database project which creates the database using NHibernate then runs a set up post creation scripts which create index's and the stored procedures we use.

I know this doesn't directly answer your question but I was struggling with this issue yesterday so thought I'd share my solution and a bit of code to give you a head start if this is the route you decide to head down.

Be interesting to see what other have done here!

private void ExecuteScripts(string dir, bool useTransaction = true, bool useMasterConnection = false)
{
    foreach (var file in SqlFiles.Where(x => x.Contains(dir)))
    {
        var resource = file.Substring((Assembly.GetExecutingAssembly().GetName().Name + ".scripts.").Length);               

        if (useTransaction)
            ExecuteSqlWithTransaction(resource, useMasterConnection);
        else
            ExecuteSqlWithoutTransaction(resource, useMasterConnection);
    }
}



 private void ExecuteSqlWithoutTransaction(string sqlFile, bool useMasterConnection = false)
        {
            var sql = string.Empty;

            var stream = Assembly.GetExecutingAssembly().GetManifestResourceStream((Assembly.GetExecutingAssembly().GetName().Name + ".scripts.") + sqlFile.Replace('\\', '.'));

            using (var reader = new StreamReader(stream))
            {
                sql = reader.ReadToEnd();
            }

            // replace the [[[dbName]]] and [[[SQLDATAFILEPATH]]] with ACTUAL DB settings we want to work with
            sql = sql.Replace("[[[DBNAME]]]", DbName);
            sql = sql.Replace("[[[SQLDATAFILEPATH]]]", SqlDataFilePath);

            var regex = new Regex("^GO", RegexOptions.IgnoreCase | RegexOptions.Multiline);
            var lines = regex.Split(sql);

            var currentConnection = (useMasterConnection) ? MasterSqlConnection : SqlConnection;

            using (var cmd = currentConnection.CreateCommand())
            {
                cmd.Connection = currentConnection;

                foreach (var line in lines)
                {
                    if (line.Length > 0)
                    {
                        cmd.CommandText = line;
                        cmd.CommandType = CommandType.Text;

                        try
                        {
                            cmd.ExecuteNonQuery();
                        }
                        catch (SqlException ex)
                        {
                            //ShowInfo("Error running script " + sqlFile + "    Error: " + ex.Message);
                            throw new Exception(ex.Message);
                        }
                    }
                }
            }

        }



ALTER TABLE [dbo].[ArticleProjectAssignments] ADD  CONSTRAINT [pk_ArticleProjectAssignments] PRIMARY KEY CLUSTERED 
(
    [ArticleId] ASC,
    [ProjectId] ASC
)
GO