2

Every time a row is changed in the database, it needs to write all the column values for that row to an audit table along with an author and date stamp.

I've done this exact thing before, but not with an inheritance relationship involved (the other time it was database-first, so maintaining two separate classes with almost all the same properties wasn't so annoying since the EF designer was doing it for me...)

Given the following table structure in SQL Server:

CREATE TABLE SampleClass
(
    Id int NOT NULL IDENTITY PRIMARY KEY,
    ImagineABunchOfColumnsHere int
)

CREATE TABLE Audit_SampleClass
(
    AuditId bigint NOT NULL IDENTITY PRIMARY KEY,
    ChangedOn datetime NOT NULL,
    ChangedBy nvarchar(200) NOT NULL,
    ChangeType nvarchar(200) NOT NULL,

    Id int NOT NULL,
    ImagineABunchOfColumnsHere int
)

and the following classes in C#:

public class SampleClass
{
    public int Id { get; set; }
    public int ImagineABunchOfColumnsHere { get; set; }
}

public class Audit_SampleClass : SampleClass
{
    public int64 AuditId { get; set; }
    public DateTime ChangedOn { get; set; }
    public string ChangedBy { get; set; }
    public string ChangeType { get; set; }
}

public class SampleContext : DbContext
{
    public virtual DbSet<SampleClass> SampleClasses { get; set; }
    public vidtual DbSet<Audit_SampleClass> Audit_SampleClasses { get; set; }

    public override int SaveChanges()
    {
        // There's code here that does the following (normally in a transaction)
        // 1) Set aside a local copy of this.ChangeTracker.Entries() to use later
        // 2) Call base.SaveChanges()
        // 3) For each change, add a row to the corresponding audit table
        // 4) Call base.SaveChanges() again
    }
}

I can't get Entity Framework to work the way this implies.

All the information and examples of EF inheritance strategies assume you have an abstract class and two concrete classes that inherit from it. No one seems to be in this boat where I want TPT/TPC with a direct inheritance...

The combinations of things I've tried all lead to one or more of the following problems:

  • When inserting 1 new object into the SomeClasses DbSet, instead of 1 new row in the SomeClasses table and 1 new row in the Audit_SomeClasses table with the same ImagineABunchOfColumns here value, I get TWO identical rows in the SomeClasses table and 1 new row in the Audit_SomeClasses table that has NULL ImagineABunchOfColumns. Or,
  • The first SaveChanges() crashes with "Cannot insert explicit value for identity column in table 'SampleClass' when IDENTITY_INSERT is set to OFF". And/or,
  • The second SaveChanges() call crashes with "Cannot insert explicit value for identity column in table 'Audit_SampleClass' when IDENTITY_INSERT is set to OFF." And/or,
  • Trying to select from either DbSet crashes with "All objects in the EntitySet 'SampleContext.SampleClasses' must have unique primary keys. However, an instance of type 'SampleClass' and an instance of type 'Audit_SampleClass' both have the same primary key value, 'EntitySet=SampleClasses;Id=[whatever]'."

Basically I can't seem to find any combination of EntityTypeConfiguration that makes EF treat these two classes totally separately; it will either duplicate things because it's doing a backwards TPH somehow, or it'll treat the inherited Id property on Audit_SampleClass as a primary key even though it isn't on that class (I understand there's a uniqueness issue with some inheritance types but it's not even keys that are conflicting, it's one key and one not-supposed-to-be-a-key), or it'll refuse to treat one or both of the keys as Identity...

I don't expect code generation to be able to handle this scenario, but surely when bringing my own classes and explicitly configuring the ToTable and HasKey and MapInheritedProperties on them there must be a way to get it to treat these two classes separately and not interfere with each other?

EDIT:

As requested in the comments, here are the mapping classes. Various combinations of uncommenting commented lines result in the different outcomes listed in the bullets above.

public class SampleClassMap : EntityTypeConfiguration<SampleClass>
{
    public SampleClassMap()
    {
        this.ToTable("SampleClass");
        this.HasKey(s => s.Id);
        // this.Property(s => s.Id).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
    }
}

public class Audit_SampleClassMap : EntityTypeConfiguration<Audit_SampleClass>
{
    public Audit_SampleClassMap()
    {
        this.ToTable("Audit_SampleClass");
        this.HasKey(a => a.AuditId);
        this.Property(a => a.AuditId).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
        // this.Map(a => a.MapInheritedProperties());
    }
}
Grank
  • 5,242
  • 7
  • 33
  • 36
  • Is this a code-first implementation? If so, you need to define the relationships via fluent mapping by overriding the `OnModelCreating` function of the `DbContext`. [Reference](https://msdn.microsoft.com/en-us/data/jj591620.aspx) – Brett Apr 16 '15 at 16:50
  • Here's another reference on [Table-per-Type (TBT)](http://weblogs.asp.net/manavi/inheritance-mapping-strategies-with-entity-framework-code-first-ctp5-part-2-table-per-type-tpt) implementations in EF. – Brett Apr 16 '15 at 16:56
  • Yes, it's the EntityTypeConfiguration/ that I've been experimenting with to get the various different results mentioned in the bulleted items. They both have ToTable(table name), and their respective HasKey(lambda), and the Audit one has its AuditId Property HasDatabaseGeneratedOption Identity (even though HasKey ought to tell it that, so already a bad sign). Adding Map(m => m.MapInheritedProperties()) to the Audit one moves it from the 1st bullet to the 2nd. Adding HasDataBaseGeneratedOption Identity to the SampleClass table moves it from 2nd to 3rd bullet. – Grank Apr 16 '15 at 20:41
  • Could you show your fluent mapping code in your question? It'd be easier to read and decipher. – Brett Apr 17 '15 at 00:05
  • The difficulty is that I was changing it to get the different results in the bullet points and I wasn't sure which version to post... I'll put something up though, you're right – Grank Apr 20 '15 at 16:32

0 Answers0