c# - Inserting to a link table without fetching first -

i have task table (called task_task) , field table (called core_field). have task_field table links 2 in many 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 insert number of records table. i'm doing 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 following trace:

in case had 5 guids in list<guid> fields. reason see insane value (that's time query took run), because rows in core_field table wide. have few binary fields lot of data. if don't retrieve these fields, instead:

var tmp = _context.core_field     .where(x =>fields.contains(x.fieldid))     .select(x => x.somefield).tolist(); 

the time drops ~1000ms few ms, in cases zero.

as can see inserting records not take long either.

now, not need whole row table. heck, don't need anything table, have guids inserted.

this ef relationship looks like:

i know, how add these records link table efficiently. i, of course, can run executesqlcommand method, , update without using task or field entities, wondering if there more ef - idiomatic way of doing this.

in line of code...

dbtask.core_field.add(field) 

...the dbtask.core_field loaded due lazy loading.

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

_context.configuration.lazyloadingenabled = false; 

but, since "already have guids inserted", can gain more (albeit less) using stub entities, entities having id value. after all, ef needs id values create 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 dbtask.core_field more. means no longer spots duplicates. lazy loading, ef ignore duplicate junction records. without it, duplicate key errors if try insert duplicates. may want check 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 relatively light-weight query.