NHibernate Many to Many mapping Invalid Column arbitrary [table]_[id]

Question

Ok. I have read a lot of similar situations but all of them seems to be more related to one-to-many mappings.

long story short... Here is the code.

public class Roles
    {
        public virtual int RoleID { get; protected set; }
        public virtual Guid RoleGUID { get; protected set; }
        public virtual string RoleName { get; protected set; }
        public virtual string RoleDescription { get; protected set; }
        public virtual IList<User> Users { get; protected set; }
    }


public class RolesMap:ClassMap<Roles>
    {
        public RolesMap()
        {
            Table("Web_Roles");
            Id(x => x.RoleID);
            Map(x => x.RoleDescription);
            Map(x => x.RoleName);
            Map(x => x.RoleGUID);
            HasManyToMany(x => x.Users)
                .Cascade.All()
                .Inverse()
                .Table("Web_UserRoles");
        }

    }


public class User
{
    public virtual int UserID { get; protected set; }

    public virtual string UserName { get; protected set; }
    public virtual string Password { get; protected set; }
    public virtual string Email { get; protected set; }
    public virtual Guid UserGUID { get; protected set; }
    public virtual IList<Roles> Roles { get; protected set; }
}



public class UserMap: ClassMap<User>
{
    public UserMap()
    {
        Table("Web_User");
        Id(x => x.UserID);

        Map(x => x.UserName);
        Map(x => x.Password);
        Map(x => x.UserGUID);
        Map(x => x.Email);
        HasManyToMany(x => x.Roles)
            .Cascade.All()
            .Table("Web_UserRoles");
    }
}


public class UserRoles
{
    public virtual int RoleID { get; protected set; }
    public virtual int UserID { get; protected set; }
}

So, that's the domain entities and their respectives mappings. Not biggie. Of course, no mapping for UserRoles since it's only a "mapping table".

When i get my User I get the following error.

could not initialize a collection: [FamilyDerm.AUTH.Domain.User.Roles#1][SQL: SELECT roles0_.UserID as UserID1_, roles0_.Roles_id as Roles4_1_, roles1_.RoleID as RoleID1_0_, roles1_.RoleDescription as RoleDesc2_1_0_, roles1_.RoleName as RoleName1_0_, roles1_.RoleGUID as RoleGUID1_0_ FROM Web_UserRoles roles0_ left outer join Web_Roles roles1_ on roles0_.Roles_id=roles1_.RoleID WHERE roles0_.UserID=?]

It doesn't take much to realize that the mapping is converting RoleID from my "mapping entity" UserRoles to Roles_id, which I don't understand why. I am following NHibernate exact "way" but i don't seem to get it straight.

If I replace in SQL Server Roles_id by RoleID, it works like a charm. Obviously I am having either a naming convention issue or a mapping issue itself.

As I write this, i see questions popping out to my right with similar concerns but none of them offer a solution to my problem.

Thank you.

Did my answer help at all? – connectedsoftware Aug 09 '13 at 08:46 — connectedsoftware, Aug 09 '13 at 08:46

score 1 · Answer 1 · answered Jul 15 '13 at 20:46

Because you haven't explicitly declared the field names in the Web_UserRoles table nHibernate infers them by adding an _Id to the name of the child collection so

HasManyToMany(x => x.Users)
     .Cascade.All()
     .Inverse()
     .Table("Web_UserRoles");

will infer a field named Users_Id in the Web_UserRoles table and likewise Roles_Id from your HasManyToMany Roles mapping.

Modifying your HasManyToMany definitions to explicitly define the Parent and Child Id columns from your ProductInProduct table should sort your issue:

HasManyToMany(x => x.Users)
    .Cascade.All()
    .Inverse()
    .Table("Web_UserRoles")
    .ParentKeyColumn("UserId")
    .ChildKeyColumn("RoleId");

and

 HasManyToMany(x => x.Roles)
    .Cascade.All()
    .Table("Web_UserRoles")
    .ParentKeyColumn("RoleId")
    .ChildKeyColumn("UserId");

NHibernate Many to Many mapping Invalid Column arbitrary [table]_[id]

1 Answers1