Correct way to remove many-to-many relationships via linq in sql?

Question

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 remove these records (ignore the possibility of more than one relationship or no relationship is 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 need to do this on both parts? What is needed here?

Answer 1

Here is a small 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 can probably be improved, but it recommended 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);

Description of the algorithm:

Suppose A and B are many-to-many relationships, where AB will be an intermediate 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;} }

A, B AB.

• B A.Bs 'fkvalue'.
• , .
• , .
• AB "fkmaker".
• .
• 'fkdelete'.

, Expression , "" , .

Answer 2

, :

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;
  }
}

uber simple!:)

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

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

"". 2 . :)

:

Linq2Sql, 'DeleteOnNull = "true" dbml. , SqlMetal.

, .

Answer 3

Personally, I replaced

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

from

Db.LeftRights.DeleteAllOnSubmit(relation)

because it seems more obvious what will happen. If you are wondering what the ".Remove" behavior is, you will be wondering when you look at this code after 6 months.