none
Mixing TPT,TPH and entity splitting throws exception on inserts RRS feed

  • Question

  • hi

    Tables Contacts and Customers are mapped to entities Contact and Customer. I've combined TPT and TPH mappings using the following steps:

    1 -  Since Customer table provides additional information about a subset of Contacts, I've defined TPT inheritance, where Customer entity derives from Contact entity.

    2- I've then created a new entity called AlsoCustomer, and choose Customer as its base type ( thus AlsoCustomer derives from Customer, which in turn derives from Contact).

    3) I then created TPH inheritance using Customer and AlsoCustomer entities, where conditional mapping determines whether the record is of type Customer or of type AlsoCustomer

    Up to this point everything works fine, but when I try to also implement entity splitting, where Customer entity ( as a reminder, AlsoCustomer   --> Customer  --> Contact ) would be mapped to both Customer and ContactPersonalInfo tables, then I get an exception on inserts:

    System.Data.UpdateException: Conflicting changes detected.This may happen when trying to insert multiple entities with the same key.

    var customer = new  AlsoCustomer();
     ...
    context.Contacts.AddObject(customer);
    context.SaveChanges(); // Exception


    or: 


    var customer = new  Customer();
     ...
    context.Contacts.AddObject(customer);
    context.SaveChanges(); // Exception


    Here is the relevant MSL section:


    <EntitySetMapping Name="Contacts">
                <EntityTypeMapping TypeName="IsTypeOf(BAModel.Contact)">
                  <MappingFragment StoreEntitySet="Contact">
                    <ScalarProperty Name="ContactID" ColumnName="ContactID" />
                    <ScalarProperty Name="RowVersion" ColumnName="RowVersion" />
                    <ScalarProperty Name="ModifiedDate" ColumnName="ModifiedDate" />
                    <ScalarProperty Name="AddDate" ColumnName="AddDate" />
                    <ScalarProperty Name="Title" ColumnName="Title" />
                    <ScalarProperty Name="LastName" ColumnName="LastName" />
                    <ScalarProperty Name="FirstName" ColumnName="FirstName" />
                  </MappingFragment>
                </EntityTypeMapping>
                <EntityTypeMapping TypeName="IsTypeOf(BAModel.AlsoCustomer)" xmlns="http://schemas.microsoft.com/ado/2008/09/mapping/cs">
                  <MappingFragment StoreEntitySet="Customers">
                    <ScalarProperty Name="ContactID" ColumnName="ContactID" />
                    <ScalarProperty Name="CustomerRowVersion" ColumnName="RowVersion" />
                    <ScalarProperty Name="SecondaryActivity" ColumnName="SecondaryActivity" />
                    <ScalarProperty Name="PrimaryActivity" ColumnName="PrimaryActivity" />
                    <ScalarProperty Name="SecondaryDestination" ColumnName="SecondaryDestination" />
                    <ScalarProperty Name="PrimaryDesintation" ColumnName="PrimaryDesintation" />
                    <ScalarProperty Name="InitialDate" ColumnName="InitialDate" />
                    <ScalarProperty Name="CustomerTypeID" ColumnName="CustomerTypeID" />
                    <Condition ColumnName="Notes" Value="agent" />
                  </MappingFragment>
                </EntityTypeMapping>
                <EntityTypeMapping TypeName="BAModel.Customer" xmlns="http://schemas.microsoft.com/ado/2008/09/mapping/cs">
                  <MappingFragment StoreEntitySet="Customers">
                    <ScalarProperty Name="ContactID" ColumnName="ContactID" />
                    <ScalarProperty Name="CustomerRowVersion" ColumnName="RowVersion" />
                    <ScalarProperty Name="SecondaryActivity" ColumnName="SecondaryActivity" />
                    <ScalarProperty Name="PrimaryActivity" ColumnName="PrimaryActivity" />
                    <ScalarProperty Name="SecondaryDestination" ColumnName="SecondaryDestination" />
                    <ScalarProperty Name="PrimaryDesintation" ColumnName="PrimaryDesintation" />
                    <ScalarProperty Name="InitialDate" ColumnName="InitialDate" />
                    <ScalarProperty Name="CustomerTypeID" ColumnName="CustomerTypeID" />
                    <Condition ColumnName="Notes" Value="secret agent"/>
                  </MappingFragment>
                  </EntityTypeMapping>
                <EntityTypeMapping TypeName="IsTypeOf(BAModel.Customer)">
                  <MappingFragment StoreEntitySet="ContactPersonalInfo">
                    <ScalarProperty Name="ContactID" ColumnName="ContactID" />
                    <ScalarProperty Name="DietaryRestrictions" ColumnName="DietaryRestrictions" />
                    <ScalarProperty Name="WeightPounds" ColumnName="WeightPounds" />
                    <ScalarProperty Name="HeightInches" ColumnName="HeightInches" />
                    <ScalarProperty Name="BirthDate" ColumnName="BirthDate" />
                  </MappingFragment>
                </EntityTypeMapping>
                </EntitySetMapping>
    


    Any ideas why I'm getting the exception?

    thank you


    • Edited by KlemS100 Wednesday, March 7, 2012 10:04 PM
    Wednesday, March 7, 2012 10:01 PM

All replies

  • Hi KlemS100,

    Welcome to MSDN Forum.

    I want to make sure the schema first, below is my understanding, if I understand incorrectly, please feel free to correct me.

    Steps:

    1. There are two tables in the database, Contacts and Customers;

    2. Drag the two tables to the designer;

    3. Create a inheritance relationship between the two tables, Contacts is the base type and Customer derived from it.

    4. Create an entity named 'AlsoCustomer' in designer, then create an inheritance relationship between 'AlsoCustomer' and 'Customers', 'Customers' is the base type and 'AlsoCustomer' derived from it.

    Is it right?


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

    Friday, March 9, 2012 5:31 AM
    Moderator
  • Yes you are correct, but you forgot to mention that inheritance relationship between Customer and AlsoCustomer is a TPH type inheritance. Anyways, up to this point it all works correctly. But if  I additionally try to implement entity splitting, where Customer entity would be mapped to both Customer and ContactPersonalInfo tables, then I will get an exception on inserts:

    System.Data.UpdateException: Conflicting changes detected.This may happen when trying to insert multiple entities with the same key.

    Friday, March 9, 2012 5:10 PM
  • Hi KlemS100,

    What's the ContactPersonInfo? Is it another table?

    Best Regards


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

    Monday, March 12, 2012 7:28 AM
    Moderator
  • Yes, ContactPersonalInfo is a table which contains additional information about customers

    cheers

    Tuesday, March 13, 2012 2:30 PM
  • Hi KlemS100,

    I'm not sure about the schema, you have mentioned there are two tables "Contact" and "Customer" in the database, why there's another database table appears in the follow post? How did you map the "Customer" model to "ContactPersonInfo" database table? I have tried to create the model, below is what I have done,

    The Customer conceptual model doesn't map to "ContactPersionInfo" table, if possible, could you please upload the project to SkyDrive and post the link here? I will test the code and help you to solve the issue.

    Best Regards


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

    Wednesday, March 14, 2012 6:07 AM
    Moderator
  • Hi

    Your model is wrong since ContactPersonalInfo entity doesn't exist, instead ContactPersonalInfo table's columns are mapped to properties of a Customer entity.

    I've never used SkyDrive, so forgive me if I did something wrong. Anyways, I uploaded the files from the project and also included BreakAwayInstaller.exe, which installs the database this particular project is using. Here's the link:

    https://skydrive.live.com/redir.aspx?cid=95c037f2f33f2c1f&resid=95C037F2F33F2C1F!103&parid=root

    Note: In case you're wondering, BreakAwayInstaller.exe was downloaded from http://learnentityframework.com/LearnEntityFramework/downloads/#2ed ( search for link  BreakAway Database for 2nd Edition (exe installer) (revised 8/13/2010)  )

    Much appreciated

    Wednesday, March 14, 2012 7:45 PM
  • Hi KlemS100,

    I have downloaded the project and test it. Based on the model, there're some key points as below.

    1. You can't make Customer model derived from Contact model. If you do like that, Contact will be the base type of Customer model, when you want to insert a Customer entity, it will be inserted into Contact table, it obviously can't work.

    2. In TPH, you have to specify a designator to distinguish which type the record belongs to, so if you want to realize TPH, there must be a designator column in the database table to store a value to distinguish the type. In the Customer table, there's no such a column to distinguiash that the record belongs to Customer or AlsoCustomer.

    3. Mapping two tables to one conceptual model, the two tables must share the common key, but in the database, there's no relationship between Customer table and ContactPersonalInfo table. So you can't do that.

    Here are some links I think can help you.

    Walkthrough: Mapping an Entity to Multiple Tables (Entity Data Model Tools)

    Mapping two Tables to one Entity in the Entity Framework

    How to: Define a Model with Table-per-Hierarchy Inheritance

    Walkthrough: Mapping Inheritance - Table-per-Hierarchy (Entity Data Model Tools)

    Best Regards


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

    • Marked as answer by Allen_MSDNModerator Monday, March 19, 2012 2:14 AM
    • Unmarked as answer by KlemS100 Tuesday, March 20, 2012 9:50 PM
    Thursday, March 15, 2012 4:32 AM
    Moderator
  • hi, I apologize for my late reply


    1. You can't make Customer model derived from Contact model. If you do like that, Contact will be the base type of Customer model, when you want to insert a Customer entity, it will be inserted into Contact table, it obviously can't work.

    Deriving Customer entity from Contact entity is called TPT mapping, so what do you mean by saying it can't work?! As I mentioned in my first posts, everything works correctly until I also implement  entity splitting ( it works until I map both Customer and ContactPersonalInfo tables to Customer entity ), while you are claiming that  the TPT and TPH mappings I've created can't work even if I don't also implement entity splitting?!

    2. In TPH, you have to specify a designator to distinguish which type the record belongs to, so if you want to realize TPH, there must be a designator column in the database table to store a value to distinguish the type. In the Customer table, there's no such a column to distinguiash that the record belongs to Customer or AlsoCustomer.

    But I do have a designator column to distinguish the type – it is a Customer.Notes column!

    3. Mapping two tables to one conceptual model, the two tables must share the common key, but in the database, there's no relationship between Customer table and ContactPersonalInfo table. So you can't do that.

    Since Customer and ContactPersonalInfo tables do have matching primary keys, I used entity splitting to map the two tables to a Customer entity, so I'm not sure what you're talking about? Namelly, on its own ( thus if I don't also implement TPH mapping using Customer and AlsoCustomer entities ) entity splitting I've implemented works fine, but from your post it seems you are arguing that it shouldn't work regardless?!



    • Edited by KlemS100 Tuesday, March 20, 2012 10:01 PM
    Tuesday, March 20, 2012 9:59 PM