How can I set a many-to-many EntityCollection in Entity Framework efficiently?

Question

When Entity Framework generates an ObjectContext for a two database tables (let's say Table1 and Table2) connected with a many-to-many relationship table, it doesn't create an object for the xref table, opting instead for collection properties on either end of the relationship. So on Table1 you have EntityCollection<Table2> Table2s and on Table2 you have EntityCollection<Table2> Table1s. In most cases that's actually pretty great...

However, in this scenario, I have a list of integers that represent the database IDs of the Table2 rows that should be in the Table1.Table2s collection.

I can't see any way to just set that collection using the entity keys, so I'm stuck selecting these into the ObjectContext, which is already a ton of work to do for no reason. I let myself hope that LINQ-to-Entities will intelligently defer the execution and perform it all on the SQL server like I would like (though my Where uses Contains, which may or may not be correctly translated to IN() in SQL). So I can go as far as:

table1instance.Table2s.Clear();
var table2sToInclude = context.Table2s.Where(
  t => 
  listOfTable2DatabaseIds.Contains(t.Id));

But there's no EntityCollection<T>.AddRange(IEnumerable<T>) or anything, nor is there an IEnumerable<T>.ToEntityCollection<T>() extension method of course, so I don't know what to do with these results at this point. All I can do is

foreach (var table2 in table2sToInclude)
{
  table1instance.Table2s.Add(table2);
}

which seems ridiculous and I know will force a lot of unnecessary evaluation.

Is there a "correct", or, perhaps, "less lame" way to do this?

score 2 · Accepted Answer · answered May 30 '11 at 20:27

No EF will not defer any query execution. There is nothing like insert from select. Linq-to-entities is just query language and responsibility of query is to execute. It is strictly separated from persistence functionality offered by EF itself.

If you want to create relations between existing item from table1 and exiting items from table2 you can use code like this:

using (var ctx = new YourContext())
{
    var table1 = new Table1 { Id = 123 };
    ctx.Table1s.Attach(table1);

    foreach (var table2 in table2sToInclude.Select(id => new Table2 { Id = id }))
    {
        ctx.Table2s.Attach(table2);
        order.Table2s.Add(table2);
    }
    ctx.SaveChanges();
}

This code creates relation between Table1's item with id 123 and all Table2's items from table2sToInclude without loading any single record from the database.

What makes adding records one by one "lame"? Do you understand what is benefit of AddRange? AddRange in typical collection extends capacity of internal array and just copy items to extended array. EntityCollection is not typical array and it must process each added entity. So even if there will be some AddRange it will internally iterate items and process them on by one.

It wasn't about the one by one versus AddRange, except the naive hope that calling AddRange in that situation (if it existed) wouldn't cause SELECT statements that I don't need any information from (since I already have the EntityKey Ids) The approach of instantiating new Table2 objects and calling Attach should work. Thank you — Grank, Feb 15 '12 at 18:08

How can I set a many-to-many EntityCollection in Entity Framework efficiently?

1 Answers1