How to properly set the one-to-one relationship in Entity Framework's code-first with fluent API?
The root model is the Project class. Every project contains exactly one offer. It might also optionally contain one customer order.
The purpose of the historical models is to contain snapshots of the respective non-historical rows (when a Project is added, a new Project is created and than copied to a new ProjectHistorical class, if it is edited, a new ProjectHistorical class is created filled with data of the edited Project).
The historical objects should be AutoMapper friendly. You can consider historical models as a silly, trivial and naive workaround for the absence of temporal databases support in the database engine.
With this configuration, I experience the following difficulties:
CustomerOrdersandOfferstables and their historical counterparts have aProject_Idcolumn generated. I have found no way how to force the use of theIdprimary key or the commentedProjectIdproperties (I prefer the use of the commentedProjectIdproperties).ProjectHistoricalstable hasCustomerOrder_IdandOffer_Idcolumns generated. I have found no way how to force the use of theIdcolumn (please note, that theIdcolumn is not a primary key on historical tables) or the commentedCustomerOrderIdandOfferIdcolumns (on bothProjectandProjectHistoricalclasses; I prefer the use of the commentedCustomerOrderIdandOfferIdcolumns).
I have tried to search a lot and tried even more things but nothing worked as (I would have been) expected.
I have the following models:
public class Project
{
public virtual int Id { get; set; }
// public virtual int OfferId { get; set; }
public virtual Offer Offer { get; set; }
// public virtual int? CustomerOrderId { get; set; }
public virtual CustomerOrder CustomerOrder { get; set; }
}
public class ProjectHistorical
{
public virtual int LogId { get; set; }
public virtual int Id { get; set; }
// public virtual int OfferId { get; set; }
public virtual Offer Offer { get; set; }
// public virtual int? CustomerOrderId { get; set; }
public virtual CustomerOrder CustomerOrder { get; set; }
}
public class CustomerOrder
{
public virtual int Id { get; set; }
// public virtual int ProjectId { get; set; }
public virtual Project Project { get; set; }
}
public class CustomerOrderHistorical
{
public virtual int LogId { get; set; }
public virtual int Id { get; set; }
// public virtual int ProjectId { get; set; }
public virtual Project Project { get; set; }
}
public class Offer
{
public virtual int Id { get; set; }
// public virtual int ProjectId { get; set; }
public virtual Project Project { get; set; }
}
public class OfferHistorical
{
public virtual int LogId { get; set; }
public virtual int Id { get; set; }
// public virtual int ProjectId { get; set; }
public virtual Project Project { get; set; }
}
And the following fluent api (the primary key must be always named Id as all models implements a shared interface which is than consumed by generic parts of the application):
modelBuilder.Conventions.Remove<IdKeyDiscoveryConvention>();
modelBuilder.Conventions.Remove<ForeignKeyAssociationMultiplicityConvention>();
modelBuilder.Conventions.Remove<PrimaryKeyNameForeignKeyDiscoveryConvention>();
modelBuilder.Conventions.Remove<OneToOneConstraintIntroductionConvention>();
modelBuilder.Conventions.Remove<TypeNameForeignKeyDiscoveryConvention>();
modelBuilder.Conventions.Remove<AssociationInverseDiscoveryConvention>();
modelBuilder.Conventions.Remove<ManyToManyCascadeDeleteConvention>();
modelBuilder.Conventions.Remove<OneToManyCascadeDeleteConvention>();
modelBuilder.Entity<CustomerOrder>().HasKey(t => t.Id);
modelBuilder.Entity<CustomerOrderHistorical>().HasKey(t => t.LogId);
modelBuilder.Entity<CustomerOrderHistorical>().HasRequired(t => t.Project);
modelBuilder.Entity<Offer>().HasKey(t => t.Id);
modelBuilder.Entity<OfferHistorical>().HasKey(t => t.LogId);
modelBuilder.Entity<OfferHistorical>().HasRequired(t => t.Project);
modelBuilder.Entity<Project>().HasKey(t => t.Id);
modelBuilder.Entity<Project>().HasRequired(t => t.Offer).WithRequiredPrincipal(t => t.Project);
modelBuilder.Entity<Project>().HasOptional(t => t.CustomerOrder).WithRequired(t => t.Project);
modelBuilder.Entity<ProjectHistorical>().HasKey(t => t.LogId);
modelBuilder.Entity<ProjectHistorical>().HasRequired(t => t.Offer);
modelBuilder.Entity<ProjectHistorical>().HasOptional(t => t.CustomerOrder);
This is how the model is compiled to the database by the Entity Framework:
CreateTable(
"dbo.CustomerOrderHistoricals",
c => new
{
LogId = c.Int(nullable: false, identity: true),
Id = c.Int(nullable: false),
Project_Id = c.Int(nullable: false),
})
.PrimaryKey(t => t.LogId)
.ForeignKey("dbo.Projects", t => t.Project_Id)
.Index(t => t.Project_Id);
CreateTable(
"dbo.Projects",
c => new
{
Id = c.Int(nullable: false, identity: true),
})
.PrimaryKey(t => t.Id);
CreateTable(
"dbo.CustomerOrders",
c => new
{
Id = c.Int(nullable: false, identity: true),
Project_Id = c.Int(nullable: false),
})
.PrimaryKey(t => t.Id)
.ForeignKey("dbo.Projects", t => t.Project_Id)
.Index(t => t.Project_Id);
CreateTable(
"dbo.Offers",
c => new
{
Id = c.Int(nullable: false, identity: true),
Project_Id = c.Int(nullable: false),
})
.PrimaryKey(t => t.Id)
.ForeignKey("dbo.Projects", t => t.Project_Id)
.Index(t => t.Project_Id);
CreateTable(
"dbo.OfferHistoricals",
c => new
{
LogId = c.Int(nullable: false, identity: true),
Id = c.Int(nullable: false),
Project_Id = c.Int(nullable: false),
})
.PrimaryKey(t => t.LogId)
.ForeignKey("dbo.Projects", t => t.Project_Id)
.Index(t => t.Project_Id);
CreateTable(
"dbo.ProjectHistoricals",
c => new
{
LogId = c.Int(nullable: false, identity: true),
Id = c.Int(nullable: false),
CustomerOrder_Id = c.Int(),
Offer_Id = c.Int(nullable: false),
})
.PrimaryKey(t => t.LogId)
.ForeignKey("dbo.CustomerOrders", t => t.CustomerOrder_Id)
.ForeignKey("dbo.Offers", t => t.Offer_Id)
.Index(t => t.CustomerOrder_Id)
.Index(t => t.Offer_Id);