0

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:

  1. CustomerOrders and Offers tables and their historical counterparts have a Project_Id column generated. I have found no way how to force the use of the Id primary key or the commented ProjectId properties (I prefer the use of the commented ProjectId properties).
  2. ProjectHistoricals table has CustomerOrder_Id and Offer_Id columns generated. I have found no way how to force the use of the Id column (please note, that the Id column is not a primary key on historical tables) or the commented CustomerOrderId and OfferId columns (on both Project and ProjectHistorical classes; I prefer the use of the commented CustomerOrderId and OfferId 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);
alik
  • 2,244
  • 3
  • 31
  • 44
  • 1
    Is your data model correct? A one to one relationship generally implies that the two tables should be consolidated into one table. – Greg the Incredulous Nov 25 '14 at 22:52
  • I am aware that this should be the same from the theory perspective. However, from the business perspective, Project, Offer and CustomerOrder is a distinct thing. You can imagine that the Project is a folder which contains several documents. Another imagination is that the Project is a workflow and the existence of other models says at which step you are in the process (First, you create an offer to a customer, than you receive an order from the customer, next you ...). – alik Nov 25 '14 at 22:57

0 Answers0