none
creating new child entities in table per type inheritance scenario RRS feed

  • Question

  • Hi,

     

    I am trying to use Entity Framework 4.0 with a Sql Server 2005 database which has table per type inheritance.

     

    The table structure is as follows

     

    Parent

    [Id PK Identity,

    TypeId,

    Property1 varchar..]

    Parent table has a unique index on Id, TypeId

     

     

    Child1

    [Id PK References Parent.Id,

    TypeId persisted column with a value indicating child1 type,

    Property2 varchar..]

    Child1 table has a composite foreign key on (Id, TypeId) referencing Parent(Id, TypeId)

     

    Child2

    [Id PK References Parent.Id,

    TypeId persisted column with a value indicating child2 type,

    Property3 varchar..]

    Child2 table has a composite foreign key on (Id, TypeId) referencing Parent(Id, TypeId)

     

    The persisted column with the foreign key on child tables are used to enforce that a parent record cannot have a related records in more than one child tables.

     

    I generated the model in EF, deleted the 1 to 0..1 relationship between parent and child entities, created inheritance between parent child entities, deleted Id, and TypeId columns from child entities and mapped the inherited Id and TypeId properties from Parent to corresponding table columns in child entities. When I look at the generated datacontext code, I cannot see entitysets for child entities. All I can see is the parent entityset. How can I insert child entites into the database in this scenario? I am very new to entity framework. Kindly help.

     

    Thank you

     

    Shameer

     

     

     

     

     

    Wednesday, October 5, 2011 5:19 AM

Answers

  • Shameer,
    to tell for sure it would be good to have the DDL and code to repro this. You mentioned that the TypeId column on the child tables is a “persisted” column; do you mean a “persisted computed column”? If so, that might be causing the problem.  You would use an non-computed column with a check constraint to enforce the TypeID.

    You can map update functions to do the update, and write your sprocs so that they don’t actually write the problematic column.

    Another approach that seems to work is to map the TypeId into the Base type’s CSDL rather than into each of the derived classes.   This of course forces you to set the correct explicit value every time.   If you aren’t relying on codegen for your C#, you can make this more or less reasonable by putting the code to set this property in the derived classes’ constructors, and being careful not to treat the TypeId property as readonly (or private).

    You can send your DDL and code to juliako@microsoft.com

    Thank you,

    Julia

     


    This posting is provided "AS IS" with no warranties, and confers no rights.
    • Marked as answer by Shameer A U Wednesday, October 19, 2011 11:16 PM
    Thursday, October 6, 2011 6:04 AM
    Moderator

All replies

  •  

    You would add an instance of your child type to the parent entity set.

    For example, in the following code OnlineCourse inherits from Course. We add the OnlineCourse instance to Courses entity set.

     

           OnlineCourse newCourse = new OnlineCourse()
            { 
                CourseID = courseID,
                DepartmentID = departmentID,
                URL = "http://www.fineartschool.net/Trigonometry",
                Title = "New Onsite Course",
                Credits = 4
            };
    
            // Add the course to the department.
            newDepartment.Courses.Add(newCourse);

     

    thank you,

    Julia


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Wednesday, October 5, 2011 5:56 AM
    Moderator
  • Hi, there is only one set in EF shared among the parent and inherited entities, in your example you'd have one set Parents and it'd let you:

    Parents.Add(new Child1{ ... });
    
    Parents.OfType<Child2>().Single(p => p.Id == 3);
    

     etc.

    please take a look at this thread as well:

    http://social.msdn.microsoft.com/Forums/en-US/adodotnetentityframework/thread/46e84dc1-680c-4aef-97fe-fe14f8afa7c7

     

    and if you are new to Entity Framework I'd strongly recommend reading Julia Lermans book: Building Data-Centric Apps Using ADO.NET Entity Framework

     

    Regards


    Clarity VS Precision
    Wednesday, October 5, 2011 6:04 AM
  • Hi Julia,

    Thanks for your response. I tried the following code

    Child1 c = new Child1();
    c.TypeId = 1;
    c.Property1 = "Parent prperty";
    c.Property2 = "Child property";
               

    TestDBEntities context = new TestDBEntities();
    context.Parents.AddObject(c);
    context.SaveChanges();

    and nothing is saved to the database and no exception is thrown. But I can see the messages below in debug output window.

    A first chance exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll
    A first chance exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll
    A first chance exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll
    A first chance exception of type 'System.Data.UpdateException' occurred in System.Data.Entity.dll

    Any ideas?

    Shameer


    • Edited by Shameer A U Wednesday, October 5, 2011 6:32 AM
    Wednesday, October 5, 2011 6:31 AM
  • Thanks Giorgi. I tried the code you (and Julia as well) suggested and unfortunately it did not work. I did not find an Add method on Parents entity set. I could see only AddObject.

    Shameer

     

    Wednesday, October 5, 2011 6:54 AM
  • Hi, I'm sorry I wrote it in Code First manner, just do this:

    _context.Parents.AddObject(new Child1());

    _context.Parents.OfType<Child1>().Single(p => p.Id == 3);

    and it'll do.

     

    Regards

     


    Clarity VS Precision
    Wednesday, October 5, 2011 7:19 AM
  • I don't think anything is wrong with your EF code. Let me try to research this.
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Wednesday, October 5, 2011 7:46 PM
    Moderator
  • Shameer,
    to tell for sure it would be good to have the DDL and code to repro this. You mentioned that the TypeId column on the child tables is a “persisted” column; do you mean a “persisted computed column”? If so, that might be causing the problem.  You would use an non-computed column with a check constraint to enforce the TypeID.

    You can map update functions to do the update, and write your sprocs so that they don’t actually write the problematic column.

    Another approach that seems to work is to map the TypeId into the Base type’s CSDL rather than into each of the derived classes.   This of course forces you to set the correct explicit value every time.   If you aren’t relying on codegen for your C#, you can make this more or less reasonable by putting the code to set this property in the derived classes’ constructors, and being careful not to treat the TypeId property as readonly (or private).

    You can send your DDL and code to juliako@microsoft.com

    Thank you,

    Julia

     


    This posting is provided "AS IS" with no warranties, and confers no rights.
    • Marked as answer by Shameer A U Wednesday, October 19, 2011 11:16 PM
    Thursday, October 6, 2011 6:04 AM
    Moderator
  • Thanks Julia,

    As you suggested the issue is with the persisited 'computed' column. I changed it to a normal column with a check constraint and it worked fine. I appreciate your help.

    Shameer

     

    Wednesday, October 19, 2011 11:16 PM