Many to many relationships in LINQ-to-SQL (part 2)

Last time, I examined one of the limitations of LINQ to SQL - the lack of automatic tools to cut out the junction table in a many-to-many relationship. I examined the reasons behind this limitation and concluded that providing automation for the simplest case ( foreach (Product p in order.Products) ) left us with two very important issues:

• We currently don't have a way of sending changes to the object model back to the database - order.Products.Add( ... won't result in a new record being added to the junction table.
• We need a way to access properties of the relationship beyond references to the two related entities (e.g. "Quantity" in the Order_Detail table relating Orders and Products).

In this article, I examine ways to solve these issues.

Let's take a look at our schema again:

If we don't need to interact with the data often (e.g. if we just need to DataBind it once), and if we don't need to update or delete, we can make use of anonymous types to get away with the really simple:-

Order o = ...
MyGridView.DataSource = from od in o.Order_Details
                        select new
                        {
                           od.Product.ProductID,
                           od.Product. ...,
                           od.Quantity
                        };
MyGridView.DataBind();

There are a lot of cases where this is all we need. However, the new type we're defining here isn't a Product, nor is it an Order_Detail. The properties of our anonymous type are read-only so we can't add new products to this order, we can't take prodcuts away from this order, we can't map changes made to this instance back to the database by just calling SubmitChanges().

In essence, we're not getting any leverage out of our ORM at all. Once the data has been mapped, it's detached from the database. We'd need to re-attach it (the equivalent of doing SELECT * FROM table WHERE id = someValueWeKnow) in order to do anything interactive with it.

I always find that it's useful to think about the code you want to be able to write, and what you want to happen. In this case, we want to be able to write:-

order.Products.Add(product);

However, we actually want this code to add an Order_Detail record to the database, not a product. We're going to have to define our own collection type for order.Products and handle that processing in there.

The trouble is, on the one hand we want a generic solution - it's no good if we have to define our own collection for order.Products, and then again for products.Orders - but on the other hand, our generic solution is going to need to know how to handle joining these two entities together - specifically, how to add a record to the junction table.

This means we have to catch the Add and Remove events inside our new collection, and delegate the implementation to external code which we'll write in the entity class. Mitsu Furuta suggests the following solution (I've not shown the entire source here - you can download it from his blog - and I've altered some of the language for consistency with the rest of my article) which I'll talk you through:-

public class JunctionList<TJunction,TRight> : IList<TRight>
{

  protected IList<TJunction> junction;
  protected Func<TJunction,TRight> mappingFunction;
  protected IEnumerable<TRight> right;

  private bool isReadOnly = false;

  protected Action<IList<TJunction>, TRight> onRemove;
  protected Action<IList<TJunction>, TRight> onAdd;

  public JunctionList(
    IList<TJunction> junction,
    Func<TJunction,TRight> mappingFunction,
    Action<IList<TJunction>, TRight> onAdd,
    Action<IList<TJunction>, TRight> onRemove
  )
  {
    this.junction = junction;
    this.mappingFunction = mappingFunction;
    this.onRemove = onRemove;
    this.onAdd = onAdd;
    right = junction.Select(mappingFunction);
  }

...

  public IEnumerator<TRight> GetEnumerator()
  {
    return right.GetEnumerator();
  }

...

  public void Add(TRight item)
  {
    if (onAdd != null)
      onAdd(junction, item);
  }

  public bool Remove(TRight item)
  {
    if (onRemove != null)
    {  
      onRemove(junction, item);
      return true;
    }
    else
      return false;
  }

...

}

This collection is internally a collection of type TJunction (Order_Details), but is exposed as a collection of type TRight (Products). It uses three delegates:

• mappingFunction: Maps from a TJunction object to the matching TRight object.
• onAdd: Handles the creation of the TJunction object when a TRight object is "added" to the collection.
• onRemove: Handles the deletion of the TJunction object when a TRight object is "removed" from the collection.

We can handle these as required in our entity classes:

• mappingFunction: od => od.Product
• onAdd: (ods, p) => ods.Add(new Order_Detail { OrderID = this.OrderID, Product = p, Quantity=1 })
• onRemove:
  
  

  delegate(IList<Order_Detail> ods, Product p)
                      {
                      var odToRemove = ods.Where(od => od.ProductID == p.ProductID).Single();
                      db.Order_Details.DeleteOnSubmit(odToRemove);
                      ods.Remove(odToRemove);
                      }

Here's the partial declaration we would need to make for the order class:-

public partial class Order
{
private JunctionList<Order_Detail, Product> products = null;
public JunctionList<Order_Detail, Product> Products
{
    get
    {
        if (products == null)
            products = new JunctionList<Order_Detail, Product>(
                this.Order_Details,
                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();
                    db.Order_Details.DeleteOnSubmit(odToRemove);
                    ods.Remove(odToRemove);
                    });
        return products;
    }
}

Now we've got add/remove support in Order.Products. We only need to add the JunctionList collection, and handle the three delegates in our entity class.

There are a couple of little issues left to solve here (the solution will depend heavily on our requirements):

First, we haven't handled any custom Order_Detail data - we've simply set the quantity to 1 on insert. I think this is intuitive behaviour for Order.Products.Add(product). If we want more complex behaviour then you'll need to do a bit more.

Second, we haven't yet handled the case where we add a product that's already in the order. Sometimes we'll want to be able to do this, sometimes we want to limit each pair of order/product to a single entry in the junction table.

Third, you'll notice that I made a call to db.Order_Details.DeleteOnSubmit(odToRemove);. If you're paying attention you'll notice that db (my data context) isn't in scope. You need a reference to your data context in order to tell it to remove the Order_Detail record when SubmitChanges() is called. You also need to ensure that SubmitChanges() is called on the same instance of your data context that you removed the record from. There are various ways you can do this, but the way you choose depends on the rest of your project.

That's it for now. I'm currently investigating ASP.NET AJAX to create some intuitive ways for users to handle many-many relationships at the UI level. I'll post as soon as I have something newsworthy.

< Previous Article: Many to many relationships in LINQ-to-SQL
Next Article: Many to many relationships: Putting it Together (part 3) >