4

Is there a way to map two entities to have one to one relationship optional on both sides using fluent API in Entity Framework 6?

Code example:

// Subscription (has FK OrderId)
this.HasOptional(t => t.Order)
    .WithOptionalDependent(t => t.Subscription)
    .HasForeignKey(d => d.OrderId); // does not compile

Context: why would I do this? I work in an existing system where there are payment orders to buy subscriptions. When a order get paid a subscription is created and associated whit it, meaning subscription is optional to order. Also, there are other ways to create subscriptions, meaning order is optional to subscription.

Filipe Borges
  • 2,712
  • 20
  • 32
  • 1
    In the past, it wasn't possible (see [this](http://stackoverflow.com/a/10108574/4812586)), but maybe you can try [this](http://stackoverflow.com/questions/14701378/implementing-zero-or-one-to-zero-or-one-relationship-in-ef-code-first-by-fluent) – jjj May 22 '15 at 17:11
  • There's a [lot to say about this.](http://stackoverflow.com/q/21889367/861716) – Gert Arnold May 22 '15 at 21:15

2 Answers2

1

Usually in an one-to-one (or zero) relationship both entities shares the same PK and, in the dependent one, the PK is also specified as FK. Check this link for more info about this. But if you entities not share the same PK, then you can't add a FK property in the dependent entity. If you do that, EF will throw an exception related with the multiplicity saying that it must be *.

About the relationship's configuration, there is only one way to configure an one-to-one relationship with both sides as optional, which it is what you currently have using Fluent Api. This way you can also use the Map method to rename the FK column that EF create by convention in the dependent table by the name that you already have in the Subscription table in your DB.

Update

If you were not tied to an existing database, you could do something like this:

public class Subscription
{
    public int SubscriptionId { get; set; }

    public int? OrderId { get; set; }
    public virtual Order Order { get; set; }
}

public class Order
{
    public int OrderId { get; set; }
    public int SubscriptionId { get; set; }

    public virtual Subscription Subscription { get; set; }
}

And the configuration would be this:

 modelBuilder.Entity<Subscription>()
            .HasOptional(s => s.Order)
            .WithMany()
            .HasForeignKey(s=>s.OrderId);

 modelBuilder.Entity<>(Order)
            .HasOptional(s => s.Subscription)
            .WithMany()
            .HasForeignKey(s=>s.SubscriptionId);

This way you can work with the OrderIdFK (and SubscriptionId too) like it was a one-to-one relationship. The problem here is you have to set and save both associations separately.

ocuenca
  • 38,548
  • 11
  • 89
  • 102
  • Using `Map` I am not allowed to define a property OrderId to handle the FK, so I will have to change a lot of code that uses `subscription.OrderId` in linq queries. Is there a workaround for it? Thanks for your help. – Filipe Borges May 22 '15 at 19:53
  • Are you sure you have an one to one relationship? It would be helpful if I could see the diagram of both tables, – ocuenca May 22 '15 at 20:09
  • quoting myself: "Context: why would I do this? I work in an existing system where there are payment orders to buy subscriptions. When a order get paid a subscription is created and associated whit it, meaning subscription is optional to order. Also, there are other ways to create subscriptions, meaning order is optional to subscription" – Filipe Borges May 22 '15 at 20:12
  • And business defined: one subscription for one order (if any), one order after payment, generates one subscription. – Filipe Borges May 22 '15 at 20:17
  • The ER is like this: `[Subscription] 0..1 ----- 0..1 [Order]` – Filipe Borges May 22 '15 at 20:21
  • If you really have an one-to-one relationship, in this case it is not possible specify a FK in the dependent entity. There are other ways to accomplish what you want but the problem is you are tied to a scheme that already have in BD. – ocuenca May 22 '15 at 20:32
  • Yeah, thanks for your help. Unfortunately I cannot accomplish what I want. – Filipe Borges May 22 '15 at 20:39
0

Kindly try this code in the database context class

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Subscription>()
            .HasOptional(x => x.Order)
            .WithOptionalPrincipal()
            .Map(x => x.MapKey("SubscriptionId"));

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

    }

My test models are as follows

public class Order
{
    [Key]
    public int OrderId { get; set; }
    public string Description { get; set; }

    public virtual Subscription Subscription { get; set; }
}
public class Subscription
{
    [Key]
    public int SubscriptionId { get; set; }

    public virtual Order Order { get; set; }
}

Edit: I did a reverse engineering to database trying to reach the required code structure by using double 1 to many relation to work like you want. The generated code is like the following. However, It is bad idea to do so.

public partial class Order
{
    public Order()
    {
        this.Subscriptions = new List<Subscription>();
    }

    public int OrderId { get; set; }
    public string Description { get; set; }
    public Nullable<int> SubscriptionId { get; set; }
    public virtual Subscription Subscription { get; set; }
    public virtual ICollection<Subscription> Subscriptions { get; set; }
}
public partial class Subscription
{
    public Subscription()
    {
        this.Orders = new List<Order>();
    }

    public int SubscriptionId { get; set; }
    public Nullable<int> OrderId { get; set; }
    public virtual ICollection<Order> Orders { get; set; }
    public virtual Order Order { get; set; }
}
public class OrderMap : EntityTypeConfiguration<Order>
{
    public OrderMap()
    {
        // Primary Key
        this.HasKey(t => t.OrderId);

        // Properties
        // Table & Column Mappings
        this.ToTable("Orders");
        this.Property(t => t.OrderId).HasColumnName("OrderId");
        this.Property(t => t.Description).HasColumnName("Description");
        this.Property(t => t.SubscriptionId).HasColumnName("SubscriptionId");

        // Relationships
        this.HasOptional(t => t.Subscription)
            .WithMany(t => t.Orders)
            .HasForeignKey(d => d.SubscriptionId);

    }
}
public class SubscriptionMap : EntityTypeConfiguration<Subscription>
{
    public SubscriptionMap()
    {
        // Primary Key
        this.HasKey(t => t.SubscriptionId);

        // Properties
        // Table & Column Mappings
        this.ToTable("Subscriptions");
        this.Property(t => t.SubscriptionId).HasColumnName("SubscriptionId");
        this.Property(t => t.OrderId).HasColumnName("OrderId");

        // Relationships
        this.HasOptional(t => t.Order)
            .WithMany(t => t.Subscriptions)
            .HasForeignKey(d => d.OrderId);

    }
}
public partial class EFOrdersContextContext : DbContext
{
    static EFOrdersContextContext()
    {
        Database.SetInitializer<EFOrdersContextContext>(null);
    }

    public EFOrdersContextContext()
        : base("Name=EFOrdersContextContext")
    {
    }

    public DbSet<Order> Orders { get; set; }
    public DbSet<Subscription> Subscriptions { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Configurations.Add(new OrderMap());
        modelBuilder.Configurations.Add(new SubscriptionMap());
    }
}
Muhammad Nagy
  • 216
  • 1
  • 7
  • It does not work, entity framework complains: Subscription_Order_Source: : Multiplicity is not valid in Role 'Subscription_Order_Source' in relationship 'Subscription_Order'. Because the Dependent Role properties are not the key properties, the upper bound of the multiplicity of the Dependent Role must be '*'. – Filipe Borges May 22 '15 at 16:43
  • It works only if you hide `OrderId` pk, something I cant. Anyway, thanks for your help. – Filipe Borges May 22 '15 at 17:40