Without Alter Command, Delete Cascade Rows

Question

I have to delete a product and its images. Images are in seperate table and productId is acting as foreign key. Here is the single queries to do so.

string deleteImages = @"DELETE FROM [ProductsImages] WHERE ProductId IN (SELECT ProductId FROM [Products] WHERE ProductId = @ProductId)";
string deleteProduct = @"DELETE FROM [Products] WHERE ProductId = @ProductId";
       db.ExecuteNonQuery(deleteImages);
       db.ExecuteNonQuery(deleteProduct);  

I am not getting the way to avoid writing 2 different queries, Is there any help to delete cascade without alter command?

Answer 1

This is something you can define in the database. Just define the foreign key relationship between Products and ProductsImages as "ON DELETE CASCADE". Deleting the product will then automatically delete all associated ProductImages.

Answer 2

If you don't want to change the index, you can avoid two round trips by performing multiple operations in one command:

string deleteStuff = @"
  DELETE FROM [ProductsImages] WHERE ProductId = @ProductId;
  DELETE FROM [Products] WHERE ProductId = @ProductId;"
db.ExecuteNonQuery(deleteStuff);