fluent nhibernate - Many to Many mapping with attribute

Question

I have 2 master tables which are linked by a map table as below

User [UserId,Name]

Resource [ResourceId,Name]

UserResourceMap [UserId,ResourceId,AccessLevel]

How would the Resource and User ClassMap with AccessLevel as a resource attribute look?

My Domain classes look like this

public class User
{
    public virtual int UserId { get;protected set; }
    public virtual string Name { get;set; }
}
public class Resource
{
    public virtual int ResourceId { get;protected set; }
    public virtual string Name { get;set; }
    public virtual string AccessLevel { get;set; }//Issue-populate this using fluent
}

How can I use fluent to map the accessLevel attribute in the below code.

public class UserMap : ClassMap<User>
    {
        public UserMap()
        {
            Table("User");
            Id(x => x.Key);
            Map(x=>x.Name);
        }
    }

public class ResourceMap : ClassMap<Resource>
    {
        public ResourceMap()
        {
            Table("Resource");
            Id(x => x.Key);
            Map(x=>x.Name);//Need some Map Here to make a hasManyToMany Map with attribute
        }
    }

Can you show your domain objects too? It's hard to suggest an appropriate mapping from database to objects when we don't know what the objects look like. — Ian Nelson, Feb 17 '11 at 11:47
@Ian Nelson - Updated with code. The hasManyToMany map is unsuitable for this since it cannot handle maps with attributes.I am not sure how nhibernate solves this — Whimsical, Feb 17 '11 at 12:14

Yhrn · Accepted Answer · 2011-02-18T09:32:25.930

Your domain model does not seem to match your database model - the Resource class has the property AccessLevel (i.e. one AccessLevel per Resource) but in the DB model AccessLevel is a column on the map table (i.e. one AccessLevel per User-Resource relation).

Assuming the DB model is the correct model one (fairly straightforward) way of mapping this would be to introduce a class like this.

public class UserResource {

  public virtual int UserResourceId { get; protected set; }
  public virtual User User { get; set; }
  public virtual Resource { get; set; }
  public virtual string AccessLevel { get; set; }
}

and map it in this way:

public class UserResourceMap : ClassMap<UserResource> {

  public UserResourceMap() {

    Table("UserResourceMap");
    Id(x => x.UserResourceId);
    References(x => x.User).UniqueKey("UniqueUserAndResource");
    References(x => x.Resource).UniqueKey("UniqueUserAndResource");
    Map(x => x.AccessLevel);
  }
}

If you want bidirectional associations you could also add a Collection property on User and/or Resource and map these with HasMany(...).Inverse(). Of course, this kind of mapping would introduce a new UserResourceId column in the UserResourceMap table (using a composite key consisting of User and Resource would mitigate that).

Another solution would be to add an EntityMap association. If the association is owned by User it would be a Dictionary<Resource, string> property. Something like this might do the trick:

public class User {
  public virtual int UserId { get; protected set; }
  public virtual string Name { get; set; }
  public virtual Dictionary<Resource, string> Resources { get; set; } // Resource -> AccessLevel
}

public class UserMap : ClassMap<User> {

  public UserMap() {

    Table("User");
    Id(x => x.UserId);
    Map(x => x.Name);
    HasMany<Resource, string>(x => x.Resources).AsEntityMap().Element("AccessLevel");
  }
}

Sorry, missed Ian's answer - but the second part of my answer contains something new at least. — Yhrn, Feb 17 '11 at 15:04
Hey..AsEntityMap Says its depracated...and also Element doesnt exist in it. — Whimsical, Feb 17 '11 at 16:23
@Mulki: I have checked it now and I have a mapping exactly like that and AsEntityMap() is not obsolete and Element() do exist and is used to specify the column name of the for the Dictionary values in the mappings table. Works like a charm. I use NH3 and the "stable pre-release for NH3" version of Fluent but I'm pretty sure I had the same mapping earlier before I switched from NH2.1.2/Fluent1.1. — Yhrn, Feb 18 '11 at 09:31

score 5 · Answer 2 · edited Mar 16 '11 at 10:24

As you've correctly identified in your database schema, this isn't a pure many-to-many relationship - it's two one-to-many relationships as the intermediate table has an attribute (the access level).

I therefore think your domain is missing an entity - there doesn't appear to be any relationship in your model between a user and the resources they can access.

How about something like this:

public class User
{
    public virtual int Id { get;protected set; }
    public virtual string Name { get;set; }
    public virtual ICollection<UserResource> UserResources { get; set;}
}

public class UserResource
{
    public virtual int Id { get; protected set; }
    public virtual User User { get; set;}
    public virtual Resource Resource { get; set;}
    public virtual string AccessLevel { get; set;}
}

public class Resource
{
    public virtual int Id { get;protected set; }
    public virtual string Name { get;set; }
}

And mappings like:

public class UserMap : ClassMap<User>
{
    public UserMap()
    {
        Id(x => x.Id);
        Map(x => x.Name);
        HasMany(x => x.UserResource)
            .AsSet()
            .Inverse()
            .Cascade.AllDeleteOrphan();
    }
}

public class UserResourceMap : ClassMap<UserResource>
{
    public UserResourceMap()
    {
        Table("UserResourceMap");
        Id(x => x.Id);
        References(x => x.User).Not.Nullable();
        References(x => x.Resource).Not.Nullable();
        Map(x => x.AccessLevel);
    }
}

public class ResourceMap : ClassMap<Resource>
{
    public ResourceMap()
    {
        Cache.ReadOnly();

        Id(x => x.Id);
        Map(x => x.Name);
    }
}

fluent nhibernate - Many to Many mapping with attribute

2 Answers2

Linked