how to insert records to unrelated tables using EF

Question

I have a Comment table which can be linked to many different entities that have comments, but for reasons, I have not linked those tables. Instead Comment contains TableReferenceId and EntryReferenceId. TableReferenceId is just an int that we can check in the app layer as to which entity/table that comment refers to, and EntryReferenceId is an int that refers to a particular entry in said entity/table to which the comment belongs.

Querying such comments by table and entry reference would be fine, but when inserting bulk data, I am drawing a blank. For example if I have Vehicle entity and a Vehicle can have many comments, when inserting the data, how would I link them since I don't have a VehicleId yet? Is this doable or is it better to just go many-to-many route for each of the tables that link to comments?

Answer 1

If you can avoid this situation, then you should try to, or you should try to avoid supporting a bulk insert. If you must do this though, then either of the following patterns may work for you.

1. Perform the Bulk Insert in 2 stages, before the normal import, maintain a map or dictionary of records and the comments that they are linked to, then after the first call to SaveChanges() the IDs will be available to insert.

2. You could store the mapped comments inside an unbound collection on the entity, after SaveChanges() if there are any entries in this collection, they should be inserted using the new record's Id.

Lets look at the first option:

var mappedComments = new Dictionary<Vehicle,Comment[]>();
// bulk processing, however you choose to do it
// importantly for each item, capture the record reference and the comments
foreach(var item in source)
{
    Vehicle newItem;
    ... construct/parse the new Entity object
    List<Comment> newComments = new List<Comment>();
    ... parse the comments records
    // store the map
    mappedComments.Add(newItem, newComments.ToArray());

    // Add the entity to the context?
    db.AddToVehicles(newItem);
}

db.SaveChanges();

foreach(var mapEntry in mappedComments)
{
    var newVehicle = mapEntry.Key;
    // replace this with your actual logic of course...
    int vehicleTableReferenceId = db.TableReferences.Single(x => x.TableName == nameof(Vehicle));
    foreach(var comment in mappEntry.Value)
    {
        comment.TableReferenceId = vehicleTableReferenceId;
        comment.EntityReferenceId = newVehicle.Id; // the Id that is now populated
        db.AddToComments(comment);
    }
}

db.SaveChanges();

If you have a lot Entity types that exhibit this linking behaviour, then you could build this functionality into the Entities themselves, by embedding the mapped comments within the entity itself.

1. Define an Interface that describes an object that has a weak reference to these Comments

   public interface ICommentsToInsert
   {
       // Only necessary if your convention is NOT to use a common name for the PK
       int Id { get; }
       ICollection<Comment> CommentsToInsert { get;set;}
   }
   

2. Implement this interface and add an unmapped collection property to the entities to store the Comment Entries to insert against each record.

   partial class Vehicle : ICommentsToInsert
   {
       [NotMapped]
       int ICommentsToInsert.Id { get => Vehicle_Id; }
       [NotMapped]
       public ICollection<Comment> CommentsToInsert { get;set; } = new HashSet<Comment>();
   }
   

3. In your bulk logic, add the Comment records into the Vehicle.CommentsToInsert collection, I'll leave that to you...

4. Override SaveChanges() to detect entities that have comments and re-process them after the save operation.

   In this example I am storing the EntityState for all modified entries before the save, this is overkill for this particular example, but you only lose this state information during the save, keeping a record of it becomes useful for a whole range of other applications for post-processing logic.

   public override int SaveChanges()
   {
       var beforeStates = BeforeSaveChanges();
       int result = base.SaveChanges();
       if (AfterSaveChanges(beforeStates);
           result += base.SaveChanges();
       return results;
   }
   
   private Dictionary<DbEntityEntry, EntityState> BeforeSaveChanges()
   {
       var beforeSaveChanges = new Dictionary<DbEntityEntry, EntityState>();
       foreach( var entry in this.ChangeTracker.Entries())
       {
           //skip unchanged entries!
           if (entry.State == EntityState.Unchanged)
               continue;
           // Today, only cache the ICommentsToInsert records...
           if (entry.Entity is ICommentsToInsert)
               beforeSaveChanges.Add(entry, entry.State);
       }
       return beforeSaveChanges;
   }
   
   private bool AfterSaveChanges(Dictionary<DbEntityEntry, EntityState> statesBeforeSaveChanges)
   {
       bool moreChanges = false;
       foreach (var entry in statesBeforeChanges)
       {
           if (entry.Key.Entity is ICommentsToInsert hasComments)
           {
               if(hasComments.CommentsToInsert.Any())
               {
                   moreChanges = true;
                   // Get the Id to the TableReference, based on the name of the Entity type
                   // you would normally cache this type of lookup, rather than hitting the DB every time
                   int tableReferenceId = db.TableReferences.Single(x =
        > x.TableName == entry.Key.Entity.GetType().Name);
                   foreach (var comment in hasComments.CommentsToInsert)
                   {
                       comment.TableReferenceId = tableReferenceId;
                       comment.EntityReferenceId = hasComments.Id;
                       db.AddToComments(comment);
                   }
               }
           }
       }
       return moreChanges;
   }
   

You can further evolve this by implementing DbTransaction scopes to rollback the whole lot if things fail, this code itself is para-phrased from my common routines that I use in production code, so whilst it may not work as is, the concept has served me well in many projects.