Hi,

Is there a way to remove related entities without fetching them?

I'm working with the Northwind database and would like to remove a Supplier.

But adapter.DeleteEntity(Supplier) gives me problems in the foreignkeys (what is pretty normal, because products are still related with the Supplier).

So I tried adapter.DeleteEntity(Supplier.Products) but because the products aren't fetched this returned me:

Unable to cast object of type 'NorthwindApp.HelperClasses.EntityCollection`1[NorthwindApp.EntityClasses.ProductsEntity]' to type 'SD.LLBLGen.Pro.ORMSupportClasses.IEntity2'.

I was hoping that I'm able to delete all the related Products before I start to delete the Supplier but it looks pointless to first fetch all the Products that I want to delete.

Any ideas on this matter?

Use adapter.DeleteEntitiesDirectly(), which accepts a filter. Or use database Cascade Delete.

I hoped this was my solution:

To delete the supplier I have to delete the products first where the supplierId = the same as the supplier I want to delete. But to delete my products I first have to delete the Order Details where the productId = the same as the products related to my supplier.

So I thought:

                adapter.DeleteEntitiesDirectly("OrderDetailsEntity", New RelationPredicateBucket(OrderDetailsFields.ProductId= ProductsFields.ProductId))
                adapter.DeleteEntitiesDirectly("ProductsEntity", obj.GetRelationInfoProducts)
                adapter.DeleteEntity(obj)

But that doesn't seem to be right because the query generated from the first DeleteEntitiesDirectly is:

DELETE FROM [Northwind].[dbo].[Order Details] WHERE ( ( [Northwind].[dbo].[Products].[ProductID] = [Northwind].[dbo].[Order Details].[OrderID]))

Which makes sense given the RelationPredicateBucket I passed on but that is not what I need in this case.

You know the supplierId you want to delete, so you can delete the Products first based on the SupplierId value. But you don't know which products are they to delete their OrderDetails first.

So wither you have to fetch the Products or their Ids first, to be able to delete their OrderDetails.

Or just use Cascading deletes between Supplier & Products, and between Products and OrderDetails.

So it's not possible to do the following?:

delete from [Order Details] where OrderID in (select OrderID from Orders where CustomerID = TheCustomerIWantToDelete)
delete from Orders where CustomerID = TheCustomerIWantToDelete
delete from Customers where CustomerID = TheCustomerIWantToDelete

So it's not possible to do the following?:

delete from [Order Details] where OrderID in (select OrderID from Orders where CustomerID = TheCustomerIWantToDelete) delete from Orders where CustomerID = TheCustomerIWantToDelete delete from Customers where CustomerID = TheCustomerIWantToDelete

Yes you can do that.

Is there a way to execute this in LLBLGEN or do I have to go native SQL on this one.

delete from [Order Details] where OrderID in (select OrderID from Orders where CustomerID = TheCustomerIWantToDelete)

            using (var adapter = new DataAccessAdapter())
            {
                var bucket = new RelationPredicateBucket();
                bucket.PredicateExpression.Add(new FieldCompareSetPredicate(
    OrderDetailsFields.OrderID, null, OrderFields.OrderID, null,
    SetOperator.In, (OrderFields.CustomerID == XYZ)));
                adapter.DeleteEntitiesDirectly("OrderDetailsEntity", bucket);
            }

delete from Orders where CustomerID = TheCustomerIWantToDelete

            using (var adapter = new DataAccessAdapter())
            {
                var bucket = new RelationPredicateBucket(OrdersFields.CustomerId == XYZ);
                adapter.DeleteEntitiesDirectly("OrdersEntity", bucket);
            }

delete from Customers where CustomerID = TheCustomerIWantToDelete

            using (var adapter = new DataAccessAdapter())
            {
                var bucket = new RelationPredicateBucket(CustomerFields.CustomerId == XYZ);
                adapter.DeleteEntitiesDirectly("CustomerEntity", bucket);
            }

Works like a charm, thanks for the quick reply