MegaMark MegaMark - 1 month ago 29
C# Question

Deleting multiple rows based on where clause using FluentMigrator

Given a Payment table, a PaymentStatus table, and a PaymentHistory table, I need to delete a status from the PaymentStatus table that the Payment and PaymentHistory tables have associated records for. So naturally that means delete the history table before the payment table. Of course the historical data has multiple records of payments in different statuses. What I need is a where clause for the history table delete... the sql would look kind of like this:

DELETE FROM PaymentHistory
WHERE PaymentId IN
(SELECT Id FROM Payment WHERE PaymentStatusCode = 'Processing')


What I don't understand is how to achieve this in FluentMigrator. This is what I have so far but there are no .Where() or other extension methods available after the .FromTable()

Delete.FromTable("PaymentHistory").Row(new {PaymentStatusCode = "Processing"});


Thanks in advance for the help.

Answer

Now I feel stupid for asking....

const string cascadeDeleteScript = 
@"DELETE FROM PaymentHistory WHERE PaymentId IN 
              (SELECT Id FROM Payment WHERE PaymentStatusCode = 'Processing') ";
Execute.Sql(cascadeDeleteScript);
Comments