none
Can't get TPC inheritance to work - please help RRS feed

  • Question

  • hi 

    In a database I have Reservations and OldReservations tables, where OldReservations is a copy ( of Reservations table ) and is used to store old reservations. Here are the steps I did to create TPC inheritance:



    1) I derived OldReservation entity from Reservation entity 
    2) I've changed Reservation entity into abstract type and renamed it into ReservationBase ( ReservationBase is mapped to Reservations table )
    3) I removed the overlapping properties from OldReservations entity 
    4) I've then mapped the OldReservations table fields in the XML of the EDMX file
    5) I've alse derived ( from ReservationBase ) a new type Reservation
    6) I've then mapped derived entity Reservation to Reservations table

              <EntitySetMapping Name="Reservations">
                <EntityTypeMapping TypeName="IsTypeOf(BAModel.ReservationBase)">
                  <MappingFragment StoreEntitySet="Reservations">
                    <ScalarProperty Name="ReservationID" ColumnName="ReservationID" />
                    <ScalarProperty Name="RowVersion" ColumnName="RowVersion" />
                    <ScalarProperty Name="EventID" ColumnName="EventID" />
                    <ScalarProperty Name="ContactID" ColumnName="ContactID" />
                    <ScalarProperty Name="ReservationDate" ColumnName="ReservationDate" />
                  </MappingFragment>
                </EntityTypeMapping>
                <EntityTypeMapping TypeName="IsTypeOf(BAModel.OldReservation)">
                  <MappingFragment StoreEntitySet="OldReservations">
                    <ScalarProperty Name="ReservationID" ColumnName="ReservationID" />
                    <ScalarProperty Name="RowVersion" ColumnName="RowVersion" />
                    <ScalarProperty Name="EventID" ColumnName="EventID" />
                    <ScalarProperty Name="ContactID" ColumnName="ContactID" />
                    <ScalarProperty Name="ReservationDate" ColumnName="ReservationDate" />
                    </MappingFragment>
                </EntityTypeMapping>
                <EntityTypeMapping TypeName="BAModel.Reservation">
                  <MappingFragment StoreEntitySet="Reservations">
                    <ScalarProperty Name="ReservationID" ColumnName="ReservationID" />           
                  </MappingFragment>
                </EntityTypeMapping>
              </EntitySetMapping>



    But when I compile it I get an error "EntityTypes BAModel.OldReservation, BAModel.Reservation are being mapped to the same rows in table Reservations. Mapping conditions can be used to distinguish the rows that these types are mapped to". From the error it appears that EF is trying to map OldReservation entity to Reservations table instead of OldReservations table?! Error also advices me to use a mapping condition, but that would require me to introduce new fileld into both tables that would act as a filter ( I did try this approach and still got an error )

    thank you

    Wednesday, February 29, 2012 9:38 PM

Answers

  • hi

    I haven't already marked your post as answered since I wasn't sure whether you intended to explain why my emdx file contains IsTypeOf, while yours doesn't. You did answer my original questions, so I will mark it as answered only regardless, but only in a day or two, just in case you still plan to reply with regards to IsTypeOf confusion

    In any case, I really appreciate your help

    Oh, sorry, forgot to clarify that part: the reason mine didn't contain IsTypeOf is because I didn't use Visual Studio to generate the mapping; I used my own tool ( http://huagati.blogspot.com/2010/10/mixing-inheritance-strategies-in-entity.html ) that supports creating TPC mapping in model-first scenarios.

    (While the EF runtime supports all three of TPT, TPH, and TPC, the out-of-the-box VS designer only support TPT and TPH).

    Anyways, if you manually remove the IsTypeOf from EntityTypeMapping/@TypeName, it should work.


     

       Cool tools for Linq-to-SQL and Entity Framework 4:
     huagati.com/dbmltools - Visual Studio add-in with loads of new features for the Entity Framework and Linq-to-SQL designers
     huagati.com/L2SProfiler - Runtime SQL query profiler for Linq-to-SQL and Entity Framework v4

    • Marked as answer by KlemS100 Wednesday, March 7, 2012 9:40 PM
    Wednesday, March 7, 2012 1:05 AM

All replies

  • If you want to use TPC (one table for each of the two entities), try this mapping instead:

    <EntitySetMapping Name="Reservations">
      <EntityTypeMapping TypeName="BAModel.Reservation">
        <MappingFragment StoreEntitySet="Reservations">
          <ScalarProperty Name="ReservationID" ColumnName="ReservationID" />
          <ScalarProperty Name="EventID" ColumnName="EventID" />
          <ScalarProperty Name="ContactID" ColumnName="ContactID" />
          <ScalarProperty Name="ReservationDate" ColumnName="ReservationDate" />
        </MappingFragment>
      </EntityTypeMapping>
      <EntityTypeMapping TypeName="BAModel.OldReservation">
        <MappingFragment StoreEntitySet="OldReservations">
          <ScalarProperty Name="ReservationID" ColumnName="ReservationID" />
          <ScalarProperty Name="EventID" ColumnName="EventID" />
          <ScalarProperty Name="ContactID" ColumnName="ContactID" />
          <ScalarProperty Name="ReservationDate" ColumnName="ReservationDate" />
        </MappingFragment>
      </EntityTypeMapping>
    </EntitySetMapping>

    ...since ReservationBase is abstract (with no underlying table), no need to map it.


       Cool tools for Linq-to-SQL and Entity Framework 4:
     huagati.com/dbmltools - Visual Studio add-in with loads of new features for the Entity Framework and Linq-to-SQL designers
     huagati.com/L2SProfiler - Runtime SQL query profiler for Linq-to-SQL and Entity Framework v4

    • Edited by KristoferA Thursday, March 1, 2012 2:21 AM
    • Proposed as answer by KristoferA Wednesday, March 7, 2012 1:52 AM
    Thursday, March 1, 2012 2:02 AM
  • hi, I really hope you can help me a bit more, since I'm completely lost

    a) It appears I completely misunderstood what TPC mapping is, since I thought  the whole point of using TPC is that if otherwise unrelated tables have overlapping fields, then TPC allows us to define those common  properties in a base class, from which entities ( mapped to tables with overlapping fields ) derive? Thus, in my example,  I thought that I could define properties ( which are common to both Reservation and OldReservation entities ) in abstract ReservationBase entity?! 

    b) But from the code excerpt  you've posted,  it appears that ReservationBase doesn't define those properties that are common to both Reservation and OldReservation entities?! 

    c) In code excerpt you've posted, it also appears that Reservation and OldReservation don't derive from ReservationBase ( I'm assuming this is the case since you don't have TypeName attribute set to IsTypeOf(...) ). 

    d) But if my assumptions under a) are correct, then why do I get an exception even if I don't map ReservationBase to any table?

    thank you

    Thursday, March 1, 2012 5:07 PM
  • Hi,

    a) Yes, you can do exactly that; define the common members in the base class.

    b) They're all defined in ReservationBase in the CSDL. However, in the mappings (MSL), they need to be mapped individually to the underlying (separate) SSDL entities representing each table.

    Here's a more complete example. First the class diagram, representing the CSDL portion of the model you have described:

    Class diagram (CSDL)

    ...the underlying tables however are two separate tables with identical schema:

    Tables

    To achieve this, let's look at the three parts of the EDMX file involved. First up - the CSDL (conceptual layer representing the generated classes):

    <EntityContainer Name="TPCSampleContainer" annotation:LazyLoadingEnabled="true">
      <EntitySet Name="Reservations" EntityType="TPCSample.ReservationBase" huagati:InheritanceStrategy="TPC" />
    </EntityContainer>
    <EntityType Name="ReservationBase" Abstract="true">
      <Key>
        <PropertyRef Name="ReservationID" />
      </Key>
      <Property Type="Int32" Name="ReservationID" Nullable="false" annotation:StoreGeneratedPattern="Identity" />
      <Property Type="Int32" Name="EventID" Nullable="false" />
      <Property Type="Int32" Name="ContactID" Nullable="false" />
      <Property Type="DateTime" Name="ReservationDate" Nullable="false" />
    </EntityType>
    <EntityType Name="OldReservation" BaseType="TPCSample.ReservationBase" />
    <EntityType Name="Reservation" BaseType="TPCSample.ReservationBase" />
    

    ...and SSDL (storage layer representing the database tables):

    <EntityContainer Name="TPCSampleTargetContainer">
      <EntitySet Name="OldReservation" store:Type="Tables" Schema="dbo" Table="OldReservation" store:Name="OldReservation" EntityType="TPCSample.Store.OldReservation" />
      <EntitySet Name="Reservation" store:Type="Tables" Schema="dbo" Table="Reservation" store:Name="Reservation" EntityType="TPCSample.Store.Reservation" />
    </EntityContainer>
    <EntityType Name="OldReservation">
      <Documentation />
      <Key>
        <PropertyRef Name="ReservationID" />
      </Key>
      <Property Name="ReservationID" Type="int" Nullable="false" StoreGeneratedPattern="Identity" />
      <Property Name="EventID" Type="int" Nullable="false" />
      <Property Name="ContactID" Type="int" Nullable="false" />
      <Property Name="ReservationDate" Type="datetime" Nullable="false" />
    </EntityType>
    <EntityType Name="Reservation">
      <Documentation />
      <Key>
        <PropertyRef Name="ReservationID" />
      </Key>
      <Property Name="ReservationID" Type="int" Nullable="false" StoreGeneratedPattern="Identity" />
      <Property Name="EventID" Type="int" Nullable="false" />
      <Property Name="ContactID" Type="int" Nullable="false" />
      <Property Name="ReservationDate" Type="datetime" Nullable="false" />
    </EntityType>
    

    ...and MSL (mappings betweeen the CSDL and SSDL):

    <EntitySetMapping Name="Reservations">
      <EntityTypeMapping TypeName="TPCSample.OldReservation">
        <MappingFragment StoreEntitySet="OldReservation">
          <ScalarProperty Name="ReservationID" ColumnName="ReservationID" />
          <ScalarProperty Name="EventID" ColumnName="EventID" />
          <ScalarProperty Name="ContactID" ColumnName="ContactID" />
          <ScalarProperty Name="ReservationDate" ColumnName="ReservationDate" />
        </MappingFragment>
      </EntityTypeMapping>
      <EntityTypeMapping TypeName="TPCSample.Reservation">
        <MappingFragment StoreEntitySet="Reservation">
          <ScalarProperty Name="ReservationID" ColumnName="ReservationID" />
          <ScalarProperty Name="EventID" ColumnName="EventID" />
          <ScalarProperty Name="ContactID" ColumnName="ContactID" />
          <ScalarProperty Name="ReservationDate" ColumnName="ReservationDate" />
        </MappingFragment>
      </EntityTypeMapping>
    </EntitySetMapping>
    

    ...and finally the TSQL-DDL for creating the two tables:

    /*
     *** Generated 02 Mar 2012 07:53:03 by Huagati DBML/EDMX Tools version 2.24.4154.30070
     *** Please read and verify the actions performed by this script before applying to your database.
     *** Portions may require manual editing due to missing information in model-generated script portions.
     */
    /*Table dbo.OldReservation corresponding to storage model entity set OldReservation*/
    Create Table dbo.OldReservation (ReservationID int NOT NULL IDENTITY, EventID int NOT NULL, ContactID int NOT NULL, ReservationDate datetime NOT NULL,  constraint PK_OldReservation primary key (ReservationID));
    go
    /*Table dbo.Reservation corresponding to storage model entity set Reservation*/
    Create Table dbo.Reservation (ReservationID int NOT NULL IDENTITY, EventID int NOT NULL, ContactID int NOT NULL, ReservationDate datetime NOT NULL,  constraint PK_Reservation primary key (ReservationID));
    go
    

    ...and with this in place you can do queries like:

    using (TPCSampleContainer tsc = new TPCSampleContainer())
    {
        //query both old and current reservations in a union query
        var q1 = tsc.Reservations.AsQueryable();
        var r1 = q1.ToList();
    
        //query only current reservations
        var q2 = tsc.Reservations.OfType<Reservation>().AsQueryable();
        var r2 = q2.ToList();
    
        //query only old reservations
        var q3 = tsc.Reservations.OfType<OldReservation>().AsQueryable();
        var r3 = q3.ToList();
    }
    


     

       Cool tools for Linq-to-SQL and Entity Framework 4:
     huagati.com/dbmltools - Visual Studio add-in with loads of new features for the Entity Framework and Linq-to-SQL designers
     huagati.com/L2SProfiler - Runtime SQL query profiler for Linq-to-SQL and Entity Framework v4

    Friday, March 2, 2012 1:18 AM
  • Ah, it's working now. I checked your example and as it turns out, I already had the correct mappings, but for some reason ReservationBase had "_1" appended to each of its properties, which I didn't notice before. 


    "b) They're all defined in ReservationBase in the CSDL. However, in the mappings (MSL), they need to be mapped individually to the underlying (separate) SSDL entities representing each table."

    Since Reservation derives from ReservationBase, shouldn't in MSL be the following entry:


    <EntityTypeMapping TypeName="IsTypeOf(TPCSample.OldReservation)">

    instead of:

    <EntityTypeMapping TypeName="TPCSample.OldReservation">


    thank you

    Friday, March 2, 2012 7:10 PM

  • Since Reservation derives from ReservationBase, shouldn't in MSL be the following entry:


    If we were using TPH or TPT: yes. However, not for TPC.

    To be honest, I can't remember why, and when I checked my mapping code to see if there was any clues as to why the only thing I came across was:

    tpc exclusion

    ...and I am sure there is some good reason for excluding TPC from "IsTypeOf". :)

    Update: Thinking of it, it may have something to do with the underlying tables being completely separate, so there is no risk for constraint violations for records of different types that share the same PK/entitykey values.


     

       Cool tools for Linq-to-SQL and Entity Framework 4:
     huagati.com/dbmltools - Visual Studio add-in with loads of new features for the Entity Framework and Linq-to-SQL designers
     huagati.com/L2SProfiler - Runtime SQL query profiler for Linq-to-SQL and Entity Framework v4


    • Edited by KristoferA Saturday, March 3, 2012 2:26 AM
    Saturday, March 3, 2012 2:23 AM
  • But why when I create a TPC inheritance, MSL entries for Reservation and OldReservation do contain IsTypeOF?

    Here are the relevant EDMX entries:

    SSDL:

              <EntitySet Name="OldReservations" EntityType="BreakAwayModel.Store.OldReservations" store:Type="Tables" Schema="dbo" />
              <EntitySet Name="Reservations" EntityType="BreakAwayModel.Store.Reservations" store:Type="Tables" Schema="dbo" />
    
    
           <EntityType Name="OldReservations">
              <Key>
                <PropertyRef Name="ReservationID" />
              </Key>
              <Property Name="ReservationID" Type="int" Nullable="false" StoreGeneratedPattern="Identity" />
              <Property Name="ReservationDate" Type="datetime" Nullable="false" />
              <Property Name="ContactID" Type="int" Nullable="false" />
              <Property Name="EventID" Type="int" />
              <Property Name="RowVersion" Type="timestamp" Nullable="false" StoreGeneratedPattern="Computed" />
            </EntityType>
            <EntityType Name="Reservations">
              <Key>
                <PropertyRef Name="ReservationID" />
              </Key>
              <Property Name="ReservationID" Type="int" Nullable="false" StoreGeneratedPattern="Identity" />
              <Property Name="ReservationDate" Type="datetime" Nullable="false" />
              <Property Name="ContactID" Type="int" Nullable="false" />
              <Property Name="EventID" Type="int" />
              <Property Name="RowVersion" Type="timestamp" Nullable="false" StoreGeneratedPattern="Computed" />
            </EntityType>

    CSDL:

            <EntitySet Name="ReservationBases" EntityType="BAModel.ReservationBase" />
    
            <EntityType Name="OldReservation" BaseType="BAModel.ReservationBase">
              </EntityType>
            <EntityType Name="Reservation" BaseType="BAModel.ReservationBase">
              <NavigationProperty Name="Payments" Relationship="BAModel.FK_Payments_Reservations" FromRole="Reservation" ToRole="Payment" />
            </EntityType>
    
            <EntityType Name="ReservationBase" Abstract="true" >
              <Key>
                <PropertyRef Name="ReservationID" />
              </Key>
              <Property Type="Int32" Name="ReservationID" Nullable="false" annotation:StoreGeneratedPattern="Identity" />
              <Property Type="DateTime" Name="ReservationDate" Nullable="false" />
              <Property Type="Int32" Name="ContactID" Nullable="false" />
              <Property Type="Int32" Name="EventID" />
              <Property Type="Binary" Name="RowVersion" Nullable="false" MaxLength="8" FixedLength="true" annotation:StoreGeneratedPattern="Computed" />
            </EntityType>

    MSL:

     
              <EntitySetMapping Name="ReservationBases">
                <EntityTypeMapping TypeName="IsTypeOf(BAModel.Reservation)">
                  <MappingFragment StoreEntitySet="Reservations">
                    <ScalarProperty Name="ReservationID" ColumnName="ReservationID"/>
                    <ScalarProperty Name="ReservationDate" ColumnName="ReservationDate"/>
                    <ScalarProperty Name="ContactID" ColumnName="ContactID"/>
                    <ScalarProperty Name="EventID" ColumnName="EventID"/>
                    <ScalarProperty Name="RowVersion" ColumnName="RowVersion"/>
                  </MappingFragment>
                </EntityTypeMapping>
                <EntityTypeMapping TypeName="IsTypeOf(BAModel.OldReservation)">
                  <MappingFragment StoreEntitySet="OldReservations">
                    <ScalarProperty Name="ReservationID" ColumnName="ReservationID"/>
                    <ScalarProperty Name="ReservationDate" ColumnName="ReservationDate"/>
                    <ScalarProperty Name="ContactID" ColumnName="ContactID"/>
                    <ScalarProperty Name="EventID" ColumnName="EventID"/>
                    <ScalarProperty Name="RowVersion" ColumnName="RowVersion"/>
                  </MappingFragment>
                </EntityTypeMapping>
              </EntitySetMapping>


    • Edited by KlemS100 Monday, March 5, 2012 12:22 AM
    Sunday, March 4, 2012 11:33 PM
  • But why when I create a TPC inheritance, MSL entries for Reservation and OldReservation do contain IsTypeOF?

    Here are the relevant EDMX entries:


    What tool did you use for creating the TPC mapping entry? There is probably a bug in that tool if it outputs IsTypeOf for TPC.

    As far as I know, the VS designer don't support TPC (unless that has changed recently..?)


     

       Cool tools for Linq-to-SQL and Entity Framework 4:
     huagati.com/dbmltools - Visual Studio add-in with loads of new features for the Entity Framework and Linq-to-SQL designers
     huagati.com/L2SProfiler - Runtime SQL query profiler for Linq-to-SQL and Entity Framework v4

    Monday, March 5, 2012 11:49 AM
  • hi, I'm using Microsoft VS 2010 Premium, trial edition
    Monday, March 5, 2012 10:56 PM
  • hi

    I haven't already marked your post as answered since I wasn't sure whether you intended to explain why my emdx file contains IsTypeOf, while yours doesn't. You did answer my original questions, so I will mark it as answered only regardless, but only in a day or two, just in case you still plan to reply with regards to IsTypeOf confusion

    In any case, I really appreciate your help

    Tuesday, March 6, 2012 8:58 PM
  • hi

    I haven't already marked your post as answered since I wasn't sure whether you intended to explain why my emdx file contains IsTypeOf, while yours doesn't. You did answer my original questions, so I will mark it as answered only regardless, but only in a day or two, just in case you still plan to reply with regards to IsTypeOf confusion

    In any case, I really appreciate your help

    Oh, sorry, forgot to clarify that part: the reason mine didn't contain IsTypeOf is because I didn't use Visual Studio to generate the mapping; I used my own tool ( http://huagati.blogspot.com/2010/10/mixing-inheritance-strategies-in-entity.html ) that supports creating TPC mapping in model-first scenarios.

    (While the EF runtime supports all three of TPT, TPH, and TPC, the out-of-the-box VS designer only support TPT and TPH).

    Anyways, if you manually remove the IsTypeOf from EntityTypeMapping/@TypeName, it should work.


     

       Cool tools for Linq-to-SQL and Entity Framework 4:
     huagati.com/dbmltools - Visual Studio add-in with loads of new features for the Entity Framework and Linq-to-SQL designers
     huagati.com/L2SProfiler - Runtime SQL query profiler for Linq-to-SQL and Entity Framework v4

    • Marked as answer by KlemS100 Wednesday, March 7, 2012 9:40 PM
    Wednesday, March 7, 2012 1:05 AM
  • I'd give you 100 points if I could :)

    Thank you for helping me out

    Wednesday, March 7, 2012 9:40 PM