2

In my model I have set defaultPersistenceEnforce="true", because I want the database to enforce referential integrity. However, I get an error when deleting an entity.

Here is my model:

<cf:entity name="Keyword">
  <cf:property name="Id" key="true" />
  <cf:property name="Value" typeName="string" />
</cf:entity>

<cf:entity name="BaseOrderline" abstract="true">
  <cf:property name="Id" key="true" />
</cf:entity>

<cf:entity name="Orderline" baseTypeName="{0}.BaseOrderline">
  <cf:property name="Order" typeName="{0}.Order" />
  <cf:property name="Keywords" typeName="{0}.KeywordCollection" />
</cf:entity>

<cf:entity name="Order">
  <cf:property name="Id" key="true" />
  <cf:property name="Keywords" typeName="{0}.KeywordCollection" />
  <cf:property name="Orderlines" typeName="{0}.OrderlineCollection" cascadeDelete="Before" cascadeSave="After" />
</cf:entity>  

Here is my code:

Keyword keyword = new Keyword();
keyword.Save();

Order order = new Order();
order.Keywords.Add(keyword);

Orderline orderline = new Orderline();
orderline.Keywords.Add(keyword);
order.Orderlines.Add(orderline);

// Save the order and its order lines.
order.Save();

// Delete the order and its order lines.
order.Delete();

I get the following error:

The DELETE statement conflicted with the REFERENCE constraint "FK_Orl_Bas_Bas_Ore". The conflict occurred in table "dbo.Orderline_Keywords_Keyword", column 'BaseOrderline_Id'.
The DELETE statement conflicted with the REFERENCE constraint "FK_Ore_Ord_Ord_Ord". The conflict occurred in table "dbo.Orderline", column 'Orderline_Order_Id'.
Concurrency error in procedure Order_Delete

Everything goes fine if I remove orderline.Keywords.Add(keyword); from the code.

Here is the code of the stored procedure Order_Delete:

USE [PersistenceIdentity]
GO
/****** Object:  StoredProcedure [dbo].[Order_Delete]    Script Date: 18-7-2016 20:41:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Order_Delete]
(
 @Order_Id [uniqueidentifier],
 @_rowVersion [rowversion]
)
AS
SET NOCOUNT ON
DECLARE @error int, @rowcount int
DECLARE @tran bit; SELECT @tran = 0
IF @@TRANCOUNT = 0
BEGIN
 SELECT @tran = 1
 BEGIN TRANSACTION
END
DELETE [Order_Keywords_Keyword] FROM [Order_Keywords_Keyword] 
    WHERE ([Order_Keywords_Keyword].[Order_Id] = @Order_Id)
SELECT @error = @@ERROR, @rowcount = @@ROWCOUNT
DELETE [BaseOrderline] FROM [BaseOrderline]
    INNER JOIN [Orderline] ON ([BaseOrderline].[BaseOrderline_Id] = [Orderline].[BaseOrderline_Id])
            LEFT OUTER JOIN [Order] ON ([Orderline].[Orderline_Order_Id] = [Order].[Order_Id]) 
    WHERE ([Order].[Order_Id] = @Order_Id)
SELECT @error = @@ERROR, @rowcount = @@ROWCOUNT
DELETE [Orderline] FROM [Orderline]
    LEFT OUTER JOIN [Order] ON ([Orderline].[Orderline_Order_Id] = [Order].[Order_Id])
            LEFT OUTER JOIN [Orderline] [Orderline$1] ON ([Order].[Order_Id] = [Orderline$1].[Orderline_Order_Id]) 
    WHERE ([Order].[Order_Id] = @Order_Id)
SELECT @error = @@ERROR, @rowcount = @@ROWCOUNT
DELETE [Order] FROM [Order] 
    WHERE (([Order].[Order_Id] = @Order_Id) AND ([Order].[_rowVersion] = @_rowVersion))
SELECT @error = @@ERROR, @rowcount = @@ROWCOUNT
IF(@rowcount = 0)
BEGIN
    IF @tran = 1 ROLLBACK TRANSACTION
    RAISERROR (50001, 16, 1, 'Order_Delete')
    RETURN
END
IF @tran = 1 COMMIT TRANSACTION

RETURN

As you can see the following lines are missing in this stored procedure:

DELETE [Orderline_Keywords_Keyword] FROM [Orderline_Keywords_Keyword] 
    WHERE ([Orderline_Keywords_Keyword].[Orderline_Id] = @Orderline_Id)
Willem
  • 111
  • 4
  • I cannot reproduce your issue. The stored procedure `Order_Delete` starts by deleting rows in the table `Order_Keywords_Keyword` to prevent referential integrity issues. Can you show us the code of the stored procedure? – meziantou Jul 18 '16 at 08:29
  • I have updated my question with the code of the stored procedure. – Willem Jul 18 '16 at 18:55
  • Everything goes fine if I remove orderline.Keywords.Add(keyword); from the code. – Willem Jul 18 '16 at 19:44
  • Do you have a solution? Why are the records in Orderline_Keywords_Keyword not deleted? – Willem Jul 19 '16 at 14:15
  • This scenario doesn't seem to be currently supported. As a workaround you can create a [RAW method](https://www.softfluent.com/documentation/Methods_Raw.html) to replace the generated procedure or delete records manually before calling `order.Delete()` – meziantou Jul 21 '16 at 14:30

0 Answers0