none
Entity Framework 4 - SQL Server 2008 inserts child row before parent row if the child table name is alphabetically before the parent table name RRS feed

  • General discussion

  • I know the title of this question is somewhat unbelievable but it seems to be true.  I have two tables in a SQL 2008 r2 db (happens under 2008 as well) that are a simple parent-child relationship that do not have a declared Foreign Key in the database.  The relationship is declared in the Entity Framework (4.0) EDMX model and is a very standard one-to-many relationship constraint where the parent contributes it's single INT primary key to the child's multipart key. The only odd thing is that the child table name sorts alphabetically ahead of it's parent table.  It doesn't seem to matter if the actual Entity name sorts before the parent entity name; only the physical SQL server table name seems to matter.

    When creating a parent entity and a child entity in the same object context and then saving the changes, EF inserts the CHILD entity first which causes the db trigger to throw and error (correctly).  If I simply rename the child table to say my_parent_child) from SQL and rebuild the model (deleting the Child entity since EF has no way of knowing the table has been renamed and re-adding the new association), EF now correctly inserts the parent entity before the child.

    How is this possible?  Is this something that was fixed in 4.1? 

    Here's the model, SQL and EDMX:

    Here's the SQL to create some test tables along with a test of the insert trigger:

    CREATE TABLE dbo.my_parent(
    	parent_id int NOT NULL,
    	description nvarchar(255) NULL,
    	modified_datetime datetime NOT NULL,
    	timestamp timestamp NOT NULL
    PRIMARY KEY CLUSTERED 
    (
    	parent_id ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    
    
    CREATE TABLE dbo.my_child(
    	parent_id int NOT NULL,
    	seq_nbr int NOT NULL,
    	description nvarchar(255) NULL,
    	modified_datetime datetime NOT NULL,
    	timestamp timestamp NOT NULL
    PRIMARY KEY CLUSTERED 
    (
    	parent_id ASC,
    	seq_nbr ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    
    
    CREATE TRIGGER dbo.tInsert_my_child ON dbo.my_child FOR INSERT
    AS
    BEGIN
    DECLARE
    @numrows	int,
    @nullcnt	int,
    @validcnt	int,
    @ri		int,
    @errno		int,
    @errmsg		varchar(255),
    @rtn_code	int
    
    DECLARE @insschedule_sid	int
    DECLARE @insseq_nbr	int
    
    SELECT @numrows = @@rowcount
    IF @numrows = 0
    	RETURN
    
    BEGIN TRANSACTION
    
    IF UPDATE(parent_id)
    BEGIN
    	BEGIN
    		SELECT @nullcnt = 0
    		SELECT @validcnt = COUNT(*) FROM INSERTED,my_parent
    		WHERE	inserted.parent_id = my_parent.parent_id
    			
    		IF @validcnt + @nullcnt != @numrows
    		BEGIN
    			SELECT	@errno = 30002,
    				@errmsg = 'Cannot INSERT my_child because my_parent does not exist.'
    			GOTO ERROR
    		END
    	END
    END
    
    
    COMMIT TRANSACTION
    
    RETURN
    
    ERROR:
    ROLLBACK TRANSACTION
    RAISERROR @errno @errmsg
    
    END
    
    GO
    
    /* test the trigger to be sure it won't allow orphans */
    INSERT INTO my_child (parent_id, seq_nbr, description, modified_datetime)
    VALUES (1, 1, 'test with no parent', GETDATE())
    
    

    Here's what the EDMX looks like:

    <edmx:Edmx Version="2.0" xmlns:edmx="http://schemas.microsoft.com/ado/2008/10/edmx">
     <!-- EF Runtime content -->
     <edmx:Runtime>
      <!-- SSDL content -->
      <edmx:StorageModels>
      <Schema Namespace="testDBModel.Store" Alias="Self" Provider="System.Data.SqlClient" ProviderManifestToken="2008" xmlns:store="http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator" xmlns="http://schemas.microsoft.com/ado/2009/02/edm/ssdl">
        <EntityType Name="my_child">
         <Key>
          <PropertyRef Name="parent_id" />
          <PropertyRef Name="seq_nbr" />
         </Key>
         <Property Name="parent_id" Type="int" Nullable="false" />
         <Property Name="seq_nbr" Type="int" Nullable="false" />
         <Property Name="description" Type="nvarchar" MaxLength="255" />
         <Property Name="modified_datetime" Type="datetime" Nullable="false" />
         <Property Name="timestamp" Type="timestamp" Nullable="false" StoreGeneratedPattern="Computed" />
        </EntityType>
        <EntityType Name="my_parent">
         <Key>
          <PropertyRef Name="parent_id" />
         </Key>
         <Property Name="parent_id" Type="int" Nullable="false" />
         <Property Name="description" Type="nvarchar" MaxLength="255" />
         <Property Name="modified_datetime" Type="datetime" Nullable="false" />
         <Property Name="timestamp" Type="timestamp" Nullable="false" StoreGeneratedPattern="Computed" />
        </EntityType>
       </Schema></edmx:StorageModels>
      <!-- CSDL content -->
      <edmx:ConceptualModels>
       <Schema Namespace="testDBModel" Alias="Self" xmlns:annotation="http://schemas.microsoft.com/ado/2009/02/edm/annotation" xmlns="http://schemas.microsoft.com/ado/2008/09/edm" >
        <EntityContainer Name="testDBEntities" annotation:LazyLoadingEnabled="true">
         <EntitySet Name="Children" EntityType="testDBModel.Child" />
         <EntitySet Name="Parents" EntityType="testDBModel.Parent" />
         <AssociationSet Name="ParentChild" Association="testDBModel.ParentChild">
          <End Role="Parent" EntitySet="Parents" />
          <End Role="Child" EntitySet="Children" />
         </AssociationSet>
        </EntityContainer>
        <EntityType Name="Child">
         <Key>
          <PropertyRef Name="parent_id" />
          <PropertyRef Name="seq_nbr" />
         </Key>
         <Property Type="Int32" Name="parent_id" Nullable="false" />
         <Property Type="Int32" Name="seq_nbr" Nullable="false" />
         <Property Type="String" Name="description" MaxLength="255" FixedLength="false" Unicode="true" />
         <Property Type="DateTime" Name="modified_datetime" Nullable="false" />
         <Property Type="Binary" Name="timestamp" Nullable="false" MaxLength="8" FixedLength="true" annotation:StoreGeneratedPattern="Computed" />
         <NavigationProperty Name="Parent" Relationship="testDBModel.ParentChild" FromRole="Child" ToRole="Parent" />
        </EntityType>
        <EntityType Name="Parent">
         <Key>
          <PropertyRef Name="parent_id" />
         </Key>
         <Property Type="Int32" Name="parent_id" Nullable="false" />
         <Property Type="String" Name="description" MaxLength="255" FixedLength="false" Unicode="true" />
         <Property Type="DateTime" Name="modified_datetime" Nullable="false" />
         <Property Type="Binary" Name="timestamp" Nullable="false" MaxLength="8" FixedLength="true" annotation:StoreGeneratedPattern="Computed" />
         <NavigationProperty Name="Children" Relationship="testDBModel.ParentChild" FromRole="Parent" ToRole="Child" />
        </EntityType>
        <Association Name="ParentChild">
         <End Type="testDBModel.Parent" Role="Parent" Multiplicity="1" />
         <End Type="testDBModel.Child" Role="Child" Multiplicity="*" />
         <ReferentialConstraint>
          <Principal Role="Parent">
           <PropertyRef Name="parent_id" />
          </Principal>
          <Dependent Role="Child">
           <PropertyRef Name="parent_id" />
          </Dependent>
         </ReferentialConstraint>
        </Association>
       </Schema>
      </edmx:ConceptualModels>
      <!-- C-S mapping content -->
      <edmx:Mappings>
       <Mapping Space="C-S" xmlns="http://schemas.microsoft.com/ado/2008/09/mapping/cs">
        <EntityContainerMapping StorageEntityContainer="testDBModelStoreContainer" CdmEntityContainer="testDBEntities">
         <EntitySetMapping Name="Children">
          <EntityTypeMapping TypeName="testDBModel.Child">
           <MappingFragment StoreEntitySet="my_child">
            <ScalarProperty Name="timestamp" ColumnName="timestamp" />
            <ScalarProperty Name="modified_datetime" ColumnName="modified_datetime" />
            <ScalarProperty Name="description" ColumnName="description" />
            <ScalarProperty Name="seq_nbr" ColumnName="seq_nbr" />
            <ScalarProperty Name="parent_id" ColumnName="parent_id" />
           </MappingFragment>
          </EntityTypeMapping>
         </EntitySetMapping>
         <EntitySetMapping Name="Parents">
          <EntityTypeMapping TypeName="testDBModel.Parent">
           <MappingFragment StoreEntitySet="my_parent">
            <ScalarProperty Name="timestamp" ColumnName="timestamp" />
            <ScalarProperty Name="modified_datetime" ColumnName="modified_datetime" />
            <ScalarProperty Name="description" ColumnName="description" />
            <ScalarProperty Name="parent_id" ColumnName="parent_id" />
           </MappingFragment>
          </EntityTypeMapping>
         </EntitySetMapping>
        </EntityContainerMapping>
       </Mapping>
      </edmx:Mappings>
     </edmx:Runtime>
     <!-- EF Designer content (DO NOT EDIT MANUALLY BELOW HERE) -->
     <Designer xmlns="http://schemas.microsoft.com/ado/2008/10/edmx">
      <Connection>
       <DesignerInfoPropertySet>
        <DesignerProperty Name="MetadataArtifactProcessing" Value="EmbedInOutputAssembly" />
       </DesignerInfoPropertySet>
      </Connection>
      <Options>
       <DesignerInfoPropertySet>
        <DesignerProperty Name="ValidateOnBuild" Value="true" />
        <DesignerProperty Name="EnablePluralization" Value="True" />
        <DesignerProperty Name="IncludeForeignKeysInModel" Value="True" />
       </DesignerInfoPropertySet>
      </Options>
      <!-- Diagram content (shape and connector positions) -->
      <Diagrams>
       <Diagram Name="Model1">
        <EntityTypeShape EntityType="testDBModel.Child" Width="2.25" PointX="4.5" PointY="11.5" Height="1.7566536458333317" />
        <EntityTypeShape EntityType="testDBModel.Parent" Width="2" PointX="1.125" PointY="11.5" Height="1.5923063151041674" />
        <AssociationConnector Association="testDBModel.ParentChild">
         <ConnectorPoint PointX="3.125" PointY="12.378326822916666" />
         <ConnectorPoint PointX="4.5" PointY="12.378326822916666" />
        </AssociationConnector>
       </Diagram>
      </Diagrams>
     </Designer>
    </edmx:Edmx>
    



    Monday, August 15, 2011 8:28 PM

All replies

  • FURTHER UPDATES:  It seems that EF really is ignoring the conceptual Relationship Constraint.  If I physically just add the same Association to the SSDL that gets generated when you have a true DB-backed Foreign Key, then EF does the insert correctly.  Of course, if you do that manually in the EDMX file, the designer wipes it out the next time you update from the database.  So the question is, why does EF not use the CSDL association?  I'm guessing that the table name order is used as some default when all else is equal for deciding the insert dependency, EF just generate the inserts in db table name order???

    From MSDN, here's info that suggests it should work by not having the SSL  AssociationSet if you have the CSDL ReferentialContraint and if you have both, the CSDL is supposed to take precedence: 

    ReferentialConstraint Element (CSDL)

    A ReferentialConstraint element in conceptual schema definition language (CSDL) defines functionality that is similar to a referential integrity constraint in a relational database. In the same way that a column (or columns) from a database table can reference the primary key of another table, a property (or properties) of an entity type can reference the entity key of another entity type. The entity type that is referenced is called the principal end of the constraint. The entity type that references the principal end is called the dependent end of the constraint.

     

    If a foreign key that is exposed on one entity type references a property on another entity type, the ReferentialConstraint element defines an association between the two entity types. Because the ReferentialConstraint element provides information about how two entity types are related, no corresponding AssociationSetMapping element is necessary in the mapping specification language (MSL). An association between two entity types that do not have foreign keys exposed must have a corresponding AssociationSetMapping element in order to map association information to the data source.

     

    http://msdn.microsoft.com/en-us/library/bb738464.aspx

     

     

    AssociationSet Element (SSDL)

    The AssociationSet element in store schema definition language (SSDL) represents a foreign key constraint between two tables in the underlying database. The table columns that participate in the foreign key constraint are specified in an Association element. The Association element that corresponds to a given AssociationSet element is specified in the Association attribute of the AssociationSet element.

     

    SSDL association sets are mapped to CSDL association sets by an AssociationSetMapping element. However, if the CSDL association for a given CSDL association set is defined by using a ReferentialConstraint element , no corresponding AssociationSetMapping element is necessary. In this case, if an AssociationSetMapping element is present, the mappings it defines will be overridden by the ReferentialConstraint element.

     

    http://msdn.microsoft.com/en-us/library/bb399601.aspx

    Monday, August 15, 2011 9:56 PM
  • On 8/15/2011 4:28 PM, pk55 wrote:
     
    <AssociationSet  Name="ParentChild"  Association="testDBModel.ParentChild">
     <End  Role="Parent"  EntitySet="Parents"  />
     <End  Role="Child"  EntitySet="Children"  />
    </AssociationSet>
     
    You get rid of the Association out of the edmx and your problem is
    solved. Of course, you must valid the model in the IDE. The constraints
    are still on the database tables, but not on the model that's causing
    problems.
     
    Monday, August 15, 2011 10:14 PM
  • How exactly does that help?  You wouldn't have any navigation properties between the entities and, because there no really isnt' any declared RI between the entities, the entity framework still tries to insert the child ahead of the parent and of coures a database error occurs because it had no clue that parent must be there before a child row?  The whole idea of using EF is to let it do a lot of this work of managing the relationships between the conceptual objects and the physical database storage.
    Monday, August 15, 2011 10:22 PM
  • On 8/15/2011 6:22 PM, pk55 wrote:
    > How exactly does that help? You wouldn't have any navigation properties
    > between the entities and, because there no really isnt' any declared RI
    > between the entities, the entity framework still tries to insert the
    > child ahead of the parent and of coures a database error occurs because
    > it had no clue that parent must be there before a child row? The whole
    > idea of using EF is to let it do a lot of this work of managing the
    > relationships between the conceptual objects and the physical database
    > storage.
     
    You get rid of the associations off the model,  you expose all the
    properties of the parent and child objects so that you insert the parent
    object first, get the parent's id, place id on the child object as the
    foreign-key and then insert the child.
     
    It's no different than if you had to the same thing using dynamic T-SQL
    inline or sproc using SCOPE_IDENTITY() to get the id of the parent just
    inserted and apply the parent's id to child's foreign key when the child
    is inserted.
     
    The id of the parent object is placed in its property in the parent
    object that's still in memory after the SaveChanges() is done on the parent.
     
    It's an issue with EF that it will not do  this correctly if you leave
    the association in this situation in some cases. And you have to
    manually take the control.
     
    It is what it is.
     
    Monday, August 15, 2011 10:43 PM
  • If I were writing this in T-SQL, I would have to control the order the inserts. I could manually save entities in that same order from EF as well.  But EF is supposed to do this.  That's one of the supposed benefits of an ORM tool.  You map conceptual objects to physical objects and the tool does the job of getting your object data to the data store.  I appreciate your input but please don't suggest removing associations from the model as an answer to this. 

    I'm looking for a response from the Entity Framework team about this bug. 

    Monday, August 15, 2011 10:54 PM
  • On 8/15/2011 6:54 PM, pk55 wrote:
    > If I were writing this in T-SQL, I would have to control the order the
    > inserts. I could manually save entities in that same order from EF as
    > well. But EF is supposed to do this. That's one of the supposed benefits
    > of an ORM tool. You map conceptual objects to physical objects and the
    > tool does the job of getting your object data to the data store. I
    > appreciate your input but please don't suggest removing associations
    > from the model as an answer to this.
    >
    > I'm looking for a response from the Entity Framework team about this bug.
    >
     
    Good luck, I have used the solution  on more than a few occasions
    starting with EF 3.5 to come around this issue in enterprise level
    ASP.NET solutions using EF. You are sitting here acting like EF is a
    stops all and ends all solution, and it's not that. Sometimes, one has
    got to think outside of the box.
     
    I tried to peel you off the wall and you spit in my face.
     
    Monday, August 15, 2011 11:02 PM