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.
For INNER joins, no, the order doesn't matter. The queries will return same results, as long as you change your selects from SELECT * to SELECT a. *, b.
Yes, you can! The longer answer is yes, there are a few ways to combine two tables without a common column, including CROSS JOIN (Cartesian product) and UNION. The latter is technically not a join but can be handy for merging tables in SQL.
Omit the ON clause from the JOIN statement You can just simplly JOIN two tables like this: SELECT * FROM table_a JOIN table_b; It will match each row from table_a to every row in table_b . It's similar to run SELECT * FROM multiple tables statement below.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With