Correct way to remove a many-to-many relationship via linq to sql?

Question

Let's say we have two tables with a many-to-many relationship:

public class Left{ /**/ }

public class Right{ /**/ }

public class LeftRight{ /**/ }

is the following sufficient to unhook these records (ignore the possibility of more than one relationship or no relationship defined)?

public void Unhook(Left left, Right right){
  var relation = from x in Left.LeftRights where x.Right == right;
  left.LeftRrights.Remove(relation.First());
  Db.SubmitChanges();
}

Or do I have to do it on both parts? What's required here?

Answer 1

Here is a 'little' extension method I wrote to simplify this problem:

  public static class EntitySetExtensions
  {
    public static void UpdateReferences<FK, FKV>(
        this EntitySet<FK> refs,
        Func<FK, FKV> fkvalue,
        Func<FKV, FK> fkmaker,
        Action<FK> fkdelete,
        IEnumerable<FKV> values)
      where FK : class
      where FKV : class
    {
      var fks = refs.Select(fkvalue).ToList();
      var added = values.Except(fks);
      var removed = fks.Except(values);

      foreach (var add in added)
      {
        refs.Add(fkmaker(add));
      }

      foreach (var r in removed)
      {
        var res = refs.Single(x => fkvalue(x) == r);
        refs.Remove(res);
        fkdelete(res);
      }
    }
  }

It could probably be improved, but it has served me well :)

Example:

Left entity = ...;
IEnumerable<Right> rights = ...;

entity.LeftRights.UpdateReferences(
 x => x.Right, // gets the value
 x => new LeftRight { Right = x }, // make reference
 x => { x.Right = null; }, // clear references
 rights);

Algorithm description:

Suppose A and B is many-to-many relationship, where AB would be the intermediary table.

This will give you:

class A { EntitySet<B> Bs {get;} }
class B { EntitySet<A> As {get;} }
class AB { B B {get;} A A {get;} }

You now have an object of A, that reference many B's via AB.

1. Get all the B from A.Bs via 'fkvalue'.
2. Get what was added.
3. Get what was removed.
4. Add all the new ones, and construct AB via 'fkmaker'.
5. Delete all the removed ones.
6. Optionally, remove other referenced objects via 'fkdelete'.

I would like to improve this by using Expression instead, so I could 'template' the method better, but it would work the same.

Answer 2

Take two, using expressions:

public static class EntitySetExtensions
{
  public static void UpdateReferences<FK, FKV>(
      this EntitySet<FK> refs,
      Expression<Func<FK, FKV>> fkexpr,
      IEnumerable<FKV> values)
    where FK : class
    where FKV : class
  {
    Func<FK, FKV> fkvalue = fkexpr.Compile();
    var fkmaker = MakeMaker(fkexpr);
    var fkdelete = MakeDeleter(fkexpr);

    var fks = refs.Select(fkvalue).ToList();
    var added = values.Except(fks);
    var removed = fks.Except(values);

    foreach (var add in added)
    {
      refs.Add(fkmaker(add));
    }

    foreach (var r in removed)
    {
      var res = refs.Single(x => fkvalue(x) == r);
      refs.Remove(res);
      fkdelete(res);
    }
  }

  static Func<FKV, FK> MakeMaker<FKV, FK>(Expression<Func<FK, FKV>> fkexpr)
  {
    var me = fkexpr.Body as MemberExpression;

    var par = Expression.Parameter(typeof(FKV), "fkv");
    var maker = Expression.Lambda(
        Expression.MemberInit(Expression.New(typeof(FK)), 
          Expression.Bind(me.Member, par)), par);

    var cmaker = maker.Compile() as Func<FKV, FK>;
    return cmaker;
  }

  static Action<FK> MakeDeleter<FK, FKV>(Expression<Func<FK, FKV>> fkexpr)
  {
    var me = fkexpr.Body as MemberExpression;
    var pi = me.Member as PropertyInfo;

    var par = Expression.Parameter(typeof(FK), "fk");
    var maker = Expression.Lambda(
        Expression.Call(par, pi.GetSetMethod(), 
          Expression.Convert(Expression.Constant(null), typeof(FKV))), par);

    var cmaker = maker.Compile() as Action<FK>;
    return cmaker;
  }
}

Now the usage is uber simple! :)

Left entity = ...;
IEnumerable<Right> rights = ...;

entity.LeftRights.UpdateReferences(x => x.Right, rights);

The first expression is now used to establish the 'relationship'. From there I can infer the 2 previously required delegates. Now no more :)

Important:

To get this to work properly in Linq2Sql, you need to mark the associations from intermediary table with 'DeleteOnNull="true"' in the dbml file. This will break the designer, but still works correctly with SqlMetal.

To unbreak the designer, you need to remove those additional attributes.

Answer 3

Personally, I'd replace

left.LeftRrights.Remove(relation.First());

with

Db.LeftRights.DeleteAllOnSubmit(relation)

because it seems more obvious what's going to happen. If you are wondering what the behaviour of ".Remove" is now, you'll be wondering anew when you look at this code in 6 months time.