Implementing Zero Or One to Zero Or One relationship in EF Code first by Fluent API

Question

I have two POCO classes:

Order Class:

public class Order
{
    public int Id { get; set; }
    public int? QuotationId { get; set; }
    public virtual Quotation Quotation { get; set; }
    ....
}

Quotation Class:

public class Quotation
{
    public int Id { get; set; } 
    public virtual Order Order { get; set; }
    ....   
}

• Each Order may be made from one or zero quotation, and
• each quotation may cause an order.

So I have an "one or zero" to "one or zero" relation, how can I implement this, in EF Code first by Fluent API?

Answer 1

By changing pocos to:

public class Order
{
    public int OrderId { get; set; }
    public virtual Quotation Quotation { get; set; }
}
public class Quotation
{
    public int QuotationId { get; set; }
    public virtual Order Order { get; set; }
}

and using these mapping files:

public class OrderMap : EntityTypeConfiguration<Order>
{
    public OrderMap()
    {
        this.HasOptional(x => x.Quotation)
            .WithOptionalPrincipal()
            .Map(x => x.MapKey("OrderId"));
    }
}
 
public class QuotationMap : EntityTypeConfiguration<Quotation>
{
    public QuotationMap()
    {
        this.HasOptional(x => x.Order)
            .WithOptionalPrincipal()
            .Map(x => x.MapKey("QuotationId"));
    }
}

we will have this DB(that means 0..1-0..1):

with special thanks to (Vahid Nasiri)

Answer 2

@Masoud's procedure was:

modelBuilder.Entity<Order>()
            .HasOptional(o => o.Quotation)
            .WithOptionalPrincipal()
            .Map(o => o.MapKey("OrderId"));

modelBuilder.Entity<Quotation>()
            .HasOptional(o => o.Order)
            .WithOptionalPrincipal()
            .Map(o => o.MapKey("QuotationId"));

It gives:

By changing the code to:

modelBuilder.Entity<Order>()
            .HasOptional(o => o.Quotation)
            .WithOptionalPrincipal(o=> o.Order);

It gives:

Answer 3

See http://msdn.microsoft.com/en-us/data/jj591620 EF Relationships

An excellent Book http://my.safaribooksonline.com/book/-/9781449317867

Here is a post from developer from Dec 2010. But still relevant http://social.msdn.microsoft.com/Forums/uk/adonetefx/thread/aed3b3f5-c150-4131-a686-1bf547a68804 The above article is a nice summary or the possible combinations here.

A solution where dependant Table has key from Primary table is possible.

If you Want Independent Keys where both are Principals in a PK/FK scenario, i dont think you can do it in Code first with Fluent API. If they share a Key, You are OK. 1:1 optional assumes the dependent uses the key from Primary.

But since you need to save one of the tables before the other. You can check one of the Foreign Keys with code. OR add teh second Foreign to Database after Code first has created it.

You will get close. But EF will complain about conflicting Foreign keys if you want both to be Foreign keys. Essentially the A depends on B depends A EF doesnt like, even if the columns are nullable and technically possible on the DB.

Here use this test program to try it. Just comment in an out the Fluent API stuff to try some options. I could NOT get EF5.0 to work with INDEPENDENT PK/FK 0:1 to 0:1 But of course there are reasonable compromises as discussed.

using System.Data.Entity;
using System.Linq;
namespace EF_DEMO
{
class Program
{
    static void Main(string[] args) {
        var ctx = new DemoContext();
        var ord =  ctx.Orders.FirstOrDefault();
        //. DB should be there now...
    }
}
public class Order
{
public int Id {get;set;}
public string Code {get;set;}
public int? QuotationId { get; set; }   //optional  since it is nullable
public virtual Quotation Quotation { get; set; }
  //....
}
public class Quotation
{
 public int Id {get;set;}
 public string Code{get;set;}
// public int? OrderId { get; set; }   //optional  since it is nullable
 public virtual Order Order { get; set; }
 //...
}
public class DemoContext : DbContext
{
    static DemoContext()
    {
    Database.SetInitializer(new DropCreateDatabaseIfModelChanges<DemoContext>());
    }
    public DemoContext()
        : base("Name=Demo") { }
    public DbSet<Order> Orders { get; set; }
    public DbSet<Quotation> Quotations { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
       modelBuilder.Entity<Order>().HasKey(t => t.Id)
                    .HasOptional(t => t.Quotation)
                    .WithOptionalPrincipal(d => d.Order)
                    .Map(t => t.MapKey("OrderId"));  // declaring here  via MAP means NOT declared in POCO
        modelBuilder.Entity<Quotation>().HasKey(t => t.Id)
                    .HasOptional(q => q.Order)
            // .WithOptionalPrincipal(p => p.Quotation)  //as both Principals
            //        .WithOptionalDependent(p => p.Quotation) // as the dependent
            //         .Map(t => t.MapKey("QuotationId"));    done in POCO.
            ;
    }   
}
}

Answer 4

Adapted from this answer, try this.

First, fix your classes:

public class Order
{
  public int Id {get; set;}
  public virtual Quotation Quotation { get; set; }
  // other properties
}

public class Quotation
{
  public int Id {get; set;}
  public virtual Order Order { get; set; }
  // other properties
}

Then use the fluent API like that:

modelBuilder.Entity<Quotation>()
.HasOptional(quote => quote.Order)
.WithRequired(order=> order.Quotation);

Basically, for 1:1 or [0/1]:[0/1] relationships, EF needs the primary keys to be shared.

Answer 5

public class OfficeAssignment
{
    [Key]
    [ForeignKey("Instructor")]
    public int InstructorID { get; set; }
    [StringLength(50)]
    [Display(Name = "Office Location")]
    public string Location { get; set; }

    public virtual Instructor Instructor { get; set; }
}

The Key Attribute

There's a one-to-zero-or-one relationship between the Instructor and the OfficeAssignment entities. An office assignment only exists in relation to the instructor it's assigned to, and therefore its primary key is also its foreign key to the Instructor entity. But the Entity Framework can't automatically recognize InstructorID as the primary key of this entity because its name doesn't follow the ID or classnameID naming convention. Therefore, the Key attribute is used to identify it as the key:

https://www.asp.net/mvc/overview/getting-started/getting-started-with-ef-using-mvc/creating-a-more-complex-data-model-for-an-asp-net-mvc-application

Answer 6

using DataAnnotations:

public class Order
{
       [Key]
       public int Id {get; set;}

       public virtual Quotation Quotation { get; set; }
}

public class Quotation
{
     [Key, ForeignKey(nameof(Order))]
     public int Id {get; set;}

     public virtual Order Order { get; set; }
}

Answer 7

(Note that this is using EF 6.4.4.)

It's fairly straightforward to specify, as long as you don't want foreign key properties:

modelBuilder
.Entity<Order>()
.HasOptional(o => o.Quotation)
.WithOptionalPrincipal(q => q.Order);

modelBuilder
.Entity<Quotation>()
.HasOptional(q => q.Order)
.WithOptionalDependent(o => o.Quotation);

Notice here the usage of both WithOptionalPrincipal and WithOptionalDependent. This should give you a single foreign key column on the dependent side (Quotation in the example), but with no foreign key properties. If you want the foreign key on the other side, switch "Dependent" and "Principal" around.

(Note that it is not necessary to have both definitions above; WithOptionalDependent will imply the other side is the principal and vice-versa, so you can use only one of them if you wanted, but I find specifying the relationships from both sides helps prevent errors by double declaring things; any conflict will result in a model error to let you know you missed something.)

While there is an index on the foreign key column, the index does not have a unique constraint. While it is possible to add your own unique constraint (which would require a Key IS NOT NULL filter), it doesn't seem to work and you will get exceptions when updating relationships in some cases. I think this is related to the "swapping problem" where EF will perform its updates in separate queries, so enforcing uniqueness would prevent EF from "moving" a key in two steps.

EF seems to handle the association itself internally, without a unique DB constraint:

• On either side, assigning an already used reference results in the other usage of the reference being removed automatically. (So if it is already the case that A1 <=> B1 when you opened the context, and then you write A1 => B2, then A1 <=> B1 is removed and A1 <=> B2 is added, regardless of which side you're on.)
• If you try to create a duplicate key by assigning the same reference more than once, EF will throw an exception saying "multiplicity constraint violation". (So in the same context, you wrote both A1 => B1 and A2 => B1, or some similar conflicting mapping.)
• If you update the DB manually to create a duplicate key situation, when EF encounters this it will throw an exception saying "A relationship multiplicity constraint violation occurred...this is a non-recoverable error."

It does not seem possible in EF6 to map a property to the foreign key column (at least with Fluent API). Attempting to do so results in a non-unique column name exception since it tries to use the same name for both the property and the association separately.

Note also that it is technically incorrect to have two foreign keys (ie: one on both sides). Such an arrangement would actually be two 0..1 to 0..1 associations since there would be nothing to say that keys on both ends should match. This could maybe work if you enforce the relationship some other way, through the UI and/or possibly a database constraint of some kind.

I also notice that there may be a misunderstanding/miscommunication of exactly what a 0..1 to 0..1 association is. What this means, from my understanding and the way EF seems to consider it as well, is that it is a 1 to 1 association that is optional on both sides. So, you can have objects on either side with no relationship. (Whereas a 1 to 0..1 assocation, objects on one side could exist without a relationship, but objects on the other side would always need an object to relate to.)

But 0..1 to 0..1 does not mean that you can have the association travel in one direction and not the other. If A1 => B1, then B1 => A1 (A1 <=> B1). You cannot assign B1 to A1 without also making A1 relate to B1. This is why it is possible for this association to use only a single foreign key. I think some people may be trying to have an association in which this is not true (A1 relates to B1 but B1 does not relate to A1). But that is really not one association but two 0..1 to 0..1 associations.