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:
CustomerOrders
andOffers
tables and their historical counterparts have aProject_Id
column generated. I have found no way how to force the use of theId
primary key or the commentedProjectId
properties (I prefer the use of the commentedProjectId
properties).ProjectHistoricals
table hasCustomerOrder_Id
andOffer_Id
columns generated. I have found no way how to force the use of theId
column (please note, that theId
column is not a primary key on historical tables) or the commentedCustomerOrderId
andOfferId
columns (on bothProject
andProjectHistorical
classes; I prefer the use of the commentedCustomerOrderId
andOfferId
columns).
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);