locked
Stored Procedure Mapping Result Column Binding RRS feed

  • Question

  • Using a simple insert stored procedure with the output parameter SELECT SCOPE_IDENTITY() AS Id allows me to map the Id field in the Result Column Binding on the associated C# entity's Insert Function using the Stored Procedure Mapping feature in Entity Framework. When I execute this insert stored proc in my C# code the entity is correctly updated with the Id field from the db. 

    However, if I try and recreate this exact same setup but using a stored proc that contains both an insert and update statement i.e. 

    IF EXISTS (SELECT 1 FROM [TableA] WHERE [Id] = @Id)

    BEGIN
    UPDATE [TableA] SET [X] = @X WHERE [Id] = @Id
    END
    ELSE
    BEGIN
    INSERT INTO [TableA] ([X]) VALUES (@X)
    END

    IF @@ERROR = 0
    COMMIT TRANSACTION
    ELSE
    ROLLBACK TRANSACTION

    SELECT SCOPE_IDENTITY() AS Id

    then I see the following error when attempting to execute the sp in my C# code: "InnerException = {"Unable to determine a valid ordering for dependent operations. Dependencies may exist due to foreign key constraints, model requirements, or store-generated values."} 

    When I run the sp in SQL Server Mgmt Studio it works fine and returns the Id as expected, no different to the simple insert procedure example that does work. 

    Is there a way around this, or is this a bug in Entity Framework? I don't see why the SQL code should impact EF, providing the output parameter being returned is the same. 

    Thursday, October 1, 2020 12:55 PM

All replies

  • Hi jamesdbrown79,
    The problem is to use Manage type stored procedures that can handle inserts and updates at the same time. In any case, you pass the primary key field to Sproc.
    Entity Framework considers this to be an association (maybe a foreign key), so it gives the above error. When you convert it to use separate "insert" and "update" stored procedures (where the insert fails the PK), everything works fine.
    Therefore, if you encounter the above error, make sure that the PK (PRIMARY KEY) is not mapped during the insertion process.
    Best Regards,
    Daniel Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Friday, October 2, 2020 6:51 AM