Inserting to a link table without fetching first

Question

I have a Task table (called Task_Task) and a Field table (called Core_Field). I also have Task_Field table that links the two in many to many relationship:

    CREATE TABLE [dbo].[Task_Field](
        [TaskId] [uniqueidentifier] NOT NULL,
        [FieldId] [uniqueidentifier] NOT NULL,
     CONSTRAINT [PK_Task_Field] PRIMARY KEY NONCLUSTERED 
    (
        [TaskId] ASC,
        [FieldId] ASC
    ))

I need to insert a number of records into this table. I'm doing it like this:

dbTask.Core_Field.Clear();
List<Core_Field> dbFields = _context.Core_Field
   .Where(x => fields.Contains(x.FieldId)).ToList();                
foreach (var field in dbFields)
{
    dbTask.Core_Field.Add(field);
}

This leads to the following trace:

In this case we had 5 guids in List<Guid> fields. The reason we see this insane value (that's the time the query took to run), is because the rows in the Core_Field table are very wide. They have a few binary fields with a lot of data. If I don't retrieve these fields, but do something like this instead:

var tmp = _context.Core_Field
    .Where(x =>fields.Contains(x.FieldId))
    .Select(x => x.SomeField).ToList();

The time drops from ~1000ms to just a few ms, in most cases zero.

As you can see inserting the records does not take long either.

Now, I do not need the whole row from that table. Heck, I don't need anything from that table, I already have all the guids to be inserted.

This is what EF relationship looks like:

I would like to know, how to add these records to the link table efficiently. I, of course, can always run ExecuteSqlCommand method, and do update without using task or field entities, but I was wondering if there is more EF - idiomatic way of doing this.

Answer 1

In this line of code...

dbTask.Core_Field.Add(field)

...the dbTask.Core_Field is loaded due to lazy loading.

You'll see a major performance gain if you disable lazy loading:

_context.Configuration.LazyLoadingEnabled = false;

But, since you "already have all the guids to be inserted", you can gain even more (albeit much less) by using stub entities, entities only having an Id value. After all, EF only needs the Id values to create the associations:

List<Core_Field> dbFields = fields.Select(f => new Core_Field { FieldId = f }).ToList();

 foreach (var field in dbFields)
{
    _context.Core_Fields.Attach(field);
    dbTask.Core_Field.Add(field);
}

One caveat: because of disabled lazy loading, EF isn't tracking the dbTask.Core_Field any more. That means that it no longer spots duplicates. With lazy loading, EF would just ignore duplicate junction records. Without it, you will get duplicate key errors if you try to insert duplicates. So you may want to check for that beforehand:

fields = _context.Code_Tasks
                 .Where(t => t.TaskId == id)
                 .SelectMany(t => t.Core_Field)
                 .Where(c => !fields.Contains(c.FieldId))
                 .Select(c => c.FieldId).ToList();

This is a relatively light-weight query.