2 foreign keys in one table using entity structure
I have three classes
Event, Dog, Result
Each dog can participate in many activities and for each event he will receive a rating related to that specialik event. So my result class should contain 2 foreign keys. 1 which identifies the event and 1 which indicates the dog. I am very confused about how to do this. I've already helped with this before and I hope someone can help me:
Here are my classes now:
public class Dog {
public int DogId { get; set; }
public string Name { get; set; }
public int Age { get; set; }
public bool Checked { get; set; }
public string DogImage { get; set; }
[ForeignKey("Event")]
public int EventID { get; set; }
public virtual ICollection<Result> Results { get; set; }
public virtual Event Event { get; set; }
}
public class Event
{
public int EventId { get; set; }
public string EventName { get; set; }
public string EventLocation { get; set; }
public string EventType { get; set; }
public string EventDate { get; set; }
public virtual ICollection<Dog> Dogs { get; set; }
}
public class Result
{
public int ResultId { get; set; }
public int Track { get; set; }
public int Obedience { get; set; }
public int Protection { get; set; }
[ForeignKey("Dog")]
public int DogId { get; set; }
public virtual Dog Dog { get; set; }
}
Help rate, thanks!
EDIT:
Ok, this is how it looks now. I should have said that when I tried to perform a mgration with these updates, I got an error:may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
I added this method to my dbcontext and it took care of this problem (I think ...)
public class EfdbContext : DbContext
{
public DbSet<Event> Events { get; set; }
public DbSet<Dog> Dogs { get; set; }
public DbSet<Result> Results { get; set; }
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Entity<Event>()
.HasRequired(c => c.Results)
.WithMany()
.WillCascadeOnDelete(false);
}
Now when I try to migrate I get this error:
The ALTER TABLE statement was contrary to the FOREIGN KEY constraint "FK_dbo.Events_dbo.Results_Results_ResultId". The conflict occurred in the "CloudDog.EFDB.EfdbContext" database, in the "dbo.Results" table, in the "ResultId" column. This is what my classes look like now:
public class Event
{
public int EventId { get; set; }
public string EventName { get; set; }
public string EventLocation { get; set; }
public string EventType { get; set; } //Dropdown med tävlingar
public string EventDate { get; set; } //Picker
public virtual ICollection<Dog> Dogs { get; set; }
public virtual ICollection<Result> Results { get; set; }
}
public class Dog
{
public int DogId { get; set; }
public string Name { get; set; }
public int Age { get; set; }
public bool Checked { get; set; }
public string DogImage { get; set; }
[ForeignKey("Event")]
public int EventId { get; set; }
public virtual ICollection<Merit> Merits { get; set; }
public virtual ICollection<Result> Results { get; set; }
public virtual Event Event { get; set; }
}
public class Result
{
public int ResultId { get; set; }
public int Track { get; set; }
public int Obedience { get; set; }
public int Protection { get; set; }
[ForeignKey("Dog")]
public int DogId { get; set; }
public virtual Dog Dog { get; set; }
[ForeignKey("Event")]
public int EventId { get; set; }
public virtual Event Event { get; set; }
}
source to share
In your previous question, you didn't tell the whole story. The decision itself was correct. When there is nothing more than a multivalued relationship between two classes, EF can model this over a join table, which remains invisible in the class model.
But now you add additional information to this connection table. This means that the table must be represented as a class in the class model, otherwise you will never be able to access this information. Your model should now look like this (scaled down to the point):
public class Dog
{
public int DogId { get; set; }
public virtual ICollection<Result> Results { get; set; }
}
public class Event
{
public int EventId { get; set; }
public virtual ICollection<Result> Results { get; set; }
}
public class Result
{
public int ResultId { get; set; }
[ForeignKey("Dog")]
public int DogId { get; set; }
public virtual Dog Dog { get; set; }
[ForeignKey("Event")]
public int EventId { get; set; }
public virtual Dog Event { get; set; }
}
Logically, there is a many-to-many relationship between Dog
and Event
, but technically it is implemented as a 1-n-1 association. Thus, by adding information, you are sacrificing access to the shortcut from Dog
to Event
and back. This is very common. I don't often see pure many-to-many associations. Sooner or later, people start storing information about the association.
Now, if you want to get Dog
Event
s, you have to write a slightly more complex query:
from d in Dogs
from r in d.Results
select r.Event
source to share