Entity Framework 4.0 Model First inheritance is lost after updating model from database

Question

In my application I'm using Entity Framework 4.0 Model First. I have several tables that implement inheritance, like Product as a basetable and SpecificProduct that inherits from Product.

The inheritance is visible in the edmx file. To initially set up the database, I rightclick on the designer and select "Generate Database from Model", which generates an SQL create script.

Let's assume I do some changes in the SQL database to the SpecificProduct table and want to upgrade the model. Obviously I'd delete the table SpecificProduct from my edmx file and I'd rightclick on the edmx designer and select "Update Model from Database".
This results in the inheritance between Product and SpecificProduct being lost. Instead of the inheritance I do have a 1 to 0..1 relationship and the primary key of Product is now also a column of SpecificProduct.
This is actually not the way I want it to be, because my project won't build anymore, because my code relies on the inheritance being available.

I can manually fix this in the designer file by deleting the inserted primary key column into SpecificProduct, deleting the new 1 to 0..1 relationship, and inserting the inheritance in the edmx designer, again.
Ain't there a way to do this automatically?

Or is this simply a limition of the Model First attempt, that I wasn't aware of (and wouldn't choose again, if this really is a limitation)?

score 3 · Accepted Answer · edited May 23 '17 at 11:43

You must not manually delete anything from EDMX file. Once you delete it your mapping is lost. The inheritance must be always mapped manually because database layer has no knowledge about it. You always start with basic relations which you must delete and change it to inheritance.

So in your case try to simply run update from database without deleting your entity. The new column should be added as a property to your entity. Btw. there is no swapping between model first and database first. Use the first approach or second approach. Combining them is not supported.

score 1 · Answer 2 · answered Jul 04 '12 at 11:39

I was after a solution to this same problem. I managed to achieve a very effective method of applying the inheritance changes by using Text Templates. Here's how...

Create your database

First, create your database as normal except you need to name the foreign key constraints that represent the inheritance in a different way to the other foreign keys.

The naming convention I use for constraints is a two letter prefix like this:

pkPeople           - Primary key constraint
fkPersonAddress    - Foreign key to the Address table
inInstructorPerson - Foreign key representing inheritance
ckPersonAge        - Check constraint

Generate the 'data' entity model

Next, you create a new Entity Data Model and generate it from the database. Don't modify anything at all in this EDMX file, it needs to stay exactly as it was generated. That way, if you ever need to make major changes to the database, you can just delete it and recreate it.

The only thing you need to change is to delete 'EntityModelCodeGenerator' from the Custom Tool property of the edmx file.

Add the Text Template

Then you add a new Text Template (.tt file) to your project. The job of this Text Template is to look through the XML based edmx file created in the previous step and look for all the associations that start with the prefix 'in', and tweak the XML as required to make the entities referenced by the association into inherited objects.

My code for doing this follows. The only thing you'll need to do to make it work for you is to change the hard coded filename of your base EDMX file on line 10.

<#@ template debug="true" hostspecific="true" language="C#" #>
<#@ output extension=".edmx" #>
<#@ assembly name="System.Xml" #>
<#@ assembly name="System.Xml.Linq" #>
<#@ assembly name="System.Core" #>
<#@ import namespace="System.Xml.Linq" #>
<#@ import namespace="System.Linq" #>
<#@ import namespace="System.Collections.Generic" #>
<#
    var edmx = XDocument.Load(this.Host.ResolvePath("MyData.edmx"));
    var edmxns = edmx.Root.Name.Namespace;

    var csdl = edmx.Root.Element(edmxns + "Runtime").Element(edmxns + "ConceptualModels");
    var csdlSchema = csdl.Elements().First();
    var csdlns = csdlSchema.Name.Namespace;
    var modelns = csdlSchema.Attribute("Namespace").Value;
    var InheritiedObjects = new List<InheritedObject>();

    // GET LIST OF INHERITS
    foreach (var a in csdlSchema.Elements(csdlns + "Association").Where(ca => ca.Attribute("Name").Value.StartsWith("in"))) {
        InheritedObject io = new InheritedObject() { ForeignKey = a.Attribute("Name").Value };

        try {
            io.QualifiedParent = a.Elements(csdlns + "End").Single(cae => cae.Attribute("Multiplicity").Value == "1").Attribute("Type").Value;
            io.QualifiedChild = a.Elements(csdlns + "End").Single(cae => cae.Attribute("Multiplicity").Value == "0..1").Attribute("Type").Value;
            InheritiedObjects.Add(io);

        } catch {
            Warning("Foreign key '" + io.ForeignKey + "' doesn't contain parent and child roles with the correct multiplicity.");
        }   
    }

    // SET ABSTRACT OBJECTS
    foreach (var ao in InheritiedObjects.Distinct()) {
        WriteLine("<!-- ABSTRACT: {0} -->", ao.Parent);
        csdlSchema.Elements(csdlns + "EntityType")
            .Single(et => et.Attribute("Name").Value == ao.Parent)
            .SetAttributeValue("Abstract", "true");
    }
    WriteLine("<!-- -->");

    // SET INHERITANCE
    foreach (var io in InheritiedObjects) {

        XElement EntityType = csdlSchema.Elements(csdlns + "EntityType").Single(cet => cet.Attribute("Name").Value == io.Child);
        WriteLine("<!-- INHERITED OBJECT: {0} -->", io.Child);

        // REMOVE THE ASSOCIATION SET
        csdlSchema.Element(csdlns + "EntityContainer")
            .Elements(csdlns + "AssociationSet")
            .Single(cas => cas.Attribute("Association").Value == modelns + "." + io.ForeignKey)
            .Remove();
        WriteLine("<!--     ASSOCIATION SET {0} REMOVED -->", modelns + "." + io.ForeignKey);

        // REMOVE THE ASSOCIATION
        csdlSchema.Elements(csdlns + "Association")
            .Single(ca => ca.Attribute("Name").Value == io.ForeignKey)
            .Remove();
        WriteLine("<!--     ASSOCIATION {0} REMOVED -->", io.ForeignKey);

        // GET THE CHILD ENTITY SET NAME
        io.ChildSet = csdlSchema.Element(csdlns + "EntityContainer")
            .Elements(csdlns + "EntitySet")
            .Single(es => es.Attribute("EntityType").Value == io.QualifiedChild)
            .Attribute("Name").Value;

        // GET THE PARENT ENTITY SET NAME
        io.ParentSet = csdlSchema.Element(csdlns + "EntityContainer")
            .Elements(csdlns + "EntitySet")
            .Single(es => es.Attribute("EntityType").Value == io.QualifiedParent)
            .Attribute("Name").Value;

        // UPDATE ALL ASSOCIATION SETS THAT REFERENCE THE CHILD ENTITY SET
        foreach(var a in csdlSchema.Element(csdlns + "EntityContainer").Elements(csdlns + "AssociationSet")) {
            foreach (var e in a.Elements(csdlns + "End")) {
                if (e.Attribute("EntitySet").Value == io.ChildSet) e.SetAttributeValue("EntitySet", io.ParentSet);
            }
        }           

        // REMOVE THE ENTITY SET
        csdlSchema.Element(csdlns + "EntityContainer")
            .Elements(csdlns + "EntitySet")
            .Single(es => es.Attribute("EntityType").Value == io.QualifiedChild)
            .Remove();
        WriteLine("<!--     ENTITY SET {0} REMOVED -->", io.QualifiedChild);

        // SET BASE TYPE
        EntityType.SetAttributeValue("BaseType", io.QualifiedParent);
        WriteLine("<!--     BASE TYPE SET TO {0} -->", io.QualifiedParent);

        // REMOVE KEY
        EntityType.Element(csdlns + "Key").Remove();
        WriteLine("<!--     KEY REMOVED -->");

        // REMOVE ID PROPERTY
        EntityType.Elements(csdlns + "Property")
            .Where(etp => etp.Attribute("Name").Value == "ID")
            .Remove();
        WriteLine("<!--     ID PROPERTY REMOVED -->");

        // REMOVE NAVIGATION PROPERTIES THAT REFERENCE THE OLD ASSOCIATION
        List<XElement> NavList = new List<XElement>();
        foreach (var np in csdlSchema.Descendants(csdlns + "NavigationProperty")) {
            if (np.Attribute("Relationship").Value == modelns + "." + io.ForeignKey) {
                WriteLine("<!--     REMOVING NAVIGATION PROPERTY {0} FROM {1} -->", np.Attribute("Name").Value, np.Parent.Attribute("Name").Value);
                NavList.Add(np);

            }
        }
        NavList.ForEach(n => n.Remove());

        // REMOVE NAVIGATION PROPERTIES FROM THE PARENT THAT POINTS TO A FOREIGN KEY OF THE CHILD
        foreach (var np in EntityType.Elements(csdlns + "NavigationProperty")) {
            csdlSchema.Elements(csdlns + "EntityType")
                .Single(cet => cet.Attribute("Name").Value == io.Parent)
                .Elements(csdlns + "NavigationProperty")
                .Where(pet => pet.Attribute("Name").Value == np.Attribute("Name").Value)
                .Remove();
        }

        WriteLine("<!-- -->");
    }

    Write(edmx.ToString());



#>
<#+
    public class InheritedObject : IEquatable<InheritedObject> {
        public string ForeignKey { get; set; }
        public string QualifiedParent { get; set; }
        public string QualifiedChild { get; set; }          
        public string Parent { get { return RemoveNamespace(QualifiedParent); } }
        public string Child { get { return RemoveNamespace(QualifiedChild); } }
        public string ParentSet { get; set; }
        public string ChildSet { get; set; }

        private string RemoveNamespace(string expr) {
            if (expr.LastIndexOf(".") > -1) 
                return expr.Substring(expr.LastIndexOf(".") + 1);
            else
                return expr;
        }

        public bool Equals(InheritedObject other) {
            if (Object.ReferenceEquals(other, null)) return false;
            if (Object.ReferenceEquals(this, other)) return true;
            return QualifiedParent.Equals(other.QualifiedParent);
        }

        public override int GetHashCode() {
            return QualifiedParent.GetHashCode();
        }
    }
#>

Results

The Text Template will create a new .edmx file (as a sub file from the Text Template). This is your final .edmx file that will contain all your entities with the correct inheritance based on how you named your foreign key constraints, which is fully auto-generated.

This is a great solution. It really does cater for real world examples where you can't just change your edmx file and expect to be able to push it to the DB. We have DA's who take care of this. — , Mar 25 '13 at 02:27
@kevinx007: I posted this a while ago so it's a bit out of date. I've since created a new library that I use to create the EDMX v5 file from a SQL Server database connection... it works brilliantly for me. — BG100, Mar 25 '13 at 11:41
Thanks for following up. Would you be able to elaborate a little bit regarding your library based solution. As I am a little hesitant to modify Autogen files (in case a new version of EF comes out and changes things). — , Mar 26 '13 at 02:38
I created a DLL library that contains a function called EDMXGenerator, that I pass in a connection string, and it generates and returns an EDMX XML file based on my database and it's specially named constraints. I call the function from within a simple .tt T4 template, that creates the EDMX model in my project. It takes a fair bit of effort to write the code to generate the XML from scratch, and the version I'm using targets EFv5. To support a newer version I'd have to tweak the code or start again. I've also got it to generate enum types from CHECK constraints. — BG100, Mar 26 '13 at 12:26

Entity Framework 4.0 Model First inheritance is lost after updating model from database

2 Answers2