none
Basic theory question regarding mapping of foreign keys. RRS feed

  • Question

  • I have a fairly large MVC/EF6 project I've been assigned to maintain.  (No documentation.  Very little EF experience on my part.)  I'm basically trying to copy code to add a few tables and relationships, and getting into all sorts of trouble in the process. 

    In the OnModelCreating method of the DataContext we have a huge number of mapping calls, all much the same pattern.

    modelBuilder.Entity<Table1>().HasRequired(f => f.Table2)
       .WithMany(d => d.Table1)
       .HasForeignKey(f => f.ForeignKeyId);

    In the documentation it states that the HasForeignKey call : Configures the relationship to use foreign key property(s) that are exposed in the object model. If the foreign key property(s) are not exposed in the object model then use the Map method.

    I've been assuming that this sort of mapping statement merely means that I'm telling the system that the foreign key field in Table1 is pointing to a record in Table2.

    What does 'exposed in the object model' actually mean?  Does this mean I need to mark fields in the code or is this referring to the actual data structure of the database itself, that the foreign key must be declared?

    Tuesday, July 19, 2016 8:20 PM

Answers

  • >By telling the EF what the Key is, do you mean annotating the class field with [Key]?

    Yes.  Or configuring the key in the OnModelCreating using the Fluent API.  They do the same thing.

    Currently in that model, without either a Key annotation or an explicit configuration in OnModelCreating the Key is being determined by convention, and EF assumes that the property called "Id" or "Table2Id" is the Key.

    Eg

    using System;
    using System.Collections.Generic;
    using System.Data.Entity;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    
    namespace ConsoleApplication12
    {
        class Table1
        {
            public int Id { get; set; }
            public virtual Table2 Table2 { get; set; }
            public int ForeignKeyId { get; set; }
    
        }
        class Table2
        {
            public int Id { get; set; }
            public int ForeignKeyId { get; set; }
            public virtual ICollection<Table1> Table1 { get; set; }
        }
        class DB : DbContext
        {
            public DbSet<Table1> Table1s { get; set; }
            public DbSet<Table2> Table2s { get; set; }
    
            protected override void OnModelCreating(DbModelBuilder modelBuilder)
            {
                modelBuilder.Entity<Table2>().HasKey(f => f.ForeignKeyId);
    
                modelBuilder.Entity<Table1>().HasRequired(f => f.Table2)
                .WithMany(d => d.Table1)
                .HasForeignKey(f => f.ForeignKeyId);
             
    
                base.OnModelCreating(modelBuilder);
            }
        }
    
        
        class Program
        {
            static void Main(string[] args)
            {
                var db = new DB();
                if (db.Database.Exists())
                    db.Database.Delete();
    
                db.Database.Create();
            }
        }
    }
    

    David


    David http://blogs.msdn.com/b/dbrowne/

    • Marked as answer by B. Chernick Thursday, July 21, 2016 3:48 PM
    Wednesday, July 20, 2016 6:45 PM

All replies

  • >What does 'exposed in the object model' actually mean?

    It indicates whether you have a property in your class that maps to the foreign key column.  In the ancient versions of EF the default was to omit the FK columns from the object model.

    So EF supports two different kinds of relationships "Independent Associations" which don't expose the Foreign Key column in an object property, and "Foreign Key Associations" which do.

    See

    Associations with the EF Designer

    The database always needs Foreign Key columns, whether you see them in .NET is up to you (as a best-practice should always see them).

    David


    David http://blogs.msdn.com/b/dbrowne/


    Tuesday, July 19, 2016 8:26 PM
  • What does 'exposed in the object model' actually mean?  Does this mean I need to mark fields in the code or is this referring to the actual data structure of the database itself, that the foreign key must be declared?

    If you were using EF's DB First approach, then there is a checkbox setting that will expose all primary and foreign-key properties in all objects  on the virtual object model. In that way, you can set the foreign keys yourself as you persist the parent and child objects.

    https://en.wikipedia.org/wiki/Object-relational_mapping

    <copied>

    This creates, in effect, a "virtual object database" that can be used from within the programming language.

    <end>

    Tuesday, July 19, 2016 9:25 PM
  • Thanks for your prompt response.  The problem here is that I had no hand in the design and there is no graphic design file (edmx?) in the system.  I'm not quite sure how the contractors created this thing.  All I know is that there is a BusinessLogic project with a DataContext file and the system as a whole makes use of AutoMap and Ninject if that's any help.   Each data table (more or less) has a related class.  The relationships are set up (so far) with a virtual declaration in the parent class and a virtual ICollection declaration in the child class.  Then there's those context declarations in the OnModelCreating that I mentioned.

    So in a situation like this are you saying that I should put a [ForeignKey] attribute on the field in the child class or actually implement a foreign key relationship in the database, or both?  (At this point I'm wondering if somebody deleted some of the relationships in the database.  Still looking into that.)

    Perhaps an alternate question to ask would be: how important are the actual physical foreign key relationships in the database to EF design?

    Wednesday, July 20, 2016 1:15 AM
  • >So in a situation like this are you saying that I should put a [ForeignKey] attribute on the field

    No.  The Fluent API configuration in OnModelCreating are an alternative to using the [ForeignKey] attributes on the entity type properties.

    >or actually implement a foreign key relationship in the database

    Why is the relationship not enforced in the database?  It normally would be, but it's not a requirement.

    >how important are the actual physical foreign key relationships in the database to EF design?

    In the Database-First workflows EF doesn't care, but your _should_ enforce the relationships in the database if possible.

    David


    David http://blogs.msdn.com/b/dbrowne/


    Wednesday, July 20, 2016 2:20 AM
  • Thanks.  You've removed some uncertainty but my code is still not working.

    Given my setup:

    modelBuilder.Entity<Table1>().HasRequired(f => f.Table2)
       .WithMany(d => d.Table1)
       .HasForeignKey(f => f.ForeignKeyId);

    Plus the statementpublic virtual Table2 Table2 { get; set; } in the Table1 class and the statement

    public virtual ICollection<Table1> Table1 { get; set; }   in the Table2 class.

    It looks to me like the Table1 ForeignKeyId is matching to the Id of Table2, not its ForeignKeyId field.

    This pattern is nearly universal in this system.   I still can't see what I'm missing.


    • Edited by B. Chernick Wednesday, July 20, 2016 12:26 PM Spelling error
    Wednesday, July 20, 2016 12:26 PM
  • Here's an example:

    using System;
    using System.Collections.Generic;
    using System.Data.Entity;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    
    namespace ConsoleApplication12
    {
        class Table1
        {
            public int Id { get; set; }
            public virtual Table2 Table2 { get; set; }
            public int Table2Id { get; set; }
    
        }
        class Table2
        {
            public int Id { get; set; }
            public virtual ICollection<Table1> Table1 { get; set; }
        }
        class DB : DbContext
        {
            public DbSet<Table1> Table1s { get; set; }
            public DbSet<Table2> Table2s { get; set; }
    
            protected override void OnModelCreating(DbModelBuilder modelBuilder)
            {
                modelBuilder.Entity<Table1>().HasRequired(f => f.Table2)
                                             .WithMany(d => d.Table1)
                                             .HasForeignKey(f => f.Table2Id);
    
                base.OnModelCreating(modelBuilder);            
            }
        }
        class Program
        {
            static void Main(string[] args)
            {
                var db = new DB();
                db.Database.Create();
            }
        }
    }
    

    David


    David http://blogs.msdn.com/b/dbrowne/

    Wednesday, July 20, 2016 1:11 PM
  • Actually my scenario is more like this. The foreign key is not the identity field of Table 2.

     class Table1
        {
            public int Id { get; set; }
            public virtual Table2 Table2 { get; set; }
            public int ForeignKeyId { get; set; }

        }
        class Table2
        {
            public int Id { get; set; }
            public int ForeignKeyId { get; set; }
            public virtual ICollection<Table1> Table1 { get; set; }
        }
        class DB : DbContext
        {
            public DbSet<Table1> Table1s { get; set; }
            public DbSet<Table2> Table2s { get; set; }

            protected override void OnModelCreating(DbModelBuilder modelBuilder)
            {
                modelBuilder.Entity<Table1>().HasRequired(f => f.Table2)
                                             .WithMany(d => d.Table1)
                                             .HasForeignKey(f => f.ForeignKeyId);

                base.OnModelCreating(modelBuilder);
            }
        }

        class Program
        {

            static void Main(string[] args)
            {
                var db = new DB();      
                db.Database.Create();
            }
        }

    (Late breaking news: After a bit more checking it appears that the type of context declaration I was using only works if you are linking from the foreign key of Table1 to the Id field of Table2.  So as yet I don't know how to declare a foreign key relationship when I need to look at a different field in the child table.)

    • Edited by B. Chernick Wednesday, July 20, 2016 2:56 PM Clarity
    Wednesday, July 20, 2016 1:48 PM
  • This is why I will have nothing to do with Code or Model first and use DB first, as DB first would build the virtual model from the DB schema and be done with it based on what was laid out in the DB schema. :)
    Wednesday, July 20, 2016 4:26 PM
  • I believe I've received a comprehensive answer here in a related post.  https://social.msdn.microsoft.com/Forums/en-US/653e0f1c-e332-453e-abde-4b06c764726d/how-do-i-map-a-foreign-key-field-from-table-to-table-when-neither-is-a-primary-key?forum=adodotnetentityframework

    What I want to do above in EF6 is impossible, as long as the Table2 primary key field is Id, not ForeignKeyId.

    Once again, thanks David.  (Hope I haven't misquoted you.)

    Wednesday, July 20, 2016 5:22 PM
  • >What I want to do above in EF6 is impossible, as long as the Table2 primary key field is Id, not ForeignKeyId.

    Technically it doesn't matter what the"real" Primary Key is.  The only thing that matters here is what you tell EF the Key is.  If you  tell EF the Key is Id then another Entity cannot use ForeignKeyId to reference it.  If you tell EF that the Key is ForeignKeyId then other tables can reference the entity by ForeignKeyId, but not by Id.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Wednesday, July 20, 2016 5:32 PM
  • My apologies if I'm slow (and I promise to do an EF tutorial ASAP, or at least after the next crisis).

    By telling the EF what the Key is, do you mean annotating the class field with [Key]?

    Wednesday, July 20, 2016 6:26 PM
  • >By telling the EF what the Key is, do you mean annotating the class field with [Key]?

    Yes.  Or configuring the key in the OnModelCreating using the Fluent API.  They do the same thing.

    Currently in that model, without either a Key annotation or an explicit configuration in OnModelCreating the Key is being determined by convention, and EF assumes that the property called "Id" or "Table2Id" is the Key.

    Eg

    using System;
    using System.Collections.Generic;
    using System.Data.Entity;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    
    namespace ConsoleApplication12
    {
        class Table1
        {
            public int Id { get; set; }
            public virtual Table2 Table2 { get; set; }
            public int ForeignKeyId { get; set; }
    
        }
        class Table2
        {
            public int Id { get; set; }
            public int ForeignKeyId { get; set; }
            public virtual ICollection<Table1> Table1 { get; set; }
        }
        class DB : DbContext
        {
            public DbSet<Table1> Table1s { get; set; }
            public DbSet<Table2> Table2s { get; set; }
    
            protected override void OnModelCreating(DbModelBuilder modelBuilder)
            {
                modelBuilder.Entity<Table2>().HasKey(f => f.ForeignKeyId);
    
                modelBuilder.Entity<Table1>().HasRequired(f => f.Table2)
                .WithMany(d => d.Table1)
                .HasForeignKey(f => f.ForeignKeyId);
             
    
                base.OnModelCreating(modelBuilder);
            }
        }
    
        
        class Program
        {
            static void Main(string[] args)
            {
                var db = new DB();
                if (db.Database.Exists())
                    db.Database.Delete();
    
                db.Database.Create();
            }
        }
    }
    

    David


    David http://blogs.msdn.com/b/dbrowne/

    • Marked as answer by B. Chernick Thursday, July 21, 2016 3:48 PM
    Wednesday, July 20, 2016 6:45 PM