locked
EF 5 RC TPT inheritance mapping issue with one to one relationship RRS feed

  • Question

  • I'm trying to configure a one to one relationship between entities which both have an inheritance hierarchy.

    For the sample, let's considered the following first inheritance chain :

    	[Table("A")]
    	public abstract class A
    	{
    		public Guid ID { get; set; }
    		...
    	}
    
    	[Table("AA")]
    	public class AA : A
    	{
    		...
    	}
    
    	[Table("AB")]
    	public class AB : A
    	{
    		...
    	}

    Then, considered this second inheritance chain :

    	[Table("B")]
    	public abstract class B
    	{
    		public Guid ID { get; set; }
    	}
    
    	[Table("BA")]
    	public class BA : B
    	{
    		...
    	}
    
    	[Table("BB")]
    	public class BB : B
    	{
    		...
    	}

    Add a one to one relationship between AA and BA with AA as principal entity :

    	[Table("AA")]
    	public class AA : A
    	{
    		...
    		public BA BAChild { get; set; }
    		...
    	}
    
    	[Table("BA")]
    	public class BA : B
    	{
    		...
    		public AA Parent { get; set; }
    		...
    	}
    
    	public class AAConfiguration : EntityTypeConfiguration<AA>
    	{
    		public AAConfiguration()
    		{
    			this.HasRequired(o => o.BAChild)
    				.WithRequiredPrincipal(o => o.Parent);
    		}
    	}

    Add a one to one relationship between AB and BB with AB as principal entity :

    	[Table("AB")]
    	public class AB : A
    	{
    		...
    		public BB BBChild { get; set; }
    		...
    	}
    
    	[Table("BB")]
    	public class BB : B
    	{
    		...
    		public AB Parent { get; set; }
    		...
    	}
    
    	public class ABConfiguration : EntityTypeConfiguration<AB>
    	{
    		public ABConfiguration()
    		{
    			this.HasRequired(o => o.BBChild)
    				.WithRequiredPrincipal(o => o.Parent);
    		}
    	}

    I also want that EF generates tables for entities A and B so I have added and registered the following empty EntityTypeConfiguration :

    	public class AConfiguration : EntityTypeConfiguration<A>
    	{
    
    	}
    
    	public class BConfiguration : EntityTypeConfiguration<B>
    	{
    
    	}

    If you run the code like this you will get the bug referenced here (http://stackoverflow.com/questions/10441924/unhandled-exception-after-upgrading-to-entity-framework-4-3-1)...

    So let's do some tricky code and register a custom MigrationSqlGenerator derived from SqlServerMigrationSqlGenerator to avoid index creation based on my business naming rule :

    	public class Configuration : DbMigrationsConfiguration<DataContext>
    	{
    		public Configuration()
    		{
    			...
    			this.SetSqlGenerator("System.Data.SqlClient", new CustomSqlServerGenerator());
    			...
    		}
    	}
    
    	public class CustomSqlServerGenerator : SqlServerMigrationSqlGenerator
    	{
    		protected override void Generate(CreateIndexOperation createIndexOperation)
    		{
    			if (createIndexOperation.Columns.Count() == 1 && createIndexOperation.Columns.Any(o => o == "ID"))
    				return;
    
    			base.Generate(createIndexOperation);
    		}
    	}
    
    	public class DataContext : DbContext
    	{
    		static DataContext()
    		{
    			Database.SetInitializer<DataContext>(new MigrateDatabaseToLatestVersion<DataContext, Configuration>());
    		}
    		
    		...
    	}

    So now it's time to generate the database, to make it, I use the following code :

    	...
    	DataContext dataContext = new DataContext();
    	dataContext.Database.Initialize(true);
    	...

    And now if you look at the generated database you will saw that both table BA and BB has a foreign key for the table AB and that there is no foreign key between BA and AA !?!

    I am probably missing something but I can't see what's wrong with this sample.
    What can be done to generate the databse properly ?

    Thursday, June 28, 2012 3:23 PM

All replies

  • Hi DA Costa Paulo,

    Nice to see you again.

    I will do more research on this issue and come back as soon as possible, thanks for your understanding.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Friday, June 29, 2012 9:17 AM
  • Hi Allen Li,

    Nice to see you again too.

    As you can see I've migrated my project to EF 5 but unfortunately I think that I'm stuck again...

    After doing some deep search throughout reflector it seems that this behavior is an EntityFramework bug.
    I've located the problem in System.Data.Entity.ModelConfiguration.Configuration.Mapping.ForeignKeyPrimitiveOperations in the following function :

            private static void UpdatePrincipalTables(DbDatabaseMapping databaseMapping, DbTableMetadata toTable, bool removeFks, EdmAssociationType associationType, EdmEntityType et)
            {
    			...
            }

    When EntityFramework loads the model, the DbDatabaseMapping instance is loaded with the base types and the foreign key constraints are added to the base types metadatas.
    EntityFramework also store an instance of EdmAssociationType in the annotations of the foreign key constraint to maintain all the required informations to generate the correct constraint.
    Latter, when the derived types are added and configured in the DbDatabaseMapping instance, EntityFramework tries to update the related end of the associations to move the foreign key constraints on the derived tables.
    To identify the foreign key constraints to update the following selector is used where tableInfo.Value is an IEnumerable<DbTableColumnMetadata> containing the dependent columns for the foreign key :

    	fk => fk.DependentColumns.SequenceEqual<DbTableColumnMetadata>(tableInfo.Value);

    Unfortunately, in my case for the relation between AA and BA and for the relation between AB and BB the column "ID" is used.
    So, both of them are updated a first time to replace the principal table metadata by AA metadata and a second time by AB metadata.
    As a consequence, both of the foreign key are generated with AB as principal table !

    I think that the EdmEntityType passed in the function parameters should be used in the selector to be compared to the entity type of the relationship end stored in the foreign key constraint annotations.

    Friday, June 29, 2012 3:28 PM
  • Hi DA Costa Paulo,

    I have tested the code.

    'AA' and 'BA' have a one to one relationship, and 'AA' is the principle, so the PK of 'BA' references to the PK of 'AA'. But, there's an inheritance relationship between 'BA' and 'B', so the PK of 'BA' also references the PK of 'B'. Obviously, it is not sensible. I think the design need to be modified.

    Best Regards 


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Monday, July 2, 2012 6:33 AM
  • Hi Allen Li,

    If you remove AB and BB, you can generate the model and everything work as expected so this is not a design issue...
    In this particular case, A, AA, BA and B will have the same identifier, this is sensible for my business scenario.

    The problem appear only when I add a second one to one relationship for another derived type of B because entity framework get confused when he is trying to put the foreign key on the derived type (read carefully my previous post).

    Best regards

    Monday, July 2, 2012 8:25 AM
  • Hi DA Costa Paulo,

    Eventhough removing AB and BB, the project also throws the exception on index of _BA. Please refer to the code below.

        class Program
        {
            static void Main(string[] args)
            {
                using (TestContext context = new TestContext())
                {
                    context.Database.Create();
                }
            }
        }
    
        [Table("A")]
        abstract class A
        {
            public Guid id { get; set; }
        }
    
        [Table("AA")]
        class AA : A
        {
            public string AAName { get; set; }
            public BA child { get; set; }
        }
    
        [Table("B")]
        abstract class B
        {
            public Guid id { get; set; }
        }
    
        [Table("BA")]
        class BA : B
        {
            public string BAName { get; set; }
            public AA parent { get; set; }
        }
    
        class TestContext : DbContext
        {
            public DbSet<A> ASet { get; set; }
            public DbSet<B> BSet { get; set; }
            protected override void OnModelCreating(DbModelBuilder modelBuilder)
            {
                modelBuilder.Entity<AA>().HasRequired(x => x.child).WithRequiredPrincipal(x => x.parent);
                //modelBuilder.Entity<AB>().HasRequired(x => x.child).WithRequiredPrincipal(x => x.parent);
            }
        }
    

    If you want A, AA, BA and B have the same identifier. B need to derived from A, this is, B's primary key will references A's primary key. Please refer to the code below.

    class Program
        {
            static void Main(string[] args)
            {
                using (TestContext context = new TestContext())
                {
                    context.Database.Create();
                }
            }
        }
    
        [Table("A")]
        abstract class A
        {
            public Guid id { get; set; }
        }
    
        [Table("AA")]
        class AA : A
        {
            public string AAName { get; set; }
            public BA child { get; set; }
        }
    
        [Table("AB")]
        class AB : A
        {
            public string ABName { get; set; }
            public BB child { get; set; }
        }
    
        [Table("B")]
        abstract class B:A
        {       
        }
    
        [Table("BA")]
        class BA : B
        {
            public string BAName { get; set; }
            public AA parent { get; set; }
        }
    
        [Table("BB")]
        class BB : B
        {
            public string BBName { get; set; }
            public AB parent { get; set; }
        }
    
        class TestContext : DbContext
        {
            public DbSet<A> ASet { get; set; }
            public DbSet<B> BSet { get; set; }
            protected override void OnModelCreating(DbModelBuilder modelBuilder)
            {
                modelBuilder.Entity<AA>().HasRequired(x => x.child).WithRequiredPrincipal(x => x.parent);
                modelBuilder.Entity<AB>().HasRequired(x => x.child).WithRequiredPrincipal(x => x.parent);
            }
        }

    Best Regards

    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Wednesday, July 4, 2012 6:34 AM
  • Hi Allen Li,

    Please refer to my first post, you fall on the index creation bug referenced here : http://stackoverflow.com/questions/10441924/unhandled-exception-after-upgrading-to-entity-framework-4-3-1

    This bug should be fixed in EF 5.0 RTM but it's still in EF 5.0 RC, that's why in my first post I gave instructions in order to use migration to register a custom MigrationSqlGenerator to avoid duplicate index creation.

    Please refer to the following code :

        class Program
        {
            static void Main(string[] args)
            {
                using (TestContext context = new TestContext())
                {
                    context.Database.Initialize(true);
                }
            }
        }
    
        [Table("A")]
        abstract class A
        {
            public Guid id { get; set; }
        }
    
        [Table("AA")]
        class AA : A
        {
            public string AAName { get; set; }
            public BA child { get; set; }
        }
    
        [Table("B")]
        abstract class B
        {
            public Guid id { get; set; }
        }
    
        [Table("BA")]
        class BA : B
        {
            public string BAName { get; set; }
            public AA parent { get; set; }
        }
    
        class TestContext : DbContext
        {
            static TestContext()
            {
                Database.SetInitializer<TestContext>(new MigrateDatabaseToLatestVersion<TestContext, Configuration>());
            }
    
            public DbSet<A> ASet { get; set; }
            public DbSet<B> BSet { get; set; }
            
            protected override void OnModelCreating(DbModelBuilder modelBuilder)
            {
                modelBuilder.Entity<AA>().HasRequired(x => x.child).WithRequiredPrincipal(x => x.parent);
            }
        }
    
        class Configuration : DbMigrationsConfiguration<TestContext>
        {
            public Configuration()
            {
                this.AutomaticMigrationsEnabled = true;
                this.AutomaticMigrationDataLossAllowed = true;
    
                this.SetSqlGenerator("System.Data.SqlClient", new CustomSqlServerGenerator());
            }
    
            protected override void Seed(TestContext context)
            {
                base.Seed(context);
            }
        }
    
        class CustomSqlServerGenerator : SqlServerMigrationSqlGenerator
        {
            protected override void Generate(CreateIndexOperation createIndexOperation)
            {
                if (createIndexOperation.Columns.Count() == 1 && createIndexOperation.Columns.Any(o => o == "id"))
                    return;
    
                base.Generate(createIndexOperation);
            }
        }

    Best regards

    Wednesday, July 4, 2012 7:45 AM
  • Hi DA Costa Paulo,

    Yes, I have seen the post on stackoverflow. But I don't think this is a bug for this issue.

    Please look at the table creation code in migrations folder.

    CreateTable(
                    "dbo.BA",
                    c => new
                        {
                            id = c.Guid(nullable: false),
                            BAName = c.String(),
                        })
                    .PrimaryKey(t => t.id)
                    .ForeignKey("dbo.B", t => t.id)
                    .ForeignKey("dbo.AB", t => t.id)
                    .Index(t => t.id)
                    .Index(t => t.id);
                
                CreateTable(
                    "dbo.BB",
                    c => new
                        {
                            id = c.Guid(nullable: false),
                            BBName = c.String(),
                        })
                    .PrimaryKey(t => t.id)
                    .ForeignKey("dbo.B", t => t.id)
                    .ForeignKey("dbo.AB", t => t.id)
                    .Index(t => t.id)
                    .Index(t => t.id);

    The primary key of 'BA' and 'BB' are added index twice, this is because you also let it reference two tables primary key.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Friday, July 6, 2012 2:43 AM
  • Hi DA Costa Paulo,

    Any update about this issue?

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Monday, July 9, 2012 2:36 AM
  • Hi Allen Li,

    I've opened a support ticket last week and I'm waiting for an answer of the Microsoft support team.
    If you read the 3rd post of the Stackoverflow post, the bug is clearly describes by Slauma for a similar model.

    For the main subject of my post, Andrew Peters (team ADO.Net) has let a comment on Stackoverflow :
    "Thanks for reporting this and for the detailed analysis. I have added the bug to our backlog for the EF6 timeframe."

    So I'm waiting for the official answer for my support ticket but I think that I'm going to have 2 options, wait until EF 6 release or move to TPC...

    Best regards

    Monday, July 9, 2012 7:39 AM
  • Hi DA Costa Paulo,

    Thanks for your feedback, I hope you can get a satiesfied solution from Microsoft support team. Also thanks for supporting Entity Framework, I think it will be better and better. : )

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Wednesday, July 11, 2012 2:42 AM