Entity Framework - Non Key Relationships

Question

Problem

I have a situation whereby I need to use Entity Framework 6, Code First, with a legacy database structure which cannot be changed. The database has a very generic table which stores text based data alongside some non key data which can be used to relate the record back to another table.

To illustrate:

Assume the Notes table has a model as follows:

[Table("Notes")]
public class Notes
{
    [Key]
    public int RecordId { get; set; }

    [Required]
    public string RelatedTableName { get; set; }

    [Required]
    public int RelatedTableRecordId { get; set; }

    [Required]
    public string NotesText { get; set; }
}

I then have another model which could look like so:

[Table("Drivers")]
public class Drivers
{
    [Key]
    public int RecordId { get; set; }

    [Required]
    public string DriverName { get; set; }

    public ICollection<Notes> DriverNotes { get; private set; }
}

There is no foreign key which links the tables. The Drivers table is linked to the Notes table by way of the RelatedTableName and RelatedTableRecordId fields.

I do not have a problem reading data from the database and hydrating the models using entity framework.

The problem I have is that I want to be able to save a new Driver and its newly created Notes in one transaction and have the RelatedTableRecordId field set to the primary key of the Driver.

If a foreign key existed entity framework would know to back fill the property but in this case it doesn't know about the relationship.

Key Points

1. Database Structure must not change.
2. Must use Entity Framework 6 Code First
3. Must be able to use an Execution Strategy.
4. Require a relationship between non key fields.
5. Need to be able to persist all data in a single transaction.

What I've Tried

I had a similar issue with Audit type data and solved it by doing something similar to the following (note that this is very pseudo here):

    public override int SaveChanges()
    {
        int changes = 0;

        //Disable the current execution strategy as the default ones do not support user instantiated transactions.
        this.ContextConfiguration.SuspendExecutionStrategy();

        try
        {
            //Wrap a whole transaction inside an execution strategy so that auditing can be combined with regular saving of changes.
            this.ExecutionStrategy.Execute(
                () =>
                {
                    using (var transaction = this.Database.BeginTransaction())
                    {
                        //Reset the change count so that it doesn't increase each time the transaction fails.
                        changes = 0;

                        //Remove any audit records created by previous failed transactions.
                        this.AuditTableChanges.Local.Clear();

                        //Evaluate the change tracker to identify entities which will potentially require an audit trail.
                        var insertedEntities = this.ChangeTracker.Entries().Where(entryEntity => entryEntity.State == EntityState.Added).ToList();

                        //Save all changes to get identities.
                        changes = base.SaveChanges();

                        //Create the audit trail for inserted entities. This step must occur after the initial call to SaveChanges() so that the identities are set.
                        foreach (DbEntityEntry entryEntity in insertedEntities)
                        {
                            //For each inserted record, get the audit record entries and add them
                            foreach (AuditTableChange auditTableChange in GetAuditRecords(entryEntity, AuditTableChangeType.Insert).Result)
                                this.AuditTableChanges.Add(auditTableChange);
                        }

                        //Save the audit trail for inserted entities.
                        changes += base.SaveChanges();

                        //Commit all changes to the database
                        transaction.Commit();
                    }
                });
        }
        finally
        {
            //Re-enable the execution strategy so that other calls can benefit from the retry policy.
            this.ContextConfiguration.UnSuspendExecutionStrategy();
        }

        return changes;
    }

This worked fine for the Audit data as the implementation was hidden away in the framework. I do not want my development team to have to do all of the above each time they persist records.

In its simplistic form this is as much as I'd want people to be doing:

    public void CreateDriver()
    {
        using (MyContext context = new MyContext())
        {
            Drivers driver = new Drivers();
            driver.DriverName = "Joe Bloggs";

            Notes driverNote = new Notes();
            driverNote.RelatedTableName = "Drivers";
            driverNote.NotesText = "Some very long text";

            driver.DriverNotes.Add(driverNote);

            context.Drivers.Add(driver);

            context.SaveChanges();
        }
    }

In a way I want a foreign key which exists in code but not in the database so that entity framework knows to fill in the RelatedTableRecordId field. I've read some articles on hacking the EDMX but this project is purely Code First only.

There are older questions on stack overflow which are similar but relate to older versions of entity framework and don't help much or have as much detail as the above.

I'm hoping that someone may have experienced a similar problem and has an answer which may involve perhaps some custom mapping/metadata or some overrides to entity framework logic.

Any help would be appreciated.

Thanks,

Greg