Entity Framework core one to many relationship

c# entity-framework-core one-to-many

Question

I'm trying to build a code first data model with a one to many relationship.

There is a UserDTO and a RoleDTO model with the User containing one to many roles.

Some simplified code for the RoleDTO is:

internal class RoleDTO
{
    public UserDTO User { get; set; }

    public string Value { get; set; }

    public RoleDTO()
    {

    }
}

And the simplified UserDTO:

internal class UserDTO
{
    public string Email { get; set; }

    public string FullName { get; set; }

    public string UserName { get; set; }

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

    public UserDTO()
    {

    }
}

And finally the class that inherits from DBContext

class StorageContext : DbContext
{
    public DbSet<UserDTO> Users { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer(@"My_Connection_String");
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<UserDTO>().HasKey(entity => entity.Email);
        modelBuilder.Entity<UserDTO>().HasMany(entity => entity.Roles);

        modelBuilder.Entity<RoleDTO>().HasOne<UserDTO>().WithMany(x => x.Roles);
        modelBuilder.Entity<RoleDTO>().HasKey(x => x.Value);
    }
}

When I run my add migration and I have no database or migrations folder (and even if I ran it after having some columns anyways) the Role table gets created with an extra columns referring the the User's email address.

I only want 2 columns in my role table, one that is the user's email address and is a FK to the User email PK, and the other which is a string value that combined with the foreign key make a compound primary key in the user table. Diagram below:

*******************************
* Role                        *
*******************************
* User_Email - string/varchar *
* Value - string /varchar     *
*******************************
FK - User_Email to User table Email column
PK - User_Email, Value compound primary key

Does anyone know how to achieve this using either the fluent API or data annotations?

Thanks!

Accepted Answer

First of all, class UserDTO does not have property named User_Email. You can define a shadow property in the model using EF but considering you want it to be in the model, you should define property on the class public string User_Email

Once you have above property defined on class or a shadow property defined in the model using fluent API,

To define composite primary key, you need to use HasKey fluent API. You cannot use data annotations to define composite primary key.

To define User_Email, Value as composite PK, write following code in OnModelCreating (after defining shadow property if you going that way)

modelBuilder.Entity<UserDTO>().HasKey(e => new { e.User_Email, e.Value });

To specify foreign key property for relationship, you can either use ForeignKeyAttribute on fk property/navigation or you can use HasForeignKey fluent API.

To set User_Email to be FK to UserDTO table, assuming that UserDTO User in RoleDTO & ICollection<RoleDTO> Roles in UserDTO are navigations for this relationship.

With data annotations, write ForeignKey("User") on User_Email property or write ForeignKey("User_Email") on either navigation(User/Roles). Remember you cannot use this if you are having User_Email as shadow property.

With Fluent API, write following code in OnModelCreating to configure the relationship fully

modelBuilder.Entity<RoleDTO>().HasOne(e => e.User).WithMany(e => e.Roles).HasForeignKey(e => e.User_Email);

Above will create one-to-many relationship using User & Roles navigations which uses User_Email as foreign key property.

The reason your code did not work correctly is

  1. Using HasKey, you have defined single property PK instead of composite PK.
  2. You have not used HasForeignKey method to specify the foreign key property so EF will add a shadow property for you. Further, in your relationship configuration, you used only Roles navigation. EF will try to create another relationship using User navigation, effectively making 2 relationships between UserDTO & RoleDTO. That is the reason you got extra columns because EF created shadow properties to be used as foreign key properties by convention.

Popular Answer

I am not sure what you are trying to achieve with this, but working with composite keys in ORMs can be complex and an area that can be unpredictable. So I would advise going against composite keys unless you have an actual good reason.

I would suggest not using the email address as a PK anywhere. Use a unique generated Int32/Int64 or even Guid as Primary Key (this will reflect in your Roles table as a foreign key once you've setup the relationship) and put an index on your email column (in your User table) that has a unique constraint on it. You can add the index in your migration script. At least this way you can keep identity if the email address were to change (if that is what you want).

I hope this helps.



Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why