none
Insert fails with multiple parent entities RRS feed

  • Question

  • There is a data model where one child table (entity) has two parent tables (entities). Relationships between each parent and the child are 1..0 to 1. All three entities have equal primary keys (GUID based). The child's primary key is a foreign key to both parents. All three entities are added to the ObjectContext. When executing ObjectContext.SaveChanges() one parent is not detected, so one insert happens - into one patrent, then the entity framework tries to insert into the child table and this insert fails because insert into the second parent has not happened yet.

    The entity framework needs to understand that there is another parent that has to be inserted before child.

    Question: is there a way to control an insert sequence?

    Thanks,

    Chapaev.


    Simon


    • Edited by Chapaev Monday, March 19, 2012 7:22 PM
    Monday, March 19, 2012 3:28 PM

Answers

  • On 3/21/2012 10:12 AM, Chapaev wrote:
    > This sounds interesting. Do you have code samples for this scenario?
    >
     
    This is something that you should already know. What would you do in
    order to do this with T-SQL?
     
    You would make T-SQL to insert first parent record and use Identity
    Scope to trap on the assigned primary key of the inserted row.
     
    You would make T-SQL to insert second parent record and use Identity
    Scope to trap on the assigned primary-key of the inserted row.
     
    You are holding on to those two primary-keys that are foreign-keys to
    the child.
     
    You would make T-SQL to insert the child and set its foreign-keys to the
    held parent-keys and insert the child.
     
    It's no different in dealing with the parent and child entities with
    inserting them.
     
    You insert the first parent entity and it assigns the primary-key to the
    primary-key property of the entity after the insert. It means that after
    the insert you can address the primary-key property of the inserted
    entity and hold it in a variable.
     
    You do the same thing with the second parent entity.
     
    Now you have the two primary-keys saved in memory.
     
    You populate the child entity foreign-key properties with the held keys
    and you insert the entity.
     This is based on you generating the model and in the model generation
    you tell EF to generate all entities with their primary and foreign-key
    properties.
     
    Wednesday, March 21, 2012 5:49 PM
  • I manually introduced missing entries into edmx file and everything works. So, it is not clear why edmx generator has some irrelevant restrictions.


    Chapaev

    Thursday, March 22, 2012 7:46 PM

All replies

  • Hi Chapaev,

    Welcome!

    I think you can insert child instance which contains parents' relationship:

     using (var db= new EFTestEntities())
                {
                    Guid id = Guid.NewGuid();
                    var p1 = new Parent1 { P1ID = id, Name = "P1" };
                    var p2 = new Parent2 { P2ID = id, Name = "P2" };
                    var c = new Child { CID = id, Name = "Child1", Parent1 = p1, Parent2 = p2 };
                    db.Child.AddObject(c);
                    db.SaveChanges();
                }

    BTW, you should use the same Guid.

    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.

    Tuesday, March 20, 2012 9:02 AM
    Moderator
  • In the case I described all objects are added to the object context with the same GUID (cannot be different because they are primary keys on all three tables and foreign key on the child table). The problem is with the commit sequence. When SaveChanges is executed an insert happens into one parent, then into the child that fails with referential integrity violation message. The second parent insert does not happen. I SQL traced it. The Entity Framework for some reason does not detect the second parent object in the object context, though all relationships are in place.

    Thanks,

    Chapaev.


    Simon

    Tuesday, March 20, 2012 1:44 PM
  •  
    Are you using VS2010 and EF?
     
    Tuesday, March 20, 2012 1:57 PM
  • Yes, VS 2010 and EF 4.0

    Simon

    Tuesday, March 20, 2012 2:02 PM
  • If that's the case and you are using the model first approach, you can
    generate the entities on the model to show primary and foreign key
    properties in the entities.
     
    In this way, you can take complete control of the insert sequence on how
    those parents and children are inserted, because you do the inserts
    manually for parents, get their keys, populate the child and insert it.
    You do that instead of letting EF do it with it trying to create the
    relationships that it can't do in this situation.
     
    EF has some quirks that I have encountered in the past like the one you
    have encountered on child relationships. And you have to take another
    path to accomplish it, because EF can't do it if you let EF try do it
    for you.
     
    Tuesday, March 20, 2012 2:17 PM
  • This sounds interesting. Do you have code samples for this scenario?

    Thanks,

    Chapaev.


    Simon

    Wednesday, March 21, 2012 2:12 PM
  • On 3/21/2012 10:12 AM, Chapaev wrote:
    > This sounds interesting. Do you have code samples for this scenario?
    >
     
    This is something that you should already know. What would you do in
    order to do this with T-SQL?
     
    You would make T-SQL to insert first parent record and use Identity
    Scope to trap on the assigned primary key of the inserted row.
     
    You would make T-SQL to insert second parent record and use Identity
    Scope to trap on the assigned primary-key of the inserted row.
     
    You are holding on to those two primary-keys that are foreign-keys to
    the child.
     
    You would make T-SQL to insert the child and set its foreign-keys to the
    held parent-keys and insert the child.
     
    It's no different in dealing with the parent and child entities with
    inserting them.
     
    You insert the first parent entity and it assigns the primary-key to the
    primary-key property of the entity after the insert. It means that after
    the insert you can address the primary-key property of the inserted
    entity and hold it in a variable.
     
    You do the same thing with the second parent entity.
     
    Now you have the two primary-keys saved in memory.
     
    You populate the child entity foreign-key properties with the held keys
    and you insert the entity.
     This is based on you generating the model and in the model generation
    you tell EF to generate all entities with their primary and foreign-key
    properties.
     
    Wednesday, March 21, 2012 5:49 PM
  • I inspected the edmx file and foud that one of two associations has been excluded from the StorageModels node. Please see the fragment below:

    <!-- Errors Found During Generation:
          warning 6037: Foreign key constraint 'FK_DocumentationImplant_DocumentationImplantMinimum' has been omitted from the storage model. Column 'dcimpId' of table 'ornetModel.Store.DocumentationImplant' is a foreign key participating in multiple relationships. A one-to-one Entity Model will not validate since data inconsistency is possible.
         
          <Association Name="FK_DocumentationImplant_DocumentationImplantMinimum">
            <End Role="DocumentationImplantMinimum" Type="ornetModel.Store.DocumentationImplantMinimum" Multiplicity="1" />
            <End Role="DocumentationImplant" Type="ornetModel.Store.DocumentationImplant" Multiplicity="0..1" />
            <ReferentialConstraint>
              <Principal Role="DocumentationImplantMinimum">
                <PropertyRef Name="diminId" />
              </Principal>
              <Dependent Role="DocumentationImplant">
                <PropertyRef Name="dcimpId" />
              </Dependent>
            </ReferentialConstraint>
          </Association>
    -->

    That is why the database inserts fail. DocumentationImplantMinimum is a second parent for DocumentationImplant that has to be inserted first.

    The entity framework does exactly what it says - it does not validate the second relationship.

    The question is why a valid database scenario is not supported? Is there a way of fixing it?

    Thanks,


    Chapaev

    Thursday, March 22, 2012 6:48 PM
  • I manually introduced missing entries into edmx file and everything works. So, it is not clear why edmx generator has some irrelevant restrictions.


    Chapaev

    Thursday, March 22, 2012 7:46 PM
  • Hi Chapaev,

    >>I manually introduced missing entries into edmx file and everything works.

    Not very sure about your schema, but Edmx file works on my computer:

    <Association Name="FK_Child_Child">
              <End Role="Parent1" Type="EFTestModel.Store.Parent1" Multiplicity="1" />
              <End Role="Child" Type="EFTestModel.Store.Child" Multiplicity="0..1" />
              <ReferentialConstraint>
                <Principal Role="Parent1">
                  <PropertyRef Name="P1ID" />
                </Principal>
                <Dependent Role="Child">
                  <PropertyRef Name="CID" />
                </Dependent>
              </ReferentialConstraint>
            </Association>
            <Association Name="FK_Child_Parent2">
              <End Role="Parent2" Type="EFTestModel.Store.Parent2" Multiplicity="1" />
              <End Role="Child" Type="EFTestModel.Store.Child" Multiplicity="0..1" />
              <ReferentialConstraint>
                <Principal Role="Parent2">
                  <PropertyRef Name="P2ID" />
                </Principal>
                <Dependent Role="Child">
                  <PropertyRef Name="CID" />
                </Dependent>
              </ReferentialConstraint>
            </Association>
    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 Alan_chenModerator Tuesday, April 3, 2012 5:14 AM
    • Unmarked as answer by Chapaev Tuesday, April 3, 2012 1:44 PM
    Monday, March 26, 2012 8:11 AM
    Moderator
  • If it works on your machine it does not mean that everything is right. There is definetely a bug in edmx generator. See my description above. As for now the only way to get around it is to manually edit an edmx file.

    Thanks


    Chapaev

    Tuesday, April 3, 2012 1:49 PM