Unable to determine the relationship represented by navigation 'Role.CreatedBy' of type 'User'

Question

I am using .NET 6 to develop EF Core MVC program, The concept is single User can have multiple roles, and the role data is create by a User from another system.

I have a base class EntityBase.cs :

public abstract class EntityBase
{
    public int Id { get; set; }
}

And a base class CreatableEntity.cs inherited EntityBase.cs again

public abstract class CreatableEntity : EntityBase
{
    public int CreatedById { get; set; }
    public virtual User CreatedBy { get; set; }
}

Then have a User.cs class inherited CreatableEntity class

public class User : CreatableEntity
{
    public string Username { get; set; }

    public string Password { get; set; }

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

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

And have a Role.cs class inherited CreatableEntity class also

public class Role : CreatableEntity
{
    public string RoleName { get; set; }

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

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

And UserRole.cs class is the table to store "UserId" and "RoleId"

public class UserRole
{
    public int UserId { get; set; }
    public virtual User Users { get; set; }

    public int RoleId { get; set; }
    public virtual Role Roles { get; set; }
}

The OnModelCreating in my context class

public class TestContext : DbContext
{     
    public DbSet<Role> Roles { get; set; }
    public DbSet<User> Users { get; set; }
    public DbSet<UserRole> UserRoles { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        modelBuilder.Entity<Role>()
            .HasBaseType((Type)null)
            .HasKey(rb => rb.CreatedById);

        modelBuilder.Entity<User>()
           .HasMany(u => u.Roles)
           .WithMany(u => u.Users)
           .UsingEntity<UserRole>(
               j => j
                   .HasOne(ua => ua.Roles)
                   .WithMany(a => a.UserRoles)
                   .HasForeignKey(ua => ua.UserId),
               j => j
                   .HasOne(ua => ua.Users)
                   .WithMany(u => u.UserRoles)
                   .HasForeignKey(ua => ua.RoleId)
               j =>
               {
                   j.HasKey(ua => new { ua.RoleId, ua.UserId });
               });
    }
}

When I run the program, hit the following error:

Unable to determine the relationship represented by navigation 'Role.CreatedBy' of type 'User'. Either manually configure the relationship, or ignore this property using the '[NotMapped]' attribute or by using 'EntityTypeBuilder.Ignore' in 'OnModelCreating'.

I researching some time and found a article from Microsoft : mapping using base and derived types

I am tried to implement it but still get error, how should I solve it?

`public virtual User Users { get; set; }` <-- This is a _singular_ property, yet you gave it a plural name... — Dai, Aug 14 '22 at 09:50
What is the purpose of `CreatableEntity`? _Every_ EF entity type is "creatable"... (also using inheritance in EF entity classes causes no-end of problems, just warning you) — Dai, Aug 14 '22 at 09:51
Hi Dai, most of my DB table have column `CreatedById` to record the data is created by which `User`. So I designed a `CreatableEntity` as base for it, so that I can just inherit it instead of add `CreatedById` to all my classes. And the `CreatedById` is foreign key, to link the `User` data during get the record. — Howard Hee, Aug 14 '22 at 09:55

Howard Hee · Accepted Answer · 2022-08-15T04:32:07.723

I solved the issue with following changes:

User.cs

public class User : CreatableEntity
{
    public string Username { get; set; }

    public string Password { get; set; }

    //public virtual ICollection<Role> Roles { get; set; }  <-- get the Role data from UserRole instead of here

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

Role.cs

public class Role : CreatableEntity
{
    public string RoleName { get; set; }

    //public virtual ICollection<User> Users { get; set; }  <-- get the User data from UserRole instead of here

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

UserRole.cs

[Table("UserRoles")]   <-- This changes to match with DB table name, else will hit invalid object name 'UserRole'
public class UserRole
{
    public int UserId { get; set; }
    public virtual User Users { get; set; }

    public int RoleId { get; set; }
    public virtual Role Roles { get; set; }
}

OnModelCreating

public DbSet<Role> Roles { get; set; }
public DbSet<User> Users { get; set; }
//public DbSet<UserRole> UserRoles { get; set; }  <-- no need declare also doesn't matter

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    base.OnModelCreating(modelBuilder);

    modelBuilder.Entity<Role>()
        .HasOne(r => r.CreatedBy)
        .WithMany()
        .HasForeignKey(r => r.CreatedById);

     modelBuilder.Entity<UserRole>()
        .HasKey(ur => new { ur.UserId, ur.RoleId });

    modelBuilder.Entity<UserRole>()
        .HasOne(ur => ur.Users)
        .WithMany(u => u.UserRoles)
        .HasForeignKey(ur => ur.UserId);

    modelBuilder.Entity<UserRole>()
        .HasOne(ur => ur.Roles)
        .WithMany(r => r.UserRoles)
        .HasForeignKey(ur => ur.RoleId);
}

Unable to determine the relationship represented by navigation 'Role.CreatedBy' of type 'User'

1 Answers1