EF Core - Multiple references to the same table

Question

Using Entity Framework Core (5.0.1) code first I'm having trouble implementing a class that has two references to another class.

This is basically the structure I want:

public class Location
{
    public int Id { get; set; }

    public string Name { get; set; }
}


public class Race
{
    public int Id { get; set; }

    public string Title { get; set; }

    public int? StartLocationId { get; set; }
    public Location StartLocation { get; set; }

    public int? EndLocationId { get; set; }
    public Location EndLocation { get; set; }
}

This actually works fine, but as I'm implementing the form to create a new Race I want to add some validation attributes:

public class Race
{
    public int Id { get; set; }

    [Required]
    public string Title { get; set; }

    [Required]
    public int? StartLocationId { get; set; }
    public Location StartLocation { get; set; }

    [Required]
    public int? EndLocationId { get; set; }
    public Location EndLocation { get; set; }
}

The Required attributes on the location references makes it impossible to create the tables in the initialization of the database (which I'm currently running at all startups since I'm at the beginning of the development). This is the error message I get:

Introducing FOREIGN KEY constraint 'FK_Races_Locations_StartLocationId' on table 'Races' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints. Could not create constraint or index.

As I understand it this has to do with a problem with cascade delete. I'm ok with disabling cascade delete in the database, but I would like the properties to be required for validation in the GUI. Is that even possible?

For the other relations in my model classes that are not standard I use the Fluent Api to configure, so a solution using that would be best. However, solving the problem is my priority so a solution using attributes is also fine.

I have found other threads here on StackOverflow with very similiar questions, but none of the suggested solutions have worked for me (some are for Entity Framework, not Core):

EF Core Multiple References to Same Entity EF Core 2.2 - Two foreign keys to same table Entity Framework Code First - two Foreign Keys from same table Entity Framework multiple references to same table

score 7 · Accepted Answer · answered Jan 20 '21 at 20:43

I finally found a solution! This is what I needed to do:

modelBuilder.Entity<Race>()
    .HasOne(r => r.StartLocation)
    .WithMany()
    .OnDelete(DeleteBehavior.Restrict);

modelBuilder.Entity<Race>()
    .HasOne(r => r.EndLocation)
    .WithMany()
    .OnDelete(DeleteBehavior.Restrict);

I found the solution in this post (thanks Ivan Stoev!): Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints. - How?

score -1 · Answer 2 · answered Jan 20 '21 at 04:33

-1

Have you tried FluentApi doing this on OnModelCreating?

public class AppContext : DbContext
{
   protected override void OnModelCreating(DbModelBuilder modelBuilder)
   {
      // ...
      modelBuilder.Entity<Race>.Property(r => r.StartLocation).IsOptional();
      modelBuilder.Entity<Race>.Property(r => r.EndLocation).IsOptional();
   }

}

Then run the migration again, update the database and it should work

answered Jan 20 '21 at 04:33

Agus Mariscotti

56
7

I tried this this, but I got a build error. I tried adding parenthesises behind modelBuilder.Entity, but it still said that there is no definition for IsOptional. After some searching on this I learned that there is no IsOptional in EF Core but IsRequired instead (same but reversed). I tried that, which let me build but it still did not work ("An error occured when creating the database"). I guess it gets confusing creating the database when I state with the fluent api that the property is not required but then say it is required with the attribute [Required] in the model class. – haagel Jan 20 '21 at 08:19

EF Core - Multiple references to the same table

2 Answers2