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

Here is a 'little' extension method I wrote to simplify this problem: public static class EntitySetExtensions { public static void UpdateReferences( this EntitySet refs, Func fkvalue, Func fkmaker, Action fkdelete, IEnumerable 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 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 Bs {get;} } class B { EntitySet As {get;} } class AB { B B {get;} A A {get;} } You now have an object of A, that reference many B's via AB Get all the B from A. Bs via 'fkvalue Get what was added Get what was removed Add all the new ones, and construct AB via 'fkmaker Delete all the removed ones 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.

Here is a 'little' extension method I wrote to simplify this problem: public static class EntitySetExtensions { public static void UpdateReferences( this EntitySet refs, Func fkvalue, Func fkmaker, Action fkdelete, IEnumerable 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 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 Bs {get;} } class B { EntitySet As {get;} } class AB { B B {get;} A A {get;} } You now have an object of A, that reference many B's via AB. Get all the B from A.Bs via 'fkvalue'.

Get what was added. Get what was removed. Add all the new ones, and construct AB via 'fkmaker'.

Delete all the removed ones. 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.

That's great. Could you perhaps provide a description of the algorithm as well? – Will?

Jul 25 '09 at 20:32 Oh, and xml comments are always nice! – Will? Jul 25 '09 at 20:35.

Take two, using expressions: public static class EntitySetExtensions { public static void UpdateReferences( this EntitySet refs, Expression fkexpr, IEnumerable values) where FK : class where FKV : class { Func 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 MakeMaker(Expression 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; return cmaker; } static Action MakeDeleter(Expression 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; return cmaker; } } Now the usage is uber simple!

:) Left entity = ...; IEnumerable 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.

A simple code snippet would suffice. The "DeleteOnNull="True"" must be added to the 'AB' relations or where? I added it on those two relations, but my designer still works, will those disappear if I recreate the table?

(drag/drop/etc). Thanks. – Martín Marconcini Oct 29 '09 at 9:43.

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.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.