1

I'm using database-first approach with Oracle database. In database I have 3 tables: USERS, ROLES, and join table between them: USERS_ROLES_ASSOCIATION. My model consists of two classes:

[Table("USERS", Schema = "SIGNUM")]
public class User
{
    public User()
    {
        Roles = new HashSet<Role>();
    }

    [Key]
    [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
    [Column("USR_ID")]
    public decimal UserId { get; set; }

    [Column("USR_LOGIN")]
    public string Login { get; set; }

    [Column("USR_PASS")]
    public string Password { get; set; }

    public virtual ICollection<Role> Roles { get; set; }
}

[Table("ROLES", Schema = "SIGNUM")]
public class Role
{
    public Role()
    {
        Users = new HashSet<User>();
    }

    [Key]
    [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
    [Column("ROL_ID")]
    public decimal RoleId { get; set; }

    [Column("ROL_NAME")]
    public string RoleName { get; set; }

    public virtual ICollection<User> Users { get; set; }
}

When I try to resolve user's roles:

using (var dbContext = new AppDbContext())
{
    User user = dbContext.Users.FirstOrDefault(u => string.Equals(u.Login, "someusername"));
    var roles = user.Roles.Select(r => r.RoleName); // exception
}

... I get the following error: "ORA-00942: table or view does not exist".

The problem is that EF is looking for table "dbo"."RoleUsers" instead of "SIGNUM"."USERS_ROLES_ASSOCIATION". How can I specify join table name for many-to-many relationship and schema name in database-first approach?

Signum
  • 845
  • 1
  • 10
  • 33

1 Answers1

4

How can I specify join table name for many-to-many relationship and schema name in database-first approach?

You'd have to specify it manually from the fluent config code... 

modelBuilder.Entity<User>()
    .HasMany(x => x.Roles)
    .WithMany(x => x.Users)
    .Map(x => x.ToTable("UserRole", "SIGNUM"));
NSGaga-mostly-inactive
  • 14,052
  • 3
  • 41
  • 51
  • I have to use a database-first approach, NOT code-first and that's the problem – Signum Apr 20 '13 at 18:00
  • your setup is code-first - you mean you want code-first but with existing database ? – NSGaga-mostly-inactive Apr 20 '13 at 19:31
  • OK, now I think I don't understand. I generated model from existing database, then added attributes to classes and properties (TableAttribute, Key, Column etc) and changed their names - because I didn't like auto-generated names for example "USERS" or "ROL_ID" etc. It is still database-first approach, am I right? – Signum Apr 20 '13 at 21:14
  • 1
    http://msdn.microsoft.com/en-us/data/jj206878.aspx - and e.g. http://msdn.microsoft.com/en-us/magazine/hh148150.aspx. If you use EDMX file you're db or model first. If you don't have an EDMX, but specify in code like you do, then it's `Code First with an existing database`. You can mix now (and gets blury, why you have issues), as with EF Power Tools you can reverse engineer. Still code-first if you use 'code' to 'hold' your model - or change your model. – NSGaga-mostly-inactive Apr 20 '13 at 21:28
  • 1
    Thanks! Also this post was very helpful for me: http://stackoverflow.com/questions/6028375/entity-framework-code-first-many-to-many-setup-for-existing-tables – Signum Apr 21 '13 at 15:03