Search code examples
c#entity-framework-4ef-code-first

EF Code First: Defining foreign keys

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:

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; }
...

and several tables hanging off that which have many to one relationships with the ExpenseItems:

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; }

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.

PS. I can't rename the DB fields at present.

@LUKE:

I applied the changes you mention, they make sense. I get the following error however:

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:

This came when I tried the following linq query:

        var geee = (from e in db.ExpenseItems
                        from f in db.ExpenseItemFbtItems
                        where
                        e.Item_Transaction_Date.Value.Year == 2011 &&
                        e.Item_Transaction_Date.Value.Month == 8
                        select new { A = e.UniqueID_ERLineID, B = f.ERLineID.First() });

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?

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:

public class SpendDB : DbContext
{
    public DbSet<ExpenseAttachment> ExpenseAttachments {get; set; }
    public DbSet<ExpenseComment> ExpenseComments {get; set; }
    public DbSet<ExpenseItemAccounting> ExpenseAccountings {get; set; }
    public DbSet<ExpenseItemFbtItem> ExpenseItemFbtItems {get; set; }
    public DbSet<ExpenseItem> ExpenseItems {get; set; }
    public DbSet<ExpenseItemViolation> ExpenseItemViolations {get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
        modelBuilder.Entity<ExpenseItemAccounting>().HasOptional(e => e.ExpenseItem).WithMany().HasForeignKey(e => e.UniqueID_Accounting);
    }
}

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!

Thanks again

Solution

  • Do this:

    public class ExpenseItemAccounting
{
    [Key]
    public String UniqueID_Accounting { get; set; }
    public ExpenseItem ExpenseItem{get;set;}
    public String ERLineID { get; set; }
    public String ERNum { get; set; }
    public String ItemNum { get; set; }
}

    Then in your modelBuilder use

    modelBuilder.Entity<ExpenseItemAccounting>()
    .HasOptional(e => e.ExpenseItem).WithMany()
    .HasForeignKey(e => e.UniqueID_Accounting );

    EDIT:

    To configure the navigation property to have a collection on the other end you can simply add it like this

    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; }
        public ICollection<ExpenseItemAccounting> ExpenseItemAccountings{ get; set; }
}

    then wire it up by modifying the model builder config as follows:

     modelBuilder.Entity<ExpenseItemAccounting>()
     .HasOptional(e => e.ExpenseItem).WithMany(e=> e.ExpenseItems)
     .HasForeignKey(e => e.UniqueID_Accounting );

    This will wire it up so that the ExpenseItem will have a list of all child ExpensItemAccounting's, you could also add a singular version of this if that makes more sense such as:

     public class ExpenseItem
    {
            [Key]
            public String UniqueID_ERLineID { get; set; }
            public String ERNum { get; set; }
            public String ItemNum { get; set; }
            public String Card_Number { get; set; }
            public ExpenseItemAccounting Parent_Expense_Item { get; set; }
    }

    and using the modelBuilder for config:

    modelBuilder.Entity<ExpenseItemAccounting>()
    .HasOptional(e => e.ExpenseItem)
    .WithOptionalDependent(e=>e.Parent_Expense_Item);

    i think if you also want the FK references wired up (not just the navigation properties) you need to do this in a seperate statement but thats a bit more fiddly.

    Take a look at the MSDN page on navigation properties and how to use the modelBuilder as its got lots of good examples of advanced stuff.

    http://msdn.microsoft.com/en-us/library/hh295843(v=vs.103).aspx