Tricky Entity Framework One-to-One Relationships

Question

I have an application that records entries in various tennis tournaments. He also records who is the winner of each event.

2 classes considered:

public class Event { public int EventId { get; set; } public string EventName { get; set; } public virtual ICollection<Entry> Entries { get; set; } public int? WinningEntryId { get; set; } [ForeignKey("WinningEntryId")] public virtual Entry WinningEntry { get; set; } } public class Entry { public int EntryId { get; set; } public int EventId { get; set; } [ForeignKey("EventId")] public virtual Event Event { get; set; } } 

Please note that the column Event.WinningEntryId is NULL, because I do not want to indicate the winner, because the tournament can still be completed.

Now the problem is that when I run the Update-Database command, it generates the following tables:

 Entry - EntryId - EventId - Event_EventId Event - EventId - WinningEntryId 

Why does EF generate an Event_EventId column in the Event table? I just want the Event.WinningEntryId column to point to the Entry.EntryId column with respect to the foreign key.

I have to somehow do it wrong. Does anyone know how I can do this?

Answer 1

Assuming your navigation property:

 public virtual Entry WinningEntryId { get; set; } 

actually called WinningEntry :

 public virtual Entry WinningEntry { get; set; } 

in fact, it looks like you are modeling a one-to-many relationship, not a one-to-one relationship. An event can contain many records, and a record can belong to one event? It is just that one of these entries will be marked as a winning entry.

I would prefer not to use data annotations and stick to a free API for: a) managing your entire configuration in a central place and b) not confusing persistence problems with domain objects.

In this case, you can customize your mappings using the free API as such:

 protected override void OnModelCreating(DbModelBuilder modelBuilder) { modelBuilder.Entity<Event>() .HasOptional(ev => ev.WinningEntry) .WithMany() .HasForeignKey(ev => ev.WinningEntryId) .WillCascadeOnDelete(false); modelBuilder.Entity<Entry>() .HasRequired(en => en.Event) .WithMany(ev => ev.Entries) .HasForeignKey(en => en.EventId); } 

This will simulate a relationship with:

 Events EventId (PK) EventName WinningEventId (FK, null) Entries EntryId (PK) EventId (FK, not null) 

Answer 2

The marked answer does what you need to do, but the real reason why you got Event_EventId in your table is because of this:

 public virtual ICollection<Entry> Entries { get; set; } 

Marked in your Event object. It just means that the Event is a collection of similar records. And by this logic, a one-to-many relationship is created between the event and the recording.

-Event has many entries

-Entry refers to a single event

Therefore, each record must have an Event_EventId foreign key to indicate which event the record belongs to.