C# EF Core 6. Many to many fluent configuration. First Database

Question

I have 3 tables

user (Id, name, password)
role (Id, name, description)
user_role (IdUser, IdRole)

when I do reverse engineering Link, creates the model user_role and map it in the context using fluent, but in the models of user and role the scaffolding command don't add the corresponding lists fields:

user (List Roles{get;set;})
role (List Users{get;set;})

I add the list of role manually to user model and the list of user to role model, and I'm trying to configure the mapping context with just two tables user and role ignoring the user_role table in the modeling and just referencing, I have try this in the context:

modelBuilder.Entity<User>(entity =>
{
.... other configuration of properties and table name...
   entity.HasMany(e => e.Roles)
   .WithMany(e => e.Users)
   .UsingEntity("UserRole")
   .HasAlternateKey(new string[] { "IdUser" });
});

I do similar for role model configuration but with IdRole in the alternative key.

And I get this error:

Message=The property 'IdUser' cannot be added to the type 'UserRole (Dictionary<string, object>)' because no property type was specified and there is no corresponding CLR property or field. To add a shadow state property, the property type must be specified.

I try this configuration instead:

modelBuilder.Entity<User>(entity =>
{
.... other configuration of properties and table name...
.UsingEntity<Dictionary<string, object>>(
                    "UserRole",
                    j => j
                        .HasOne<User>()
                        .WithMany()
                        .HasForeignKey("IdUser")
                        .HasConstraintName("FK_UserRole_User")
                        .OnDelete(DeleteBehavior.ClientCascade)
                    );
});

I do something similar to the role model.

This give me the next error:

Message=Invalid column name 'RolesId'. Invalid column name 'RolesId'. Invalid column name 'UsersId'. Invalid column name 'UserId'. Source=Core Microsoft SqlClient Data Provider

The way I try to call the data in the c# code is like this:

IList<User> users = AppDb.Users.Include(e => e.Roles).ToList();

how should I configure the entity in the context???

Any help will be appreciated.

There is related questions but is not like what I need, for example this one: EF 6 Core - Many-to-Many Relationship Database First (EF Core Power Tools/scaffold-dbcontext)

Rafael Alfredo Zelaya Amaya · Answer 1 · 2022-09-13T21:17:30.533

I found an available solution. Seams to be this configuration the one in the correct way:

.UsingEntity<Dictionary<string, object>>(
                    "UserRole",
                    j => j
                        .HasOne<User>()
                        .WithMany()
                        .HasForeignKey("IdUser")
                        .HasConstraintName("FK_UserRole_User")
                        .OnDelete(DeleteBehavior.ClientCascade)
                    );

but creates something called "Shadow Properties" to join the 3 tables(something described here in the "Fully defined relationships" section) ignoring my "HasForeignKey" attribute and creating 2 shadow properties for each table, that's are the ones that SQL tell me not recognize, this error message:

Message=Invalid column name 'RolesId'. Invalid column name 'RolesId'. Invalid column name 'UsersId'. Invalid column name 'UserId'. Source=Core Microsoft SqlClient Data Provider

I still don't know how to use my own join properties but I can define the join table, like this:

This is for User mapping

entity.HasMany(e => e.Roles) .WithMany(e => e.Users) .UsingEntity("UserRole");
this is for Role mapping

entity.HasMany(e => e.Users) .WithMany(e => e.Roles) .UsingEntity("UserRole");

after that I run the application and see the names that sql don't recognize. RolesId and UsersId That are the names of the fields in the join table that Entity framework is looking for and I change my original fieldnames of my join table with that ones.

and it's work... I would like to define myself the names but for now is fine. If someone knows how to do that I will appreciate it.

C# EF Core 6. Many to many fluent configuration. First Database

1 Answers1