Insertion into the table of links without preloading

Question

I have a task table (Task_Task) and a field table (called Core_Field). I also have a Task_Field table that links the two in many ways:

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 several records into this table. I do 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 pointers in the List<Guid> fields . The reason we see this insane value (the time taken to complete the query) is because the rows in the Core_Field table are very wide. They have several binary fields with a lot of data. If I do not get these fields, but do something like this:

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

The time falls from ~ 1000 ms to several ms, in most cases zero.

As you can see, inserting records does not take much time.

Now I do not need the entire row from this table. Hell, I don’t need anything from this table, I already have all the guides that need to be inserted.

This EF ratio is as follows:

I would like to know how to effectively add these entries to the link table. Of course, I can always run ExecuteSqlCommand and update without using task objects or fields, but I was wondering if there is more EF - an idiomatic way to do this.

Answer 1

In this line of code ...

 dbTask.Core_Field.Add(field) 

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

You will see a significant increase in performance if you disable lazy loading:

 _context.Configuration.LazyLoadingEnabled = false; 

But, since you “already have all the guides you need to insert,” you can get even more (albeit much less) using stub objects, entities that have only an Id value. In the end, only Id values ​​are needed to create EF 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: due to disabled lazy loading, EF no longer tracks dbTask.Core_Field . This means that it no longer duplicates places. On lazy loading, EF simply ignores duplicate connection records. Without this, you will get duplicates of key errors if you try to insert duplicates. Therefore, you can check this in advance:

 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 easy request.