Many-to-Many fluent NHibernate mapping with SQL Azure

Question

Since SQL Azure requires clustered indexes for every table for replication (see here http://blogs.msdn.com/b/sqlazure/archive/2010/05/12/10011257.aspx) I have added the following MsSqlAzureDialect to my MsSqlConfiguration:

public class MsSqlAzureDialect : MsSql2008Dialect
{
    public override string PrimaryKeyString
    {
        get { return "primary key CLUSTERED"; }
    }
}

However, that doesn't solve the problem I'm currently having with a Many-to-Many table. I currently have a situation where I have a User with Roles a Role with Users. So there is a Many-to-Many relationship between Users and Roles and a link table generated by NHibernate. So in my UserAutomappingOverride I have something like this:

public class UserAutomappingOverride : IAutoMappingOverride<User>
{
    public void Override(AutoMapping<User> mapping)
    {
        mapping.HasManyToMany(x => x.Roles).Cascade.SaveUpdate();
    }
}

This results in NHibernate creating a linking table called RoleToUser. However, this table doesn't have a PK and therefore no Clustered Index. Which is an invalid table configuration for use in SQL Azure.

I've tried to use Database Objects from non-fluent NHibernate like this:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<nhibernate-mapping>
    <database-object>
        <create>create clustered index ix on RoleToUser(User_id, Role_id)</create>
        <drop>drop index RoleToUser.ix</drop>
    </database-object>
</nhibernate-mapping>

But that was a shot in the dark to try and combine that non-fluent NHibernate code in a fluent configuration. It didn't work however, because of an error that said "For security reasons DTD is prohibited in this XML document..."

Note: I'm using fluent NHibernate for code first database creation. So I am actually using my entities and AutomappingOverrides to generate the schema of the database on deployment.

Any help would be much appreciated.

Answer 1

So @Derek Greer's answer was great. However, I didn't want the mess of having too much of a hybrid nhibernate and fluent nhibernate environment.

So what I ended up doing, since these are all little hacks to add the clustered indexes to link tables for SQL Azure anyway, was to add an hbm file dedicated to adding clustered indexes. This way, whenever a many to many relationship is added to an object in the automapping fluently, a developer can manually add a line to the file as seen below. This is actually really close to an answer I found earlier, but I just didn't know enough about the non-fluent nhibernate to know what I was doing wrong with getting the hbm file to load. So, here it is:

ManyToManyClusteredIndexes.hbm.xml

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
namespace="Bookstore.Data.HbmMappings"
assembly="Bookstore.Data.HbmMappings">
    <database-object>
        <create>create clustered index ix on RoleToUser(User_id, Role_id)</create>
        <drop>drop index RoleToUser.ix</drop>
    </database-object>
    <database-object>
        <create>create clustered index ix on RoleToRoleGroup(RoleGroup_id, Role_id)</create>
        <drop>drop index RoleToRoleGroup.ix</drop>
    </database-object>
</hibernate-mapping>

Answer 2

I was researching long time now about this subject, and since i didnt want to change fluent nhibernate, and nhibernate code, i decided to make this workaround:

I already had a console app which was initializing the db environment (locally or azure), and after the fluent nhibernate db creation was over i just dropped the ManyToMany tables, and recreated them as azure expects them to be:

drop table ProfileLikes
CREATE TABLE ProfileLikes(
    [ProfileID] [bigint] NOT NULL,
    [LikeID] [bigint] NOT NULL,
    PRIMARY KEY CLUSTERED 
(
    [ProfileID],[LikeID]  ASC
)
) 

ALTER TABLE ProfileLikes  WITH CHECK ADD  CONSTRAINT [FK6FB4BC1C85106EB3] FOREIGN KEY([LikeID])
REFERENCES [dbo].[Likes] ([Id])
ALTER TABLE ProfileLikes CHECK CONSTRAINT [FK6FB4BC1C85106EB3]

ALTER TABLE ProfileLikes  WITH CHECK ADD  CONSTRAINT [FK6FB4BC1CA12A6EC5] FOREIGN KEY([ProfileID])
REFERENCES Profiles ([Id])

ALTER TABLE ProfileLikes CHECK CONSTRAINT [FK6FB4BC1CA12A6EC5]

Hope it helps. Amir

Answer 3

To have the mapping tables generated with a primary key, you can use and IBag. Based upon my research, Fluent NHibernate doesn't support this yet, but you can use Fluent NHibernate to map hbm files along with your class mappings and/or auto-mappings in order to get to features not directly supported through Fluent NHibernate.

Here's a quick example that maps Users to Roles:

Program.cs

class Program
{
    static void Main(string[] args)
    {
        DatabaseContextFactory.CreateSchema();

        var user = new User();
        user.Roles = new List<Role>();
        var role = new Role();
        user.Roles.Add(role);

        ISessionFactory sessionFactory = DatabaseContextFactory.CreateSessionFactory();

        using (ISession session = sessionFactory.OpenSession())
        {
            using (ITransaction transaction = session.BeginTransaction())
            {
                session.SaveOrUpdate(role);
                session.SaveOrUpdate(user);
                transaction.Commit();
            }
        }
    }
}

DatabaseContextFactory.cs

public static class DatabaseContextFactory
{
    static ISessionFactory _sessionFactory;
    static Configuration _configuration;

    public static ISessionFactory CreateSessionFactory()
    {
        if (_sessionFactory == null)
        {
            _sessionFactory = Fluently.Configure()
                .Database(MsSqlConfiguration.MsSql2008.ConnectionString(cs => cs.FromConnectionStringWithKey("SurrogateExample")))
                .ExposeConfiguration(c => _configuration = c)
                .Mappings(cfg => cfg.HbmMappings.AddFromAssemblyOf<Program>())
                .BuildSessionFactory();
        }

        return _sessionFactory;
    }

    public static void CreateSchema()
    {
        CreateSessionFactory();
        new SchemaExport(_configuration).Execute(false, true, false);
    }
}

User.hbm.xml

<?xml version="1.0" encoding="utf-8"?>

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
                                     assembly="SurrogateManyToManyExample"
                   namespace="SurrogateManyToManyExample.Entities">
    <class name="User" table="[User]">
        <id name="Id">
            <generator class="guid.comb" />
        </id>

        <idbag name="Roles" table="UserInRole" lazy="true">
            <collection-id column="Id" type="Guid">
                <generator class="guid.comb" />
            </collection-id>
            <key column="UserId" />
            <many-to-many column="RoleId" class="Role" fetch="join" />
        </idbag>
    </class>
</hibernate-mapping>

Role.hbm.xml

<?xml version="1.0" encoding="utf-8"?>

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
                                     assembly="SurrogateManyToManyExample"
                   namespace="SurrogateManyToManyExample.Entities">
    <class name="Role" table="[Role]">
        <id name="Id">
            <generator class="guid.comb" />
        </id>       
    </class>
</hibernate-mapping>

Answer 4

I have a structure like this which works in Azure

CREATE TABLE [dbo].[ClientLabel](
    [ClientId] [bigint] NOT NULL,
    [LabelId] [bigint] NOT NULL,
 CONSTRAINT [PK_ClientLabel] PRIMARY KEY CLUSTERED 
(
    [ClientId] ASC,
    [LabelId] ASC
) WITH (..) ON [PRIMARY]
) ON [PRIMARY]