Many-to-many relationship with POCO classes in Entity Framework (DB first)

Question

I have a context that looks like this:

public DbSet<User> Users { get; set; }
public DbSet<AccessGroup> AccessGroups { get; set; }
public DbSet<UserAccessGroup> UsersAccessGroups { get; set; }

The database has three tables:

Users [Pk = Id, ...]
AccessGroups [Pk = Id, ...]
UsersAccessGroups [Pk = Id, Fk = UserId, Fk = AccessGroupId, ...] (Has columns for created by and when as well)

We have three classes:

public class User
{
  public virtual int Id { get; set; }
  public virtual ICollection<UserAccessGroup> UsersAccessGroups { get; set; }
  // Some more..
}

public class AccessGroup
{
  public virtual int Id { get; set; }
  public virtual ICollection<UserAccessGroup> UsersAccessGroups { get; set; }
  // Some more..
}

[Table("UsersAccessGroups")]
public class UserAccessGroup
{
  public virtual int Id { get; set; }
  public virtual int UserId { get; set; }
  public virtual int AccessGroupId { get; set; }
  public virtual int CreatedByUserId { get; set; }
  public virtual DateTime CreatedAt { get; set; }

  public virtual User User { get; set; }
  public virtual AccessGroup AccessGroup { get; set; }
  public virtual User CreatedByUser { get; set; }
}

I would like to be able to use a user object like this:

var user = Context.Users.Select(x => x);
var accessGroup = user.UsersAccessGroups.Select(x => x.AccessGroup).ToList();

The second row doesn't not work however. I get error saying "Invalid column name User_Id" The same code works when I have a one-to-one relationship (like CreatedByUser).

I have also tried something like this:

Context.Users.Include("UsersAccessGroups")
  .Include("UsersAccessGroups.AccessGroup")
  .Include("UsersAccessGroups.User").Select(...);

but I get the same problem.

Is it possible to get this to work, or do I have to do it differently somehow?

score 0 · Answer 1 · answered Jan 16 '13 at 15:16

You'll need to specify the foreign key for CreatedByUser, since EF will not be able to map that by convention.

[Table("UsersAccessGroups")]
public class UserAccessGroup
{
  public virtual int Id { get; set; }
  public virtual int UserId { get; set; }
  public virtual int AccessGroupId { get; set; }
  public virtual int CreatedByUserId { get; set; }
  public virtual DateTime CreatedAt { get; set; }

  public virtual User User { get; set; }
  public virtual AccessGroup AccessGroup { get; set; }
  [ForeignKey("CreatedByUserId")]
  public virtual User CreatedByUser { get; set; }
}

Many-to-many relationship with POCO classes in Entity Framework (DB first)

1 Answers1