Entity Framework CodeFirst: Foreign Keys without Collection

Question

i'm starting with EF6.1 CodeFirst with and existing batabase. I have the following Tables:

Company
  ID integer
  Code varchar
  Currency_ID integer

Currency
  ID integer
  Company_ID integer
  Code varchar

The concept behind is, that every table contains a reference to the Company_ID. I've created the following classes:

class Company
{
  public int ID { get; set; }
  public string Code { get; set; }
  [ForeignKey("Currency")
  public int? Currency_ID { get; set; }

  public virtual Currency Currency { get; set; }
}

class Currency
{
  public int ID { get; set; }
  public string Code { get; set; }
  [ForeignKey("Company")
  public int? Company_ID { get; set; }

  public virtual Company Company { get; set; }
}

When i use this model, i get the error

Company_Currency_Target: : Multiplicity is not valid in Role 'Company_Currency_Target' in relationship 'Company_Currency'. Because the Dependent Role properties are not the key properties, the upper bound of the multiplicity of the Dependent Role must be '*'.

is there a solution other than adding a ICollection<Currency> CurrencyList to the Company Class?

--- Update ----

Here some Sample Data:

Company ID: 1 Code: XXX Curreny_ID: 100
Company ID: 2 Code: YYY Curreny_ID: 102
Currency ID: 100 Code: EUR Company_ID: 1
Currency ID: 101 Code: DOL Company_ID: 1
Currency ID: 102 Code: EUR Company_ID: 2

--- Update 2 ----

I found a possible solution using the fluent api

modelBuilder.Entity<Company>().HasOptional(q => q.Currency).WithMany().HasForeignKey(q => q.Currency_Id);
modelBuilder.Entity<Currency>().HasRequired(q => q.Company).WithMany().HasForeignKey(q => q.Company_Id);

is there a way to do the same using attirbutes?

Your model is wrong. Can only a single Company use a Currency? If so, how do each company do business with each other? You also, I think, have placed the ForeignKey attribute on the wrong property. It should be the association not the foreign key.msdn.microsoft.com/en-us/data/jj591583#Relationships — Aron
PS What happens when a company is able to work in multiple currencies like multinationals? — Aron
The model should be ok. As i said, every table has a link to the company table. this way every record in the database is associated to a company. every company creates its own currency records and associates one currency to the company. that is the main currency used by the company. — Volkmar Rigo
From what i found, you can put the ForeignKey on the ID property: locked.nl/… — Volkmar Rigo
Can multiple companies have the same currency? And can one company have multiple currencies (of which one is the main one)? — Gert Arnold

mr100 mr100 · Accepted Answer · 2014-06-12T20:36:49

You could set it up with fluent api. First you need to remove Currency_ID and Company_ID properties from your entity classes. In your DbContext class override OnModelCreating method and place there a code:

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Company>()
            .HasOptional(f => f.Currency)
            .WithOptionalDependent()
            .Map(c => c.MapKey("Currency_ID"));

        modelBuilder.Entity<Currency>()
            .HasOptional(f => f.Company)
            .WithOptionalDependent()
            .Map(c => c.MapKey("Company_ID"));

        base.OnModelCreating(modelBuilder);
    }

But take into account that this creates not 1 relationship but 2. So if Company refers to Currency then Currency may refer to other Company. If you want to have only 1 relationship then you should get rid of one of the foreign key columns (in Company or Currency) and configure it like that:

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Company>()
            .HasOptional(f => f.Currency)
            .WithOptionalDependent(f => f.Company)
            .Map(c => c.MapKey("Currency_ID"));

        base.OnModelCreating(modelBuilder);
    }

Entity Framework CodeFirst: Foreign Keys without Collection

1 Answers