EF HasOptional WithMany

Question

Im having a problem that i just cant solve by google or searching on stackoverflow.

i have two tables JournalLines and Accounts and i want to reference a account in a journalline (i hate economics) but the reference has to be optional and Account shoulnt have any navigation property to the JournalLine (ideally).

JournalLine:

public class JournalLine : Entity<int>
{
    public int? Account_Id { get; set; }
    public string Desc { get; set; }
    public decimal Credit { get; set; }
    public decimal Debit { get; set; }
    public virtual Account Account { get; set; }
}

_Account:

public class Account : Entity<int>
{
    public string Accid { get; set; }
    public string Desc { get; set; }
    public int VatcodeId { get; set; }
}

And the respective mappings:

public class JournalLineMap : EntityTypeConfiguration<JournalLine>
{
    public JournalLineMap()
    {
        HasKey(k => new { k.Id, k.Account_Id });
        Property(k => k.Id)
            .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
        
        ToTable("attJournalLines");
        HasEntitySetName("JournalLines");
        
        HasOptional(jl => jl.Account)
            .WithMany()
            .HasForeignKey(jl => jl.Account_Id);
    }
}

public class AccountMap : EntityTypeConfiguration<Account>
{
    public AccountMap()
    {
        ToTable("attAccounts");
        HasEntitySetName("Accounts");
    }
}

The error im getting is like:

One or more validation errors were detected during model generation:

tSystem.Data.Entity.Edm.EdmAssociationType: : Multiplicity conflicts with the referential >constraint in Role 'JournalLine_Account_Target' in relationship 'JournalLine_Account'. >Because all of the properties in the Dependent Role are non-nullable, multiplicity of the >Principal Role must be '1'.

This confuses me and i hope some one can shed som light on the problem.

Update

Thanks for the answers it helped me on the way and i got the relation to work by removing the key. Then i got a wierd behavoir when assigning an Account to a JournalLine a duplicate account was created. As it turns out this isnt such weird behaviour since im using a Repository pattern with dependency injection. What i didnt think of was that the context wasnt the same in both repositories so the JournalLinesRepository wasnt tracking the account i fetched from its own repository and therefor thought it smart to just insert it as new entity. This was solved by injecting the same context in the repositories so the item tracking works as expected. Again thankyou for your help.

Answer 1

Ohlin's Answer correctly identifies the issue--you cannot have a compound primary key with a nullable column. This raises some questions about what you're trying to model.

Aside from the primary key it appears that your model would work. In fact I think just removing HasKey(k => new { k.Id, k.Account_Id }); will make things work. If you do that then every JournalLine will have it's own unique ID, and optionally relate to an Account.

This would result in data such as:

| AccountId | Desc         |
| 123456789 | Some Account |

| JournalId | AccountId | Description | Credit | Debit |
|         1 |      null |     Some Tx | 100.00 |   0.0 |
|         2 | 123456789 |  Another Tx |    0.0 |  50.0 |
|         3 | 123456789 |      MoreTx |  10.00 |   0.0 |

On the other hand, if your reason for having an AccountId in your JournalLine key is to imply that each account effectively has its own Journal (which sounds vaguely familiar from my Account classes) then your modeling needs some tweaking--or you possibly need a special "uncategorized" account specifically to handle transactions that aren't applied to a specific account.

Answer 2

By looking at your code it "feels" like HasKey(k => new { k.Id, k.Account_Id }); can be causing the problem since Account_Id is nullable and that's usually not accepted as a key. Try to remove the nullable flag for Account_Id just to see what happens.

Edit: Keys in EF

A key always needs to be unique in it's combination (if you have several columns and keys). That's the basic rule behind why you can't allow null values for key parameters.

You're already adding a key for Account_Id when you specify it as a foreign key further down so why do you want it as a primary key as well? Especially since you allow it to be null?