Bug when using stored proc to update multiples children entities

Question

When I change multiples children entities instances and their update is mapped to a stored proc, I get an Update Exception : Unable to determine a valid ordering for dependent operations. Dependencies may exist due to foreign key constraints, model requirements, or store-generated values.

But if I update only 1 child or remove the stored proc mapping, all goes well.

Here is the code to reproduce the bug:

using System;
using System.Data;
using System.Linq;

namespace ConsoleApplication1
{
   class Program
   {
      static void Main (string[] args)
      {
         using (TestEntities context = new TestEntities ())
         {
            var toUpdate = (from t in context.Composant.Include ("Descriptions")
                            select t).First ();

            int nbrUpdates = 0;
            foreach (var description in toUpdate.Descriptions)
            {
               description.Description = "new description " + Guid.NewGuid ().ToString ();
               nbrUpdates++;
            }

            if (nbrUpdates < 2)
               throw new InvalidOperationException ("Cannot reproduce bug with only 1 update.");

            try
            {
               context.SaveChanges ();
            }
            catch (UpdateException exc)
            {
               throw new InvalidOperationException ("Bug reproduced.", exc);
            }

         }
      }

   }
}

And this is the edmx file :

<?xml version="1.0" encoding="utf-8"?>
<edmx:Edmx Version="2.0" xmlns:edmx="http://schemas.microsoft.com/ado/2008/10/edmx">
	<edmx:Runtime>
		<!-- SSDL content -->
		<edmx:StorageModels>
		<Schema Namespace="TestStoreNamespace" Alias="Self" Provider="System.Data.SqlClient" ProviderManifestToken="2005" xmlns:store="http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator" xmlns="http://schemas.microsoft.com/ado/2009/02/edm/ssdl">
        <EntityContainer Name="TestStoreNamespaceContainer">
          <EntitySet Name="Composant" EntityType="TestStoreNamespace.Composant" store:Type="Tables" Schema="dbo" />
          <EntitySet Name="ComposantDescr" EntityType="TestStoreNamespace.ComposantDescr" store:Type="Tables" Schema="dbo" />
          <AssociationSet Name="FK_dbo_ComposantDescr_dbo_Composant" Association="TestStoreNamespace.FK_dbo_ComposantDescr_dbo_Composant">
            <End Role="Composant" EntitySet="Composant" />
            <End Role="ComposantDescr" EntitySet="ComposantDescr" />
          </AssociationSet>
        </EntityContainer>
        <EntityType Name="Composant">
          <Key>
            <PropertyRef Name="IdComposant" />
          </Key>
          <Property Name="IdComposant" Type="int" Nullable="false" />
        </EntityType>
        <EntityType Name="ComposantDescr">
          <Key>
            <PropertyRef Name="IdComposant" />
            <PropertyRef Name="TypeDescription" />
          </Key>
          <Property Name="IdComposant" Type="int" Nullable="false" />
          <Property Name="TypeDescription" Type="int" Nullable="false" />
          <Property Name="Description" Type="nvarchar(max)" />
        </EntityType>
        <Association Name="FK_dbo_ComposantDescr_dbo_Composant">
          <End Role="Composant" Type="TestStoreNamespace.Composant" Multiplicity="1">
            <OnDelete Action="Cascade" />
          </End>
          <End Role="ComposantDescr" Type="TestStoreNamespace.ComposantDescr" Multiplicity="*" />
          <ReferentialConstraint>
            <Principal Role="Composant">
              <PropertyRef Name="IdComposant" />
            </Principal>
            <Dependent Role="ComposantDescr">
              <PropertyRef Name="IdComposant" />
            </Dependent>
          </ReferentialConstraint>
        </Association>
        <Function Name="sp_ComposantDescr_U" Aggregate="false" BuiltIn="false" NiladicFunction="false" IsComposable="false" ParameterTypeSemantics="AllowImplicitConversion" Schema="dbo">
          <Parameter Name="pId" Type="int" Mode="In" />
          <Parameter Name="pTypeDescription" Type="int" Mode="In" />
          <Parameter Name="pDescription" Type="nvarchar(max)" Mode="In" />
        </Function>
      </Schema></edmx:StorageModels>
		<!-- CSDL content -->
		<edmx:ConceptualModels>
			<Schema Namespace="TestNamespace" Alias="Self" xmlns="http://schemas.microsoft.com/ado/2008/09/edm">
				<EntityContainer Name="TestEntities">
					<EntitySet Name="Composant" EntityType="TestNamespace.Composant" />
					<EntitySet Name="ComposantDescr" EntityType="TestNamespace.ComposantDescr" />
          <AssociationSet Name="FK_dbo_ComposantDescr_dbo_Composant" Association="TestNamespace.FK_dbo_ComposantDescr_dbo_Composant">
            <End Role="Composant" EntitySet="Composant" />
            <End Role="ComposantDescr" EntitySet="ComposantDescr" /></AssociationSet>
				</EntityContainer>
				<EntityType Name="Composant">
					<Key>
						<PropertyRef Name="IdComposant" />
					</Key>
					<Property Type="Int32" Name="IdComposant" Nullable="false" />
          <NavigationProperty Name="Descriptions" Relationship="TestNamespace.FK_dbo_ComposantDescr_dbo_Composant" FromRole="Composant" ToRole="ComposantDescr" />
				</EntityType>
				<EntityType Name="ComposantDescr">
					<Key>
						<PropertyRef Name="IdComposant" />
						<PropertyRef Name="TypeDescription" />
					</Key>
					<Property Type="Int32" Name="IdComposant" Nullable="false" />
					<Property Type="Int32" Name="TypeDescription" Nullable="false" />
					<Property Type="String" Name="Description" MaxLength="Max" FixedLength="false" Unicode="true" />
          <NavigationProperty Name="Composant" Relationship="TestNamespace.FK_dbo_ComposantDescr_dbo_Composant" FromRole="ComposantDescr" ToRole="Composant" />
				</EntityType>
        <Association Name="FK_dbo_ComposantDescr_dbo_Composant">
          <End Type="TestNamespace.Composant" Role="Composant" Multiplicity="1" >
            <OnDelete Action="Cascade" /></End>
          <End Type="TestNamespace.ComposantDescr" Role="ComposantDescr" Multiplicity="*" >
            </End>
          <ReferentialConstraint>
            <Principal Role="Composant">
              <PropertyRef Name="IdComposant" /></Principal>
            <Dependent Role="ComposantDescr">
              <PropertyRef Name="IdComposant" /></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="TestStoreNamespaceContainer" CdmEntityContainer="TestEntities">
    <EntitySetMapping Name="Composant">
      <EntityTypeMapping TypeName="IsTypeOf(TestNamespace.Composant)">
        <MappingFragment StoreEntitySet="Composant">
          <ScalarProperty Name="IdComposant" ColumnName="IdComposant" />
        </MappingFragment>
      </EntityTypeMapping>
    </EntitySetMapping>
    <EntitySetMapping Name="ComposantDescr">
      <EntityTypeMapping TypeName="IsTypeOf(TestNamespace.ComposantDescr)">
        <MappingFragment StoreEntitySet="ComposantDescr">
          <ScalarProperty Name="IdComposant" ColumnName="IdComposant" />
          <ScalarProperty Name="TypeDescription" ColumnName="TypeDescription" />
          <ScalarProperty Name="Description" ColumnName="Description" />
        </MappingFragment>
      </EntityTypeMapping>
            <EntityTypeMapping TypeName="TestNamespace.ComposantDescr">
              <ModificationFunctionMapping>
                <UpdateFunction FunctionName="TestStoreNamespace.sp_ComposantDescr_U" >
                  <ScalarProperty Name="Description" ParameterName="pDescription" Version="Current" />
                  <ScalarProperty Name="TypeDescription" ParameterName="pTypeDescription" Version="Original" />
                  <ScalarProperty Name="IdComposant" ParameterName="pId" Version="Original" /></UpdateFunction></ModificationFunctionMapping></EntityTypeMapping>
    </EntitySetMapping>
  </EntityContainerMapping>
</Mapping></edmx:Mappings>
	</edmx:Runtime>
	<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="False" />
				<DesignerProperty Name="EnablePluralization" Value="False" />
				<DesignerProperty Name="IncludeForeignKeysInModel" Value="True" />
			</DesignerInfoPropertySet>
		</Options>
		<Diagrams>
			<Diagram Name="TestEntities" ZoomLevel="95">
				<EntityTypeShape EntityType="TestNamespace.Composant" Width="2.125" PointX="3.25" PointY="1.625" Height="1.4033821614583339" />
				<EntityTypeShape EntityType="TestNamespace.ComposantDescr" Width="1.75" PointX="7.375" PointY="1.375" Height="1.787985026041667" />
        <AssociationConnector Association="TestNamespace.FK_dbo_ComposantDescr_dbo_Composant" >
          <ConnectorPoint PointX="5.375" PointY="2.3266910807291667" />
          <ConnectorPoint PointX="7.375" PointY="2.3266910807291667" /></AssociationConnector>
			</Diagram>
		</Diagrams>
	</Designer>
</edmx:Edmx>

and the T-Sql code for SqlExpress to create the database with datas :

CREATE TABLE [dbo].[Composant] (
    [IdComposant] int  NOT NULL
)
GO

CREATE TABLE [dbo].[ComposantDescr] (
    [IdComposant] int  NOT NULL,
    [TypeDescription] int  NOT NULL,
    [Description] nvarchar(max)  NULL
)
GO

ALTER TABLE [dbo].[Composant] WITH NOCHECK 
ADD CONSTRAINT [PK_Composant]
    PRIMARY KEY CLUSTERED ([IdComposant] ASC)
GO

ALTER TABLE [dbo].[ComposantDescr] WITH NOCHECK 
ADD CONSTRAINT [PK_ComposantDescr]
    PRIMARY KEY CLUSTERED ([IdComposant], [TypeDescription] ASC)
GO

ALTER TABLE [dbo].[ComposantDescr] WITH NOCHECK 
ADD CONSTRAint [FK_dbo_ComposantDescr_dbo_Composant]
    FOREIGN KEY ([IdComposant])
    REFERENCES [dbo].[Composant] ([IdComposant])
    ON DELETE CASCADE ON UPDATE CASCADE
GO
 
Create PROCEDURE [dbo].[sp_ComposantDescr_U]
   @pId int, @pTypeDescription int, @pDescription nvarchar(max)
AS
BEGIN
  
  SET NOCOUNT ON;

  -- Sample stored proc, the real one has more complexities
 
  Update Upd
	Set Description = @pDescription
  From
    dbo.ComposantDescr Upd
    Where Upd.IdComposant = @pId
      And Upd.TypeDescription = @pTypeDescription
END
GO

INSERT INTO [Test].[dbo].[Composant]
           ([IdComposant])
     VALUES
           (1)
GO

INSERT INTO [Test].[dbo].[ComposantDescr]
           ([IdComposant]
           ,[TypeDescription]
           ,[Description])
     VALUES
           (1
           ,0
           ,'0 description')
GO

INSERT INTO [Test].[dbo].[ComposantDescr]
           ([IdComposant]
           ,[TypeDescription]
           ,[Description])
     VALUES
           (1
           ,2
           ,'2 description')
GO<br/>

So how can I solve this problem? We are using lot of stored proc to update / insert / delete int our descriptions tables, and this is a big problem for us.

Answer 1

Hello,

Thanks for raising this and for the detailed repro. We are investigating if this is a bug. I will follow up in this thread with the outcome.

Diego

---

This posting is provided "AS IS" with no warranties, and confers no rights.

Answer 2

Hello,

We have confirmed this to be a bug in stored procedure support in Beta 2. We are currently working on a fix. I will let you know if we identify any workaround.

Again, thanks for raising this!
Diego

---

This posting is provided "AS IS" with no warranties, and confers no rights.

Answer 3

Has a workaround been identified for this? 

Thanks,

Jon Cain

Answer 4

Right now we try to not change 2 children at the same time. When 2 children changed, we play with the objectstatemanager to the ensure only one children will update at the same time and are using a transaction scope to ensure sync... It's not great, but at least we can advance. If someone found better, I would be happy to know.