How to implement a many-to-many relationship using Linq to Sql ? Part II : add/remove support

In my previous post (https://blogs.msdn.com/mitsu/archive/2007/06/21/how-to-implement-a-many-to-many-relationship-using-linq-to-sql.aspx), I had proposed a simple solution for implementing many-to-many relationships using Linq to Sql.
Actually, this solution is a generic Linq solution. It's also working with just memory collections (not only Linq to Sql).

Many of you asked me for add/remove support.

Let's clarify a few things:

The current solution offers extensions to IList<T> and IBindingList.
Actually, the current solution is good enough when extending IList<T> and is very basic for IBindingList. Proxying (simulating) IList<T> is quite easy because we don't have a lot of possible technical issues. Proxying IBindingList with add/remove support is much more complicated.

For example, as we are virtually changing the item type (see ListSelector class), the notion of sorting becomes quite strange (we have to sort an OrderDetails collection from a Product property !!!).

Similarly, adding and removing don't have a real meaning from a Product entity.

In a many to many relationship, writing order.Products.Remove(n) should probably mean order.OrderDetails[n].Remove(). Of course we do not want to delete the associated product. The functional meaning is 'detaching' a product from it's many to many relationship with an order.

IList<T> contains the methods for adding and removing, so that's where we have to update the code. IBindingList is adding list change notifications necessary for binding.

The first thing is, there no universal solution because we don't know the real nature of the relationship. Sometimes, the link table has no functional meaning other than making the relationship. In that cases, the link table is only grouping foreign keys to join each table of the many-to-many relationship. Some other times, the link table contains other columns that have a functional meaning. For example, in a link table between an order and a product, the price is duplicated so it belongs to this specific order and stores the price at the time we had made the order.

What I am trying to say is we can not use an automatic logic to implement adding/removing.

So what I am proposing is to catch adding and removing from our IList entry points and to delegate those actions to external code.

Let's start with adding:

 public class ListSelector<TSource, T> : IList<T>, IList
{
...
    protected Action<IList<TSource>, T> onAdd;
...
    public void Add(T item)
    {
        if (onAdd != null)
            onAdd(source, item);
    }

In the northwind case (Order/OrderDetails/Product), a good implementation for this delegate could be:

 (ods, p) => ods.Add(
  new Order_Detail { OrderID = currentOrder.OrderID, Product = p, Quantity=1 })

Of course we can notice that the goal is not to insert a Product (T) but an OrderDetail.

To make it easier to use, I have also added a new constructor so we can write:

 public partial class Order
{
    private ListSelector<Order_Detail, Product> products = null;
    public ListSelector<Order_Detail, Product> Products
    {
        get 
        {
            if (products == null)
                products = this.Order_Details.GetNewBindingList()
                   .AsListSelector<Order_Detail, Product>(
                       od => od.Product,
                       (ods, p) => ods.Add(
                           new Order_Detail {
                               OrderID = this.OrderID,
                               Product = p, Quantity=1 })

We can notice that this syntax allows you to custom the OrderDetail creation. It's important because in our Northwind case, we had not only foreign keys but also Quantity and some other columns in the link table (OrderDetails). We can also notice that we had to know the Order to get the OrderID field. Fortunately, the lamba expression syntax allows us to share 'this.OrderID' from the host method scope. (same if we had used an anonymous method).

I will explain later in this post why I have used the GetNewBindingList method.

Now what about removing ? Removing is more complex because we have to find the entity before we remove it. (We start from a Product and we need to remove the corresponding OrderDetail).

The structure is very close to adding:

     ...
    protected Action<IList<TSource>, T> onRemove;
    ...
    public bool Remove(T item)
    {
        if (onRemove != null)
        {
            onRemove(source, item);
            return true;
        }
        else
            return false;
    }

and the ListSelector is created that way:

 public partial class Order
{
    private ListSelector<Order_Detail, Product> products = null;
    public ListSelector<Order_Detail, Product> Products
    {
        get 
        {
            if (products == null)
                products = this.Order_Details.GetNewBindingList()
                   .AsListSelector<Order_Detail, Product>(
                       od => od.Product,
                       (ods, p) => ods.Add(
                           new Order_Detail {
                               OrderID = this.OrderID,
                               Product = p, Quantity=1 },
                       delegate(IList<Order_Detail> ods, Product p)
                       {
                           var odToRemove = ods.Where(
                               od => od.ProductID == p.ProductID).Single();
                           Singleton<NorthwindDataContext>.Default
                               .Order_Details.DeleteOnSubmit(odToRemove);
                           ods.Remove(odToRemove);
                       })
            return products;
        }
    }

As we need more that one instruction, I could not keep the lambda expression syntax. The anonymous method is quite the same here.

First we have to find the OrderDetail entity corresponding to the product.

Then, delete this entity. This is an issue because we have to 'talk' to Linq to Sql to declare this entity as to be removed on next SubmitChanges call. The issue is we don't have access to the datacontext at this point. I can imagine many different scenarios to make it visible here in the Order class but not to make this sample to much complicated, I have decided to give access to the current NorthwindDataContext through a static property.

Depending on your architecture, you can imagine other solutions. To preserve domain isolation, I would recommend to use an interface that would make abstraction of the persistence nature (Linq to Sql/DataContext/DeleteOnSubmit).

Just as a remark, here is the code of the generic singleton pattern I have used:

 public class Singleton<T> where T : class, new()
{
    private static T defaultInstance = null;
    public static T Default
    {
        get 
        {
            if (defaultInstance == null)
                defaultInstance = new T();    
            return defaultInstance; 
        }
    }
}

Last thing to explain, why did I have used 'this.Order_Details.GetNewBindingList().AsListSelector<Order_Detail, Product>(...);' ?

I have two AsListSelector extension methods. The first is extending IList<T> and the second one, IBindingList. As the extension method resolution is based on the reference type, I had to call AsListSelector from an IBindingList, so I can get binding notifications (add/remove).

The EntitySet class does not implement IBindingList but can provide an internal class that does !
When binding directly a Linq to Sql query to a winforms control, this class is provided implicitly:

 grid.DataSource = q;

is equivalent to:

 grid.DataSource = ((q as IListSource).GetList() as IBindingList);

To avoid multiple query execution when binding q many times, the result is cached in memory.

The GetNewBindingList method allows to get a fresh BindingList at each call.

Now we are done !

Let's modify the UI :

image

You have to use the main menu to submit the changes to the database 'Data>SubmitChanges'.

The whole source code is available on code gallery: https://code.msdn.microsoft.com/linqtosqlmanytomany