title: EF Code First: Defining foreign keys
body: <p>I've looked at some other answers and lots of articles but this simple part eludes me still.  I'm using EF code first 4.1 but am happy to move to a newer version if it makes it easier.  I have a main fact table like so:</p>
  
  <pre><code>namespace Spend.Models
  {
      public class ExpenseItem
      {
          [Key]
          public String UniqueID_ERLineID { get; set; }
          public String ERNum { get; set; }
          public String ItemNum { get; set; }
          public String Parent_Expense_Item { get; set; }
          public String Card_Number { get; set; }
  ...
  </code></pre>
  
  <p>and several tables hanging off that which have many to one relationships with the ExpenseItems:</p>
  
  <pre><code>public class ExpenseItemAccounting
  {
      [Key]
      public String UniqueID_Accounting { get; set; }
      public String ERLineID { get; set; }
      public String ERNum { get; set; }
      public String ItemNum { get; set; }
  </code></pre>
  
  <p>As we can see, the ERLineID in the second table joins to UniqueID_ErLineID in the first, so the 'conventions' I normally rely on don't work.  So I need to use a virtual ICollection - but I'd like it to specify these fields as the link. Any assistance on how to do this appreciated.</p>
  
  <p>PS. I can't rename the DB fields at present.</p>
  
  <p>@LUKE:</p>
  
  <p>I applied the changes you mention, they make sense.  I get the following error however:</p>
  
  <pre><code>System.Data.Entity.ModelConfiguration.ModelValidationException occurred
    Message=One or more validation errors were detected during model generation:
  
      System.Data.Edm.EdmAssociationType: : Multiplicity conflicts with the referential constraint in Role 'ExpenseItemAccounting_ExpenseItem_Target' in relationship 'ExpenseItemAccounting_ExpenseItem'. Because all of the properties in the Dependent Role are non-nullable, multiplicity of the Principal Role must be '1'.
      System.Data.Edm.EdmAssociationEnd: : Multiplicity is not valid in Role 'ExpenseItemAccounting_ExpenseItem_Source' in relationship 'ExpenseItemAccounting_ExpenseItem'. Because the Dependent Role refers to the key properties, the upper bound of the multiplicity of the Dependent Role must be �1�.
  
    Source=EntityFramework
    StackTrace:
         at System.Data.Entity.ModelConfiguration.Edm.EdmModelExtensions.ValidateAndSerializeCsdl(EdmModel model, XmlWriter writer)
         at System.Data.Entity.DbModelBuilder.Build(DbProviderManifest providerManifest, DbProviderInfo providerInfo)
         at System.Data.Entity.DbModelBuilder.Build(DbConnection providerConnection)
         at System.Data.Entity.Internal.LazyInternalContext.CreateModel(LazyInternalContext internalContext)
         at System.Data.Entity.Internal.RetryLazy`2.GetValue(TInput input)
         at System.Data.Entity.Internal.LazyInternalContext.InitializeContext()
         at System.Data.Entity.Internal.InternalContext.GetEntitySetAndBaseTypeForType(Type entityType)
         at System.Data.Entity.Internal.Linq.InternalSet`1.Initialize()
         at System.Data.Entity.Internal.Linq.InternalSet`1.get_InternalContext()
         at System.Data.Entity.Infrastructure.DbQuery`1.System.Linq.IQueryable.get_Provider()
         at System.Linq.Queryable.SelectMany[TSource,TCollection,TResult](IQueryable`1 source, Expression`1 collectionSelector, Expression`1 resultSelector)
         at EmailClient.Prog.getData() in C:\MF\Dropbox\Dev_LN_Projects\04_QA\EmailClient\EmailClient\Prog.cs:line 172
    InnerException: 
  </code></pre>
  
  <p>This came when I tried the following linq query:</p>
  
  <pre><code>        var geee = (from e in db.ExpenseItems
                          from f in db.ExpenseItemFbtItems
                          where
                          e.Item_Transaction_Date.Value.Year == 2011 &amp;&amp;
                          e.Item_Transaction_Date.Value.Month == 8
                          select new { A = e.UniqueID_ERLineID, B = f.ERLineID.First() });
  </code></pre>
  
  <p>I actually expected to be able to say e.ExpenseItemAccounting.ItemNum or something like that - do I need to put something in the ExpenseItem definition to achieve this?</p>
  
  <p>My model is setup with the following.  The base.OnModelCreating appeared via intellisense and I've tried it with/without it for the same result:</p>
  
  <pre><code>public class SpendDB : DbContext
  {
      public DbSet&lt;ExpenseAttachment&gt; ExpenseAttachments {get; set; }
      public DbSet&lt;ExpenseComment&gt; ExpenseComments {get; set; }
      public DbSet&lt;ExpenseItemAccounting&gt; ExpenseAccountings {get; set; }
      public DbSet&lt;ExpenseItemFbtItem&gt; ExpenseItemFbtItems {get; set; }
      public DbSet&lt;ExpenseItem&gt; ExpenseItems {get; set; }
      public DbSet&lt;ExpenseItemViolation&gt; ExpenseItemViolations {get; set; }
  
      protected override void OnModelCreating(DbModelBuilder modelBuilder)
      {
          base.OnModelCreating(modelBuilder);
          modelBuilder.Entity&lt;ExpenseItemAccounting&gt;().HasOptional(e =&gt; e.ExpenseItem).WithMany().HasForeignKey(e =&gt; e.UniqueID_Accounting);
      }
  }
  </code></pre>
  
  <p>Perhaps I need to put the virtual ICollection in the ExpenseItem definition?  Or is it perhaps the other way around - say modelBuilder.Entity has optional ExpenseItemAccounting?  That sounds more intuitive to me but I'm (clearly) not very good at this so take it with a grain of salt!</p>
  
  <p>Thanks again</p>