Entity Framework Core fails to determine relationship between two entities

Question

Entity Framework Core Version: 7.0.5
Entity Framework Core Design Version: 7.0.5
.Net Version: 6

I have 3 classes:

public class AppUser
{
    public string Id { get; set; }
    public string? Name { get; set; }
    public string? Email { get; set; }
    public int? DepartmentId { get; set; }
    public int? OrganizationId { get; set; }



    public virtual Department? Department { get; set; }
    public virtual Organization? Organization { get; set; }
}

public class Department
{
    public int Id { get; set; }
    public string? Name { get; set; }
    public int? OrganizationId { get; set; }
    public string? CreatorId { get; set; }
    public DateTime? CreatedAt { get; set; }
    public string? UpdaterId { get; set; }
    public DateTime? UpdatedAt { get; set; }



    public virtual IList<AppUser>? Users { get; set; }
    public virtual Organization? Organization { get; set; }
    public virtual AppUser? Creator { get; set; }
    public virtual AppUser? Updater { get; set; }
}

public class Organization
{
    public int Id { get; set; }
    public string? Name { get; set; }
    public string? CreatorId { get; set; }
    public DateTime? CreatedAt { get; set; }
    public string? UpdaterId { get; set; }
    public DateTime? UpdatedAt { get; set; }



    public IList<AppUser>? Users { get; set; }
    public virtual AppUser? Creator { get; set; }
    public virtual AppUser? Updater { get; set; }
}

They are configured in the following DbContext:

public class AppDbContext : DbContext
{
    public AppDbContext(DbContextOptions<AppDbContext> options) : base(options)
    {
        
    }

    public DbSet<AppUser> Users { get; set; }
    public DbSet<Department> Departments { get; set; }
    public DbSet<Organization> Organizations { get; set; }

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

        foreach (var relationship in builder.Model.GetEntityTypes().SelectMany(e => e.GetForeignKeys()))
        {
            Console.WriteLine($"Relationship: {relationship.PrincipalEntityType.Name} -> {relationship.DeclaringEntityType.Name}");
            Console.WriteLine($"Foreign Key: {relationship.Properties.Select(p => p.Name).Aggregate((p1, p2) => $"{p1}, {p2}")}");
            Console.WriteLine($"Principal Key: {relationship.PrincipalKey.Properties.Select(p => p.Name).Aggregate((p1, p2) => $"{p1}, {p2}")}");
            Console.WriteLine();
        }
    }
}

From what I know, Entity Framework Core should be able to automatically recognise that I have a DepartmentId and a Department propeties, therefore create a relationship between AppUser and Department.

However, when I try to add a relationship with: dotnet ef migrations add Initial

I get the following error:

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

So next, I decided to try and explicitly declare the relationships with:

builder.Entity<AppUser>(p =>
{
    p.HasOne(u => u.Department)
        .WithMany(d => d.Users)
        .HasForeignKey(u => u.DepartmentId);

    p.HasOne(u => u.Organization)
        .WithMany(o => o.Users)
        .HasForeignKey(u => u.OrganizationId);
});

However, after that, it gave me the following error:

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

Now, I have in the same project around 5 other classes, all following the same naming convention of "CreatorId" and "Creator" along with "UpdaterId" and "Updater" and all of them are recognised and not a problem occurs. However, for these 3 classes, the problem always arises, I even tried taking them out to a separate project, installing efcore, and setting everything up and it happened again.

I do not want to explicitly have to define every relationship when they have allowed automatic recognision. What I don't undestand is it works on most objects, just these 3 are the problems.

Also, if I fix all of the errors, then entity framework core decides to tell me it will create "shadow propeties" to track my creator and updater in the organization and name them "CreatorId1" and "UpdaterId1", the 1 is obviously because I already have properties with the same name, but it won't automatically recognie them ...

I already described what I tried, I am expecting the automatic navigation properties to be properly recognised and create the correct relationships between the three objects.

Also note: I also tried removing the OrganizationId and Organization from the AppUser wondering if that could cause some circular reference problems, but that did not change anything.

Answer 1

Mark your FKs explicitly either through attributes:

public string? CreatorId { get; set; }
[ForeignKey(nameof(CreatorId))]
public virtual AppUser? Creator { get; set; }

public string? UpdaterId { get; set; }
[ForeignKey(nameof(UpdaterId))]
public virtual AppUser? Updater { get; set; }

... or via the HasForeignKey<Department>() etc in the fluent configuration for the relationship.

When you have a one-to-many / many-to-one relationship, EF can identify an existing FK property by convention, however it expects the FK name to be based on the target entity type not the navigation property name.

For instance if you have an Order that references a Customer, you would have something like:

public int CustomerId { get; set; }
public virtual Customer Customer { get; set; }

... and it works. However, while you might assume it is because the Customer reference is named "Customer" it is because the type is Customer. Where in can get confusing is when you break this convention, commonly for instances where you have two references to the same table:

public string? CreatorId { get; set; }
public virtual AppUser? Creator { get; set; }

public string? UpdaterId { get; set; }
public virtual AppUser? Updater { get; set; }

The type of both navigation properties is "AppUser" So in either case EF convention would be to look for "AppUserId" or "AppUser_Id" and it would be unhappy since in would need two distinct IDs. Earlier versions would automatically search for and in some cases create an AppUserId and AppUserId_1.