How delete in specific order

I have a column in table which reference the same table (as a parent-child relationship).

When I want to purge like this (C# Code):

do
{
    commandRows = context.Database.ExecuteSqlCommand("DELETE top(5000) from IssuerRequests WHERE discriminator='IssuerRequest' and Issuer_ID = @Issuer_ID and ExpireDate < @LimitDate",
        new SqlParameter("@Issuer_ID", customer.ID), new SqlParameter("@LimitDate", LimitDate));
    AffectedRows += commandRows;
}
while (commandRows >= 5000);

An exception appears:

The DELETE statement conflicted with the SAME TABLE REFERENCE constraint "FK_dbo.IssuerRequests_dbo.IssuerRequests_LinkedRequestForMultichannel_ID". The conflict occurred in database "xxx", table "dbo.IssuerRequests", column 'LinkedRequestForMultichannel_ID'. The statement has been terminated.

This error is normal because if we try to delete a line referenced by another one, the system doesn't allow it.

So I manage a script in order to purge manually :

USE [Database]

DECLARE @DaysAfterExpiring INT
DECLARE @Issuer_ID INT
DECLARE @DateAfterExpiring DATETIME

SET @DaysAfterExpiring = 35
SET @DateAfterExpiring = DATEADD(day, -(@DaysAfterExpiring), GETUTCDATE())

SELECT @Issuer_ID = [ID] FROM IssuerDescs WHERE [DisplayName] LIKE '%toto%'

DECLARE @MoreRowsToDelete BIT
SET @MoreRowsToDelete = 1

-- With the relationship
WHILE @MoreRowsToDelete = 1
BEGIN
    DELETE TOP (1000) FROM IssuerRequests WHERE discriminator='IssuerRequest' AND Issuer_ID = @Issuer_ID AND [ExpireDate] < @DateAfterExpiring  AND LinkedRequestForMultiChannel_ID IS NOT NULL
    IF NOT EXISTS (SELECT TOP 1 * FROM IssuerRequests WHERE discriminator='IssuerRequest' AND Issuer_ID = @Issuer_ID AND [ExpireDate] < @DateAfterExpiring  AND LinkedRequestForMultiChannel_ID IS NOT NULL)
    BEGIN
        SET @MoreRowsToDelete = 1
    END
END

-- Without the relationship
SET @MoreRowsToDelete = 1
WHILE @MoreRowsToDelete = 1
BEGIN
    DELETE TOP (1000) FROM IssuerRequests WHERE discriminator='IssuerRequest' AND Issuer_ID = @Issuer_ID AND [ExpireDate] < @DateAfterExpiring  AND LinkedRequestForMultiChannel_ID IS NULL
    IF NOT EXISTS (SELECT TOP 1 * FROM IssuerRequests WHERE discriminator='IssuerRequest' AND Issuer_ID = @Issuer_ID AND [ExpireDate] < @DateAfterExpiring  AND LinkedRequestForMultiChannel_ID IS NULL)
    BEGIN
        SET @MoreRowsToDelete = 1
    END
END

Do you know if it's possible to avoid double loops and delete 5000 lines per action without doing the distinction?

I thought about "ORDER BY" in delete with CTE but I'm not sure about the result.

I will try this possible solution :

USE [database]

DECLARE @DaysAfterExpiring INT
DECLARE @Issuer_ID INT
DECLARE @DateAfterExpiring DATETIME

SET @DaysAfterExpiring = 35
SET @DateAfterExpiring = DATEADD(day, -(@DaysAfterExpiring), GETUTCDATE())

SELECT @Issuer_ID = [ID] FROM IssuerDescs WHERE [DisplayName] LIKE '%toto%'

DECLARE @MoreRowsToDelete BIT
SET @MoreRowsToDelete = 1

-- With the relationship first then without the relationship
WHILE @MoreRowsToDelete = 1
BEGIN
    DELETE T FROM (SELECT TOP (1000) * FROM IssuerRequests WHERE discriminator='IssuerRequest' AND Issuer_ID = @Issuer_ID AND [ExpireDate] < @DateAfterExpiring ORDER BY ISNULL(LinkedRequestForMultiChannel_ID, '') DESC) AS T
    IF NOT EXISTS (SELECT TOP 1 * FROM IssuerRequests WHERE discriminator='IssuerRequest' AND Issuer_ID = @Issuer_ID AND [ExpireDate] < @DateAfterExpiring)
    BEGIN
        SET @MoreRowsToDelete = 0
    END
END

Solution

The example at the end of my original post works as attended. Because the script will delete first every lines with any FK reference, and then lines without any FK reference. I'm using batch of 1 000 lines in order to avoid to lock the table.

So the solution is to use CTE or specific query after the FROM (as comments, it's the same result) clause.