TPC inheritance throws an exception on inserts

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. Both tables use autogenerated identity keys, but Reservations has a seed of 0 while OldReservations has a seed of 1000, so key values in the two tables don't overlap. Here are the steps I did to create TPC inheritance:

1 - I derived OldReservation entity from Reservations entity

2 - I removed the overlapping properties from OldReservations entity

3 - I've then mapped the OldReservations table fields in the XML of the EDMX file

      <EntitySetMapping Name="Reservations">
        <EntityTypeMapping TypeName="IsTypeOf(BAModel.Reservation)">
          <MappingFragment StoreEntitySet="Reservations">
            <ScalarProperty Name="ReservationID" ColumnName="ReservationID" />
            <ScalarProperty Name="ReservationDate" ColumnName="ReservationDate" />
            <ScalarProperty Name="EventID" ColumnName="EventID" />
            <ScalarProperty Name="ContactID" ColumnName="ContactID" />
            <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="EventID" ColumnName="EventID" />
            <ScalarProperty Name="ContactID" ColumnName="ContactID" />
            <ScalarProperty Name="RowVersion" ColumnName="RowVersion" />
          </MappingFragment>
        </EntityTypeMapping>
      </EntitySetMapping>

1) But when I run the folllowing query, the generated sql uses LEFT OUTER JOIN, which doesn't make sense, since it means that query will only return rows from OldReservations table where Reservations.ReservationID == OldReservations.ReservationID:

var reservations = context.Reservations;
foreach (var item in reservations);

Generated SQL:

SELECT CASE
         WHEN (NOT (([Project1].[C1] = 1)
                    AND ([Project1].[C1] IS NOT NULL))) THEN '0X'
         ELSE '0X0X'
       END                         AS [C1],
       [Extent1].[ReservationID]   AS [ReservationID],
       [Extent1].[ReservationDate] AS [ReservationDate],
       [Extent1].[ContactID]       AS [ContactID],
       [Extent1].[EventID]         AS [EventID],
       [Extent1].[RowVersion]      AS [RowVersion]
FROM   [dbo].[Reservations] AS [Extent1]
       LEFT OUTER JOIN (SELECT [Extent2].[ReservationID] AS [ReservationID],
                               cast(1 as bit)            AS [C1]
                        FROM   [dbo].[OldReservations] AS [Extent2]) AS [Project1]
         ON [Extent1].[ReservationID] = [Project1].[ReservationID]

a) So is there a situation where it would be useful to implement TPC inheritance the way I've described it in this post?

b) What is the logic behind generated query using LEFT OUTER JOIN instead of UNION operator?

2)  when I try to insert a new row into OldReservations table I get:

UpdateException: A value shared across entities or associations is generated in more than one location. Check that mapping does not split an EntityKey to multiple store-generated columns. ---> System.ArgumentException: An item with the same key has already been added.

var reservation = new OldReservation();
reservation.ReservationDate = DateTime.Now;
reservation.ContactID = 129;

context.Reservations.AddObject(reservation);
context.SaveChanges();

This exception is thrown before EF manages to send insert command to the database. Any ideas why I'm getting the exception?

Thank you

Answer 1

This exception is thrown before EF manages to send insert command to the database. Any ideas why I'm getting the exception?

Thank you

Hi,

Yes, I think it is due to the "IsTypeOf" wrappers; remove those and it should work.

<EntityTypeMapping TypeName="IsTypeOf(BAModel.Reservation)">

...to:

<EntityTypeMapping TypeName="BAModel.Reservation">

...and:

<EntityTypeMapping TypeName="IsTypeOf(BAModel.OldReservation)">
...to:

<EntityTypeMapping TypeName="BAModel.OldReservation">

(Same as the other thread :) - http://social.msdn.microsoft.com/Forums/en-US/adodotnetentityframework/thread/5feddc9b-e48c-423a-b077-dcc00928a2ca )

---

 

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

Answer 2

Hi

Yay, removing IsTypeOf made the difference. Thus, inserts no longer throw exception and now select query uses UNION operator instead of LEFT OUTER JOIN.

I will mark this post as answered in a day, but just in case you'll still be reading this, any idea why in this example we need to remove IsTypeOf for TPC to work properly, while with the example in my other thread (  http://social.msdn.microsoft.com/Forums/en-US/adodotnetentityframework/thread/5feddc9b-e48c-423a-b077-dcc00928a2ca ) TPC works properly even if we don't remove IsTypeOf?

Answer 3

thank you very much for helping me out

cheers