I've read most of the posts on this, but it's still a little blurry to me on the best way to delete "UpTo".  Is there an ES way of handling this or do I just go entity by entity?

Here's my schema as an example:

I want to delete all related records when I delete an Order.  All the children work fine, such as OrderItems, OrderItemImageFiles, OrderNote, and OrderTransaction.  But I also want to delete the related Shipping Address and Billing Address and Credit Card.

Here's the code I'm using presently:

1        private void DeleteOrder()
2        {
3            if (_ord.BillingAddressID != null)
4            {
5                Address addBilling = new Address();
6                addBilling.LoadByPrimaryKey((int)_ord.BillingAddressID);
7                addBilling.MarkAsDeleted();
8                addBilling.Save();
9            }
10   
11           if (_ord.ShippingAddressID != null)
12           {
13               Address addShipping = new Address();
14               addShipping.LoadByPrimaryKey((int)_ord.ShippingAddressID);
15               addShipping.MarkAsDeleted();
16               addShipping.Save();
17           }
18   
19           if (_ord.CreditCardID != null)
20           {
21               CreditCard cc = new CreditCard();
22               cc.LoadByPrimaryKey((int)_ord.CreditCardID);
23               cc.MarkAsDeleted();
24               cc.Save();
25           }
26   
27           // These didn't work, but I'm probably doing it wrong.
28           //_ord.UpToAddressByBillingAddressID.MarkAsDeleted();
29           //_ord.UpToAddressByShippingAddressID.MarkAsDeleted();
30           //_ord.UpToCreditCardByCreditCardID.MarkAsDeleted();
31           
32           _ord.MarkAsDeleted();
33           _ord.Save();
34       }
35   

Is there an easier, ES built-in way of handling this? The commented section wasn't working so now I use the code as shown above. I am getting a constraint error, but that's in SQL Server. I think I can fix that.

Any suggestions would be appreciated.

Thanks,

Hi King

Just a quick reply (before I get distracted with other things) - more about the schema than anything else.  It looks like you have a 1-m relationship between your order and, for example, the delivery address meaning that the same address could be used across many different orders yet the code (and question) are saying that you want to delete this address based of a specific order is that right?  If so then imagine one address is associated with 3 orders, you delete one of the orders which then tries to delete the address, as the address is still related to two other orders you're either going to have to delete those two other orders first, or you'll get referrential inconsistencies in your db (2 orders with no related address).

I'm sure the code you've posted should do what you're asking (it looks about right, haven't tested as I don't have time at the moment) but I suppose I'm just pointing out a potential "gotcha" to look out for

 Cheers

Martin

pritcham:

Hi King

Just a quick reply (before I get distracted with other things) - more about the schema than anything else.  It looks like you have a 1-m relationship between your order and, for example, the delivery address meaning that the same address could be used across many different orders yet the code (and question) are saying that you want to delete this address based of a specific order is that right?  If so then imagine one address is associated with 3 orders, you delete one of the orders which then tries to delete the address, as the address is still related to two other orders you're either going to have to delete those two other orders first, or you'll get referrential inconsistencies in your db (2 orders with no related address).

I'm sure the code you've posted should do what you're asking (it looks about right, haven't tested as I don't have time at the moment) but I suppose I'm just pointing out a potential "gotcha" to look out for

 Cheers

Martin

Martin,

You're correct in the relationship, but it would just be one address per order.  But you have gotten me thinking.   

I think if I did change the schema (and I feel I'm getting a little off topic but I might be answering my own question in doing so), so that there's a Customer table and the Address is related to it, and the Customer is related to the Orders, it might work better.  Since the Address is related to the Customer anyway, it actually makes more sense.

So one more question that is more on topic, if I want to delete the Order and all related tables to it such as OrderItems, OrderItemImageFile, OrderNote, and Transaction (assuming that in the new schema Customer is a one-to-many relationship to Orders, as Address is now), can I just use the code below to handle the deletion of all related table data, or do I also need to go into SQL Server and mark the relationships as Cascade?

1    Orders currentOrder = new Orders();
2    currentOrder.LoadByPrimaryKey(1);
3    currentOrder.MarkAsDeleted();
4    currentOrder.Save();

Thanks, 

Yeah you can't save any UpTo's so you need to load those collections manually just like you are doing. Though as pritcham noted if you keep the many to one relationship you might want to check the reference count before deleting like this

if (_ord.ShippingAddressID != null)
{
    Address addShipping = new Address();
    if (addShipping.LoadByPrimaryKey((int)_ord.ShippingAddressID));
    {
        if (addShipping.OrdersByShippingAddressID.Count == 1)
        {
            // There is only one order referencing this address so it is clear to delete it
            addShipping.MarkAsDeleted();
            addShipping.Save();
        }
    }
}

Also you might consider using an esTransactionScope like this:

using (EntitySpaces.Interfaces.esTransactionScope scope = new EntitySpaces.Interfaces.esTransactionScope())
{
    //do all of your ES stuff and when you are done call scope.Complete
    scope.Complete();
}

Scott.Schecter:

Setting the cascade delete on the SQL Server side is the preferred way of handling this.

That's what I thought.  I already had that in place but I didn't know if there was a better ES way of doing it. 

PurpleFlux:

Yeah you can't save any UpTo's so you need to load those collections manually just like you are doing. Though as pritcham noted if you keep the many to one relationship you might want to check the reference count before deleting like this

 

Code:

if (_ord.ShippingAddressID != null)
{
    Address addShipping = new Address();
    if (addShipping.LoadByPrimaryKey((int)_ord.ShippingAddressID));
    {
        if (addShipping.OrdersByShippingAddressID.Count == 1)
        {
            // There is only one order referencing this address so it is clear to delete it
            addShipping.MarkAsDeleted();
            addShipping.Save();
        }
    }
}

Also you might consider using an esTransactionScope like this:

Code:

using (EntitySpaces.Interfaces.esTransactionScope scope = new EntitySpaces.Interfaces.esTransactionScope())
{
    //do all of your ES stuff and when you are done call scope.Complete
    scope.Complete();
}

I've actually reworked the schema a bit to be more flexible and intuitive with the customer information.  I won't need to delete "UpTo"s now, so it'll work better.

And I do have the TransactionScope working on my other saves.  I just didn't have it in here because I wasn't quite done with it. 

PurpleFlux:

Would that work if multiple orders references the same Address though? Certainly it would in a one-to-one relationship.

I've reworked the schema so the Shipping address info and Billling address info is now in the Orders table, and I have a separate Customer table to persist customer records for all orders.  So this delete problem is no longer an issue.   

Thanks everyone for smacking me upside the head in order to see clearly.   

We spent much time incorporating hierarchal deletes into our custom templates and base classes (in addition to automatic audit records). So far so good but I would feel better if this were built into the underlying framework. Has my vote for a feature request.

ajhops,

I'm fine with the cascading deletes option in SQL Server, since I have full control over the database, that's not a problem for me.

Thanks,