EF6 One to One or Zero Relationship (Fluent API)

Question

I have such classes (simplicified):

public class Transaction
{
    public int LocalId { get; set; }
    public int MachineId { get; set; }
    public virtual Machine Machine { get; set; }       
    public int? MoneyId { get; set; }
    public virtual TransactionMoney Money { get; set; }
}

public class Machine
{
    public int Id { get; set; }
    public string Name { get; set; }
}

public class TransactionMoney
{
    public int LocalId { get; set; }
    public int MachineId { get; set; }
    public virtual Machine Machine { get; set; }
    public int TransactionId { get; set; }
    public virtual Transaction Transaction { get; set; }
}

I would like to have relationship Transaction 1 <-> 0...1 TransactionMoney where foreign key in Money should be TransactionId and MachineId (connected to transaction's LocalId and MachineId). I need to do this in fluent API.

What I've tried is:

    modelBuilder.Entity<Transaction>()
                .HasOptional(t => t.Money)
                .WithRequired(t => t.Transaction)
                .HasForeignKey() <--- there is no such method

and in other side

modelBuilder.Entity<TransactionMoney>()
    .HasRequired(t => t.Transaction)
    .WithOptional(t => t.Money)
            .HasForeignKey() <--- there is no such method

Well, then `TransactionMoney` should also have a primary key `LocalId, MachineId` that is also a FK to `Transaction`. That's how 1:1 is implemented in EF. — Gert Arnold, Nov 15 '15 at 00:08

Ivan Stoev · Answer 1 · 2015-11-15T13:45:21.783

~~You can use something like this~~

modelBuilder.Entity<TransactionMoney>()
    .HasRequired(t => t.Transaction)
    .WithOptional(t => t.Money)
    .Map(a => a.MapKey("TransactionId", "MachineId"));

It turns out that the design you are targeting cannot be done in EF. The closest I was able to get is as follows. But before I go, there are some things to note. MoneyId field is removed from Transaction. LocalId field is removed from TransactionMoney. Foreign key is specified with data annotation. If any of those is unacceptable, just skip the rest.

Entities:

public class Transaction
{
    public int LocalId { get; set; }
    public int MachineId { get; set; }
    public virtual Machine Machine { get; set; }
    public virtual TransactionMoney Money { get; set; }
}

public class Machine
{
    public int Id { get; set; }
    public string Name { get; set; }
    public virtual ICollection<Transaction> Transactions { get; set; }
    public virtual ICollection<TransactionMoney> Money { get; set; }
}

public class TransactionMoney
{
    public int MachineId { get; set; }
    public virtual Machine Machine { get; set; }
    public int TransactionId { get; set; }
    [ForeignKey("TransactionId,MachineId")]
    public virtual Transaction Transaction { get; set; }
}

Configuration

modelBuilder.Entity<Transaction>()
    .HasKey(t => new { t.LocalId, t.MachineId })
    .HasRequired(t => t.Machine)
    .WithMany(t => t.Transactions)
    .HasForeignKey(t => t.MachineId);

modelBuilder.Entity<TransactionMoney>()
    .HasRequired(t => t.Machine)
    .WithMany(t => t.Money)
    .HasForeignKey(t => t.MachineId);

modelBuilder.Entity<TransactionMoney>()
    .HasKey(t => new { t.TransactionId, t.MachineId })
    .HasRequired(t => t.Transaction)
    .WithOptional(t => t.Money);

I know but I want to use Foreign Key Association, not Independend Association — Lukas, Nov 14 '15 at 14:20
I read that indepdendend association is desired to implementing one-to-one relation in entity framework so I used your way and it works, but in TransactionMoney I have also reference to Machine and it creates another column called Machine_Id. How to make it to use "MachineId" field defined in fluent API? — Lukas, Nov 14 '15 at 15:01

EF6 One to One or Zero Relationship (Fluent API)

1 Answers1