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.

score 1 · Answer 1 · answered Jun 11 '12 at 23:20

1

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]

answered Jun 11 '12 at 23:20

Craig

36,306
34
114
197

1

i'm pretty sure he understands the end result that he wants, but is mainly asking how to generate that script using nhibernate and fluent nhibernate. – nathan gonzalez Jun 12 '12 at 15:32
Yes, exactly, I can certainly add clustered indexes to tables after NHibernate creates the schema, but I have to do that manually in SQL. I'm trying to figure out how to use either fluent nhibernate or nhibernate to create the clustered index when the table is generated. – David Sulpy Jun 12 '12 at 15:43

score 1 · Answer 2 · answered Jun 12 '12 at 19:02

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>

idbag was exactly what I needed. It's really unfortunate that the fluent abstraction of NHibernate doesn't expose this yet because I hate having to add hbm xml files to my fluent project. Thanks for the answer! — David Sulpy, Jun 13 '12 at 01:54
Ah, one thing I didn't notice until just now though... Now I have to add all of my properties for the entities inside the class node of the hbm files because it's like the hbm files mappings are now taking precedence over my Automapping on the C# Entities. — David Sulpy, Jun 13 '12 at 02:05

score 1 · Accepted Answer · edited Jun 16 '14 at 22:57

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>

This works great. Important to note that the xml file needs to have "Build Action" set to "Embedded". Some day, I would like to be able to do this without HBM files... maybe a Dialect? Who knows. — Byron Sommardahl, Jun 16 '14 at 22:57

score 0 · Answer 4 · answered Jun 24 '12 at 11:40

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

Many-to-Many fluent NHibernate mapping with SQL Azure

4 Answers4