3 way relationship: User, LOB and Role

Question

Let's say we have User, Line Of Business (LOB) and Role. User have 1 LOB. But a user can also have number of roles within different LOBs.

The Code First model below creates 3 tables USERS, ROLES and LOBS. If left to his own devices it also creates UserRoles. Which is not what I want.

So far I've tried creating UserLOBRole {RoleId, UserId, LOBCode} object and marked all fields as composite primary, however EF barks about foreign key.

Is there any way I can describe such relationships in EF 4.1 code first?

Any help is greatly appreciated.

public class User
{
    public int UserId {get;set;}
    public string UserName {get;set;}
    public virtual LOB UserLOB {get;set;}

    // HOW DO I DEFINE RELATIONSHIP HERE? 
    public virtual ICollection<Role> UserRoles {get;set;}

}

public class Role
{
    public int RoleId {get;set;}
    public string RoleName {get;set;}
}

public class LOB
{
    public string LOBCode {get;set;}
    public string LobName {get;set;}
}

Answer 1

It is not possible without exposing UserLOBRole as separate entity and interconnect User, Role and LOB through this new entity. Hiding junction table works only for many-to-many relation without any additional data but that is not this case.

public class UserLOBRole
{
    [Key, Column(Order = 0)]
    public int UserId { get; set; }
    [Key, Column(Order = 1)]
    public int RoleId { get; set; }
    [Key, Column(Order = 2)]
    public string LOBCode { get; set; }

    public virtual User User { get; set; }
    public virtual Role Role { get; set; }
    [ForeignKey("LOBCode")]
    public virtual LOB LOB { get; set; }
}

Your code probably complains about FKs because EF code first demands navigation property on at least one side of the realation and your LOB and Role currently don't have it so UserLOBRole must have them.

And your User will now use:

public class User
{
    public int UserId {get;set;}
    public string UserName {get;set;}
    public virtual LOB UserLOB {get;set;}

    public virtual ICollection<UserLOBRole> UserRoles {get;set;}
}