0

Having the 2 entities: A *<-->* B many-to-many relationship and corresponding 3 tables: A, B, AB.

Trying the following code: 

 var tempA = this.dbContext.A.
                  .Where(a => a.UID == 1)
                  .FirstOrDefault();

 // {check for null here}

 tempA.B.Clear();

 this.dbContext.SaveChanges();

seems to generate multiple 'Delete' sql calls to database, for-each b in tempA.B collection (somewhat strange, unless I miss something).

So, if I have AB table with these 2 records = { (1,2) , (1,3) } , the above code produces two 'Delete' sql calls (one for b=2 & another for b=3), something like this:

 1. DELETE FROM AB WHERE (A_UID = 1) AND (B_UID = 2)
 and 
 2. DELETE FROM AB WHERE (A_UID = 1) AND (B_UID = 3)

But I want some EF code which could produce something simple as the below?

 DELETE FROM AB WHERE (A_UID = 1)

EDIT: I don't understand why EF is parsing all the inner references and generates one delete for-each of them, instead one delete for all of them.

Learner
  • 3,297
  • 4
  • 37
  • 62
  • 1
    you could try looking at entity framework extended for doing batch updates/deletes: https://github.com/loresoft/EntityFramework.Extended – Thewads Aug 26 '13 at 15:10
  • @Thewads: this looks good; it's definitely something to have in mind; – Learner Aug 29 '13 at 09:00

2 Answers2

2
dbContext.Database.ExecuteSqlCommand("DELETE FROM AB WHERE (A_UID = 1)");

is the only way to achieve this with a single SQL command. EF does not track if you have loaded/attached all or only a part of the related entities. You could call Clear in a situation like this...

var tempA = new A { UID = 1, B = new List<B>() }
tempA.B.Add(new B { UID = 2 });

using (var dbContext = new MyContext())
{
    dbContext.A.Attach(tempA);
    tempA.B.Clear();
    dbContext.SaveChanges();
}

...in which case deleting all entries from the join table for A.UID = 1 would be wrong because by clearing the collection you removed only the entity with B.UID = 2 but not with B.UID = 3.

Slauma
  • 175,098
  • 59
  • 401
  • 420
  • OK, I think I understand now that Clear() affects only the attached entities which EF cannot predict if they are all or not... too bad there is no other simple way to achieve what I want... I'd probably go for calling a stored procs then. – Learner Aug 26 '13 at 13:45
  • I noticed that Execution of a command or stored proc is done in a separate transaction than the one where SaveContext is called. Any thoughts on that? Thanks! – Learner Aug 28 '13 at 19:35
  • @Cristi: If you need both in a single transaction wrap them into TransactionScope, like shown here: http://stackoverflow.com/a/7525625/270591 – Slauma Aug 28 '13 at 19:45
  • I really appreciate your help. Yes, but now I guess it goes to a second part regarding DTC. Could you have a look at this question of mine: http://stackoverflow.com/questions/18489291/ef-and-transactionscope-for-both-sql-server-and-oracle-without-escalating-to-dtc – Learner Aug 29 '13 at 07:36
  • @Cristi: Sorry, I don't know the answer to your new question. – Slauma Aug 29 '13 at 09:08
  • No problem @Slauma . Thanks a lot... Is somewhat frustrating that I cannot find something 'good' to shoot both SQL Server and Oracle in one app... uh :( – Learner Aug 29 '13 at 09:12
0

Why not do something like:

var temp = this.dbContext.AB.Where(ab => ab.A.UID == 1 && ab.B.UID == 2);
temp.Clear();

I didn't tested it, but maybee this will give better idea's?

Jeroen van Langen
  • 21,446
  • 3
  • 42
  • 57