Entity Framework Code First - Two Foreign Keys from Same Table

Entity Framework Code First - two Foreign Keys from same table

Try this:

public class Team
{
    public int TeamId { get; set;} 
    public string Name { get; set; }

    public virtual ICollection<Match> HomeMatches { get; set; }
    public virtual ICollection<Match> AwayMatches { get; set; }
}

public class Match
{
    public int MatchId { get; set; }

    public int HomeTeamId { get; set; }
    public int GuestTeamId { get; set; }

    public float HomePoints { get; set; }
    public float GuestPoints { get; set; }
    public DateTime Date { get; set; }

    public virtual Team HomeTeam { get; set; }
    public virtual Team GuestTeam { get; set; }
}


public class Context : DbContext
{
    ...

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Match>()
                    .HasRequired(m => m.HomeTeam)
                    .WithMany(t => t.HomeMatches)
                    .HasForeignKey(m => m.HomeTeamId)
                    .WillCascadeOnDelete(false);

        modelBuilder.Entity<Match>()
                    .HasRequired(m => m.GuestTeam)
                    .WithMany(t => t.AwayMatches)
                    .HasForeignKey(m => m.GuestTeamId)
                    .WillCascadeOnDelete(false);
    }
}

Primary keys are mapped by default convention. Team must have two collection of matches. You can't have single collection referenced by two FKs. Match is mapped without cascading delete because it doesn't work in these self referencing many-to-many.

Entity Framework Core - Code First - Two Foreign Keys - One Table

Apparently you have a 2 Fks to the same table in the same row so imagine if you delete a user who has created an assignment and Modified one what should happen, delete the assignment what if other users modified it this will be nulls in heir tables.

so

You need to specify the CascadeOnDelete to false using FluentApi

In the ApplicationContext:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Entity<DivAssignment>()
                .HasRequired(c => c.CreatedByUser)
                .WithMany(u => u.CreatedDivAssignments)
                .HasForeignKey(c => c.CreatedByUserId)
                .WillCascadeOnDelete(false);

            modelBuilder.Entity<DivAssignment>()
                .HasRequired(c => c.LastModifiedByUser)
                .WithMany(u => u.ModifiedDivAssignments)
                .HasForeignKey(c => c.LastModifiedByUserId)
                .WillCascadeOnDelete(false);
        }

Then add-migration then update-database

EF Core 2.2 - Two foreign keys to same table

public class Fixture
{
    public int Id { get; set; }
    public int HomeTeamId { get; set; }
    public int AwayTeamId { get; set; }

    [ForeignKey("HomeTeamId")]
    public virtual Team HomeTeam { get; set; }

    [ForeignKey("AwayTeamId")]
    public virtual Team AwayTeam { get; set; }
}

This way navigation will work. Also as suggested by @Ivan remove duplicate getters and setters.

EF Core - Multiple references to the same table

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?

Two Foreign Keys in Entity Framework Core

I managed to make it works using Fluent API.

Code in my DbContext:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Message>()
        .HasOne(p => p.Receiver)
        .WithMany(t => t.MessagesReceived)
        .HasForeignKey(m => m.ReceiverId)
        .OnDelete(DeleteBehavior.Restrict);

    modelBuilder.Entity<Message>()
        .HasOne(p => p.Sender)
        .WithMany(t => t.MessagesSent)
        .HasForeignKey(m => m.SenderId)
        .OnDelete(DeleteBehavior.Restrict);                
}

What is more, I've discovered a problem with not set User deletion behaviour.
There are two options to solve it.

First is keeping Messages if User was deleted:

.OnDelete(DeleteBehavior.Restrict);

Or second which will remove Messages:

.OnDelete(DeleteBehavior.Cascade);

Two foreign key elements of the same table on an Entity Framework model?

Does this look right to you?

No.
Try something like:

public class Lesson
{
    [Key]
    public int LessonId { get; set; }

    [ForeignKey("Student")]
    public string StudentId { get; set; } 
    public Person Student { get; set; }

    [ForeignKey("Teacher")]
    public string TeacherId { get; set; }
    public Person Teacher{ get; set; }

}

Defining multiple Foreign Key for the Same table in Entity Framework Code First

To achieve what you want you need to provide some aditional configuration.Code First convention can identify bidirectional relationships, but not when there are
multiple bidirectional relationships between two entities.You can add configuration (using Data Annotations or the Fluent API) to present this
information to the model builder. With Data Annotations, you’ll use an annotation
called InverseProperty. With the Fluent API, you’ll use a combination of the Has/With methods to specify the correct ends of these relationships.

Using Data Annotations could be like this:

public class Student
{
  public int ID { get; set; }

  public string Name { get; set; }

  public string Surname { get; set; }

  public int BirthCityID { get; set; }

  public int LivingCityID { get; set; }


  [ForeignKey("BirthCityID")]
  [InverseProperty("Students")]
  public virtual City BirthCity { get; set; }

  [ForeignKey("LivingCityID")]
  public virtual City LivingCity { get; set; }
}

This way you specifying explicitly that you want to relate the BirthCity navigation property with Students navigation property in the other end of the relationship.

Using Fluent Api could be like this:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
     modelBuilder.Entity<Student>().HasRequired(m => m.BirthCity)
                                 .WithMany(m => m.Students).HasForeignKey(m=>m.BirthCityId);
     modelBuilder.Entity<Student>().HasRequired(m => m.LivingCity)
                                 .WithMany().HasForeignKey(m=>m.LivingCityId);
}

With this last solution you don't need to use any attibute.

Now, the suggestion of @ChristPratt in have a collection of Student in your City class for each relationship is really useful. If you do that, then the configurations using Data Annotations could be this way:

public class Student
{
  public int ID { get; set; }

  public string Name { get; set; }

  public string Surname { get; set; }

  public int BirthCityID { get; set; }

  public int LivingCityID { get; set; }


  [ForeignKey("BirthCityID")]
  [InverseProperty("BirthCityStudents")]
  public virtual City BirthCity { get; set; }

  [ForeignKey("LivingCityID")]
  [InverseProperty("LivingCityStudents")]
  public virtual City LivingCity { get; set; }
}

Or using Fluent Api following the same idea:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
     modelBuilder.Entity<Student>().HasRequired(m => m.BirthCity)
               .WithMany(m => m.BirthCityStudents).HasForeignKey(m=>m.BirthCityId);
     modelBuilder.Entity<Student>().HasRequired(m => m.LivingCity)
               .WithMany(m => m.LivingCityStudents).HasForeignKey(m=>m.LivingCityId);
}

Entity Framework Core Two Foreign Keys - Same Table

At present EF Core does not support lazy loading. Tracking issue here

That means by default navigation properties will not be loaded and will remain null. As a work-around you can use eager loading or explicit loading patterns.

Eager Loading

Eager loading is pattern where you request for the referenced data you need eagerly while running the query using Include API. The usage is somewhat different from how it worked in EF6. To include any navigation, you specify the lambda expression (or string name) in include method in your query.

e.g. await _context.Fixtures.Include(f => f.HomeTeam).FirstOrDefaultAsync(f => f.Id == id);

Presently, filtered include is not supported so you can request to load navigation fully or exclude it. So the lambda expression cannot be complex. It must be simple property access. Also to load nested navigation, you can either chain them with property access calls (like a.b.c) or when its after collection navigation (since you cannot chain them) use ThenInclude.

e.g. await _context.Fixtures.Include(f => f.HomeTeam).ThenInclude(t=> t.HomeFixtures).FirstOrDefaultAsync(f => f.Id == id);

It would be good to remember that include represent a path of navigation from the entity type its being called on to populate all naviagations on the path. Often you may need to write repeated calls if you are including multiple navigations at 2nd or further level. That is just for syntax though and query will be optimized and will not do repeated work. Also with string include you can specify whole navigation path without needing to use ThenInclude. Since reference navigation can utilize join to fetch all data needed in single query, & collection navigation can load all related data in single query, eager loading is most performant way.

Explicit Loading

When you have loaded object in the memory and need to load a navigation, while lazy loading would have loaded it while accessing navigation property, in the absence of that you need to call Load method by yourself. These methods are defined on ReferenceEntry or CollectionEntry.

e.g.

Fixture fixture = await _context.Fixtures.SingleOrDefaultAsync(f => f.Id == id);
_context.Entry(fixture).Reference(f => f.HomeTeam).Load();

var team = await _context.Teams.SingleOrDefaultAsync(t => t.Id == id);
_context.Entry(team).Collection(f => f.HomeFixtures).Load();

For reference navigation you would need Reference on EntityEntry to get ReferenceEntry. For collection navigation equivalent method is Collection. Then you just call Load method on it to load the data in the navigation. There is also async version of LoadAsync if you need.

Hope this helps.

Multiple Foreign Key for Same table in Entity Framework Code First

The problem is that you have multiple paths of cascade deletes, that could end trying to delete the same row in the database. To understand more about this error, take a look at https://support.microsoft.com/en-us/kb/321843. Follow the steps above to solve the problem.

Change your class:

public int ID { get; set; }
public int DemandingEmployeeID { get; set; }
public int RequestedEmployeeID { get; set; }

public virtual Employee DemandingEmployee { get; set; }
public virtual Employee RequestedEmployee { get; set; }

Change your context:

modelBuilder.Entity<Task>()
     .HasRequired(m => m.DemandingEmployee)
     .WithMany(m => m.DemandingTasks)
     .HasForeignKey(m => m.DemandingEmployeeID)
     .WillCascadeOnDelete(false);

modelBuilder.Entity<Task>()
     .HasRequired(m => m.RequestedEmployee)
     .WithMany(m => m.RequestedTasks)
     .HasForeignKey(m => m.RequestedEmployeeId)
     .WillCascadeOnDelete(false);

modelBuilder.Entity<Task>()
    .HasRequired(m => m.UpdatedEmployee)
    .WithMany(m => m.UpdatedTasks)
    .HasForeignKey(m => m.UpdatedEmployeeID)
    .WillCascadeOnDelete(false);

---

Related Topics