none
In code first, can't map entity A to entity B with both reference navigation and collection navigation properties RRS feed

  • Question

  • Imagine this common scenario: you have Person and Address entities. Person can have multiple addresses (Person.Addresses); one address may be designated as the Person's primary address (Person.PrimaryAddress). Every address belongs to one person (this fact is irrelevant but true of the example)

    EF has no problem getting the mapping right Database First using the EDMX. But Code First can't. It generates an Address table with a spurious FK column.

    Here are the CF classes, mapped with attributes

        public class Address
        {
            public int Id { getset; }
     
            [ForeignKey("Person")] // shouldn't be necessary and doesn't help
            public int PersonId { getset; }
            public Person Person { getset; }
        }
     
        public class Person 
        {
            public int Id { getset; }
     
            [ForeignKey("PrimaryAddress")]
            public int? PrimaryAddressId { getset; }
            public Address PrimaryAddress { getset; }
     
            [InverseProperty("Person")] // shouldn't be necessary and doesn't help
            public virtual ICollection<Address> Addresses { getset; }
        }

    [Persons] table is generated with [Id], [PrimaryAddressId] columns as expected.

    But [Addresses] table is generated with 3 columns, not two: [Id], [PersonId], !!![Person_Id]!!!

    Where did [Person_Id] come from? Of course there is no corresponding Address.Person_Id property. The wheels come off.

    Seems to me that EF CF won't associate the "Person.Addresses" collection navigation with the Address.PersonId and creates its own Person_Id instead.

    If I remove the {PrimaryAddress/PrimaryAddressId}, then "Addresses" does connect with the Address.PersonId.

    There is no issue with multiple associations to the same type. I can add as many "Person" reference navigations as I want. For example, I could add a "SecondaryAddress":

            [ForeignKey("SecondaryAddress")]
            public int? SecondaryAddressId { get; set; }
            public Address SecondaryAddress { get; set; }

    The problem arises only when I provide both a reference navigation and a collection navigation:

          public Address PrimaryAddress { get; set; }
          public virtual ICollection<Address> Addresses { get; set; }

    I tried the Fluent API; it did not help. As I said, there is no problem at all if I stick with the EDMX and code generation.

    The scenario I describe is so typical that I'm afraid to proceed with CF until I have some kind of answer.

    Really hoping I'm doing something stupid.


    • Edited by WardB Monday, November 14, 2011 10:48 PM
    Monday, November 14, 2011 10:44 PM

Answers

  • Hi WardB,

    Welcome!

    Please test my code:

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Data.Entity;
    
    namespace ConsoleApplication16
    {
        class Program
        {
            static void Main(string[] args)
            {
                using (var db = new MyContext())
                {
                    db.Database.CreateIfNotExists();
                }
            }
        }
        public class Address
        {
            public int Id { get; set; }
    
            
            public int PersonId { get; set; }
            public Person Person { get; set; }
        }
    
        public class Person
        {
            public int Id { get; set; }
    
           
            public int? PrimaryAddressId { get; set; }
            public Address PrimaryAddress { get; set; }
    
           
            public virtual ICollection<Address> Addresses { get; set; }
        }
        public class MyContext:DbContext
        {
            public DbSet<Address> Addresses { get; set; }
            public DbSet<Person> Persons { get; set; }
            protected override void OnModelCreating(DbModelBuilder modelBuilder)
            {
                modelBuilder.Entity<Address>().HasRequired(a => a.Person).WithMany(p => p.Addresses).HasForeignKey(a => a.PersonId).WillCascadeOnDelete(false);
                modelBuilder.Entity<Person>().HasRequired(a => a.PrimaryAddress).WithMany().HasForeignKey(a => a.PrimaryAddressId).WillCascadeOnDelete(false);
            }
        }
    }
    
    

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by WardB Wednesday, November 16, 2011 2:15 AM
    Tuesday, November 15, 2011 9:16 AM
    Moderator
  • Mr Chen -

    ---

    UPDATE 11/16: The source of my misfortunes appears to have been ... NuGet!  We used NuGet to pull EF into the project. It brought in EF 4.2, the "latest" as I write. Unfortuately, that assembly, referenced in the project, collided with the EF 4.1 (and friends?) in the GAC in the mysterious ways that none of us can fathom. All kinds of misbehavior and mayhem ensued, leading us to flail at the mappings. Even when we had it right (as in fact I did in the code I submitted with this report), we couldn't see it. Sorry to have mislead anyone.

    There are still valuable configuration lessons to be found in this thread. But the key take-away is ... beware of mixing EF versions and beware of NuGet. I love NuGet ... but beware.

    With that out of the way, dear reader, you may yet find material of interest below.

    ---

    There appears to be far more effort in your solution than is actually required once one corrects for a small bug in your rendition of my example. The Person.PrimaryAddress was not required.

    When you made it required, that necessitated the "WillCascadeOnDelete(false)". And since one can't control cascading deletes with an attribute [please fix this in a future release], one is driven to the fluent API.

    Aside: I personally do not favor the default for cascading delete (true). I'm not a fan of cascading deletes and would rather turn them ON when I need them than turn them OFF where I don't want them. In EF, you can reverse that default - set cascade delete false by default - by removing the convention like so:

       modelBuilder.Conventions.Remove<OneToManyCascadeDeleteConvention>();
    

    Once the cascading default issue is off the table, the I was able to get the expected result with just the [InverseProperty] attribute.

    Aside: the weird thing is, that I specified that attribute in the sample that I sent to you in my original problem statement. There is no difference that I can see between the code (below) that now works ... and the code I submitted in support of my question. I fear I was iterating so many times that I just got lost and ... in my frustration ... actually sent you code that would have worked. I'm flummoxed.

    Here is revised code which now works (I commented out the unnecessary lines from your sample and added the [InverseProperty]):

    using System.Collections.Generic;
    using System.ComponentModel.DataAnnotations;
    using System.Data.Entity;
     
    namespace ConsoleApplication16
    {
     
        public class Program
        {
            public Program()
            {
                using (var db = new MyContext())
                {
                    db.Database.CreateIfNotExists();
                }
            }
        }
        public class Address
        {
            public int Id { getset; }
            public int PersonId { getset; }
            //[InverseProperty("Addresses")] // need one InverseProperty; don't need both
            public Person Person { getset; }
        }
     
        public class Person
        {      
            public int Id { getset; }      
            public int? PrimaryAddressId { getset; }
            public Address PrimaryAddress { getset; }
            [InverseProperty("Person")] // need one InverseProperty; don't need both
            public virtual ICollection<Address> Addresses { getset; }
        }
     
        public class MyContext:DbContext
        {
            public DbSet<Address> Addresses { getset; }
            public DbSet<Person> Persons { getset; }
    
            //protected override void OnModelCreating(DbModelBuilder modelBuilder)         //{         //    modelBuilder.Conventions.Remove<OneToManyCascadeDeleteConvention>(); //        //    modelBuilder.Entity<Address>()         //        .HasRequired(a => a.Person)         //        .WithMany(p => p.Addresses)         //        .HasForeignKey(a => a.PersonId);         //    //.WillCascadeOnDelete(false); //         //    modelBuilder.Entity<Person>() // // .HasRequired(a => a.PrimaryAddress)         //        .HasOptional(a => a.PrimaryAddress)         //        .WithMany().HasForeignKey(a => a.PrimaryAddressId);         //    //.WillCascadeOnDelete(false);         //}     } }




    • Marked as answer by WardB Wednesday, November 16, 2011 4:35 AM
    • Edited by WardB Wednesday, November 16, 2011 8:14 AM
    Wednesday, November 16, 2011 4:26 AM

All replies

  • Hi WardB,

    Welcome!

    Please test my code:

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Data.Entity;
    
    namespace ConsoleApplication16
    {
        class Program
        {
            static void Main(string[] args)
            {
                using (var db = new MyContext())
                {
                    db.Database.CreateIfNotExists();
                }
            }
        }
        public class Address
        {
            public int Id { get; set; }
    
            
            public int PersonId { get; set; }
            public Person Person { get; set; }
        }
    
        public class Person
        {
            public int Id { get; set; }
    
           
            public int? PrimaryAddressId { get; set; }
            public Address PrimaryAddress { get; set; }
    
           
            public virtual ICollection<Address> Addresses { get; set; }
        }
        public class MyContext:DbContext
        {
            public DbSet<Address> Addresses { get; set; }
            public DbSet<Person> Persons { get; set; }
            protected override void OnModelCreating(DbModelBuilder modelBuilder)
            {
                modelBuilder.Entity<Address>().HasRequired(a => a.Person).WithMany(p => p.Addresses).HasForeignKey(a => a.PersonId).WillCascadeOnDelete(false);
                modelBuilder.Entity<Person>().HasRequired(a => a.PrimaryAddress).WithMany().HasForeignKey(a => a.PrimaryAddressId).WillCascadeOnDelete(false);
            }
        }
    }
    
    

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by WardB Wednesday, November 16, 2011 2:15 AM
    Tuesday, November 15, 2011 9:16 AM
    Moderator
  • I can confirm that your code works as intended ... even on my machine :).   What I don't know yet is why I was obliged to go to the fluent api and why I couldn't achieve the intended effect with attributes. Any suggestions on that score would be illuminating (I will soldier on myself).  Thanks for your help.
    Wednesday, November 16, 2011 2:14 AM
  • Mr Chen -

    ---

    UPDATE 11/16: The source of my misfortunes appears to have been ... NuGet!  We used NuGet to pull EF into the project. It brought in EF 4.2, the "latest" as I write. Unfortuately, that assembly, referenced in the project, collided with the EF 4.1 (and friends?) in the GAC in the mysterious ways that none of us can fathom. All kinds of misbehavior and mayhem ensued, leading us to flail at the mappings. Even when we had it right (as in fact I did in the code I submitted with this report), we couldn't see it. Sorry to have mislead anyone.

    There are still valuable configuration lessons to be found in this thread. But the key take-away is ... beware of mixing EF versions and beware of NuGet. I love NuGet ... but beware.

    With that out of the way, dear reader, you may yet find material of interest below.

    ---

    There appears to be far more effort in your solution than is actually required once one corrects for a small bug in your rendition of my example. The Person.PrimaryAddress was not required.

    When you made it required, that necessitated the "WillCascadeOnDelete(false)". And since one can't control cascading deletes with an attribute [please fix this in a future release], one is driven to the fluent API.

    Aside: I personally do not favor the default for cascading delete (true). I'm not a fan of cascading deletes and would rather turn them ON when I need them than turn them OFF where I don't want them. In EF, you can reverse that default - set cascade delete false by default - by removing the convention like so:

       modelBuilder.Conventions.Remove<OneToManyCascadeDeleteConvention>();
    

    Once the cascading default issue is off the table, the I was able to get the expected result with just the [InverseProperty] attribute.

    Aside: the weird thing is, that I specified that attribute in the sample that I sent to you in my original problem statement. There is no difference that I can see between the code (below) that now works ... and the code I submitted in support of my question. I fear I was iterating so many times that I just got lost and ... in my frustration ... actually sent you code that would have worked. I'm flummoxed.

    Here is revised code which now works (I commented out the unnecessary lines from your sample and added the [InverseProperty]):

    using System.Collections.Generic;
    using System.ComponentModel.DataAnnotations;
    using System.Data.Entity;
     
    namespace ConsoleApplication16
    {
     
        public class Program
        {
            public Program()
            {
                using (var db = new MyContext())
                {
                    db.Database.CreateIfNotExists();
                }
            }
        }
        public class Address
        {
            public int Id { getset; }
            public int PersonId { getset; }
            //[InverseProperty("Addresses")] // need one InverseProperty; don't need both
            public Person Person { getset; }
        }
     
        public class Person
        {      
            public int Id { getset; }      
            public int? PrimaryAddressId { getset; }
            public Address PrimaryAddress { getset; }
            [InverseProperty("Person")] // need one InverseProperty; don't need both
            public virtual ICollection<Address> Addresses { getset; }
        }
     
        public class MyContext:DbContext
        {
            public DbSet<Address> Addresses { getset; }
            public DbSet<Person> Persons { getset; }
    
            //protected override void OnModelCreating(DbModelBuilder modelBuilder)         //{         //    modelBuilder.Conventions.Remove<OneToManyCascadeDeleteConvention>(); //        //    modelBuilder.Entity<Address>()         //        .HasRequired(a => a.Person)         //        .WithMany(p => p.Addresses)         //        .HasForeignKey(a => a.PersonId);         //    //.WillCascadeOnDelete(false); //         //    modelBuilder.Entity<Person>() // // .HasRequired(a => a.PrimaryAddress)         //        .HasOptional(a => a.PrimaryAddress)         //        .WithMany().HasForeignKey(a => a.PrimaryAddressId);         //    //.WillCascadeOnDelete(false);         //}     } }




    • Marked as answer by WardB Wednesday, November 16, 2011 4:35 AM
    • Edited by WardB Wednesday, November 16, 2011 8:14 AM
    Wednesday, November 16, 2011 4:26 AM