I am trying to create a composite key with all FKs. For uniqueness I need 4 properties. I've added two, but the third FK and fourth FK throw errors since they are from the same table and I am not sure how to set that up?
I've read many answers and I am just not seeing how to write it out. I am working on an ASP.Net MVC web app with Entity Framework and I've looked into these:
Defining multiple Foreign Key for the Same table in Entity Framework Code First
Entity Framework Code First - two Foreign Keys from same table
https://febdev.wordpress.com/2013/01/30/entity-framework-mapping-references-with-composite-keys/
Entity Framework Code First One to One relationship on composite key
One problem is that I never worked with modelbuilder, so an answer I got from another site was just links, but that didn't help me much.
public class TradeJournal
{
[Key, Column(Order = 0)]
public int UserID { get; set; }
[ForeignKey("UserID")]
public UserAccount UserAccount { get; set; }
[Key, Column(Order = 1)]
public int AccountNumber { get; set; }
[ForeignKey("AccountNumber")]
public FxAccount FxAccount { get; set; }
[Key, Column(Order = 2)]
[ForeignKey("OpenTime")]
public string OpenTime { get; set; }
public HistoricalTrade Open { get; set; }
[Key, Column(Order = 3)]
[ForeignKey("CloseTime")]
public string CloseTime { get; set; }
public HistoricalTrade Close { get; set; }
}
public class HistoricalTrade
{
[Key, Column(Order = 0)]
public int UserID { get; set; }
[ForeignKey("UserID")]
public UserAccount UserAccount { get; set; }
[Key, Column(Order = 1)]
public int AccountNumber { get; set; }
[ForeignKey("AccountNumber")]
public FxAccount FxAccount { get; set; }
[Key, Column(Order = 2)]
[JsonProperty("openTime")]
public string OpenTime { get; set; }
[Key, Column(Order = 3)]
[JsonProperty("closeTime")]
public string CloseTime { get; set; }
}
The database migration has thrown the above error, or the "OpenTime is not a valid entity. (something like) A key must be a valid entity with a non-abstract getter and setter.
I need the key to be the four properties specified. Thanks.
Edit: I've moved "ForeignKey("OpenTime") and "CloseTime" above the reference property, and the full error I've mentioned comes to play from the title, which is :
One or more validation errors were detected during model generation:
TradeJournal_Close_Target_TradeJournal_Close_Source: : The number of properties in the Dependent and Principal Roles in a relationship constraint must be identical.
EDIT: I tried my hand at fluent API as suggested and I run into a new error : "SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint.
This occurred on db.SaveChanges();
My attempt at Fluent API is this:
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
modelBuilder.Entity<TradeJournal>()
.HasKey(k => new { k.UserID})
.HasRequired(r => r.User);
modelBuilder.Entity<TradeJournal>()
.HasKey(k => new { k.AccountNumber })
.HasRequired(r => r.Account);
modelBuilder.Entity<TradeJournal>()
.HasKey(k => new { k.OpenTime })
.HasRequired(r => r.Open);
modelBuilder.Entity<TradeJournal>()
.HasKey(k => new { k.CloseTime })
.HasRequired(r => r.Close);
}
