Yen's Blog

Lens, Wheels, Skates, Keyboard

Edit Collection With LINQ to SQL

Let’s say for a shopping cart we have an Order entity, a Product entity and an OrderDetails entity which serves as a bridge. The OrderDetails entity has OrderId and ProductId fields to associate products with orders. We also have an Order domain object which is passed from the business logic layer to the repository layer.

Since products can be added or removed from an order, we need to manage the order details for each Order. We can either update OrderDetails directly in the database or via the Order.OrderDetails collection.

Deleting

When handling deletions, we can determine products removed from the order (i.e., products which are present in the database object but not in the domain object) as follows:

1
2
3
4
var toDelete =
  from od in order.OrderDetails
  where !domainOrder.OrderDetails.Any(o => o.ProductId == od.ProductId)
  select od;

Deleting the order details directly from the database works:

1
DataContext.OrderDetails.DeleteAllOnSubmit(toDelete);

However, trying to delete the order details using the Order.OrderDetails collection removes only the relationship between Order and OrderDetails. This would fail because of the foreign key constraint.

1
2
foreach (var item in toDelete)
     order.OrderDetails.Remove(item)

Adding

When handling additions, we can determine products added to the order (i.e., products which are present in the domain order object but not in the database order object) as follows:

1
2
3
4
5
6
7
8
var toAdd =
  from od in domainOrder.OrderDetails
  where !order.OrderDetails.Any(o => o.ProductId == od.ProductId)
  select new OrderDetails
             {
          OrderId = order.Id,
          ProductId = od.ProductId
             };

Here a different scenario applies. Adding directly to the database succeeds, but the Order.OrderDetails collection isn’t updated to show the added Products.

1
DataContext.OrderDetails.InsertAllOnSubmit(toAdd);

To ensure the Order.OrderDetails collection is also up-to-date, we’d have to add the order details via the collection.

1
order.OrderDetails.AddRange(toAdd);