EF Code First Multilevel Inheritance Issue

Question

I have an inheritance hierarchy setup that I am mapping to a DB via TPT in Code first. For the most part the hierarchy is one level deep, but sometimes it it two. My base class looks like this:

public class AuditEvent
{
    public int AuditEventID;

    //other stuff
};

Then I have a bunch of other classes that look like this (with different names and properties):

public class PageRequest : AuditEvent
{
    /// <summary>
    /// Page Request Id (Primary Key)
    /// </summary>        
    public Int64 PageRequestID { get; set; }

    /// <summary>
    /// Screen (page) being requested
    /// </summary>        
    public string Screen { get; set; }

    /// <summary>
    /// Http Method
    /// </summary>        
    public string HttpMethod { get; set; }

    /// <summary>
    /// Confirmation Logs linked to this page request
    /// </summary>
    public virtual List<ConfirmationLog> ConfirmationLogs { get; set; }
}

This specific class (PageRequest) is a parent to one other class called ConfirmationLog, which looks like this:

/// <summary>
/// Object used to log confirmations to the auditing database
/// </summary>
public class ConfirmationLog : PageRequest
{
    /// <summary>
    /// Confirmation ID
    /// </summary>        
    public long ConfirmationID { get; set; }

    /// <summary>
    /// Confirmation number
    /// </summary>
    public string ConfirmationNum { get; set; }

    /// <summary>
    /// Web action ID (automated alert or transaciton confirmation number)
    /// </summary>
    public int WebActionID { get; set; }
}

I'm configuring the mappings using configuration classes and the fluent API, like so:

/// <summary>
/// Configuration class for PageRequest
/// </summary>
public class PageRequestConfiguration : EntityTypeConfiguration<PageRequest>
{
    /// <summary>
    /// Default constructor
    /// </summary>
    public PageRequestConfiguration()
    {
        //Table
        ToTable("PageRequests");

        //primary key
        HasKey(a => a.PageRequestID);

        //Properties
        Property(a => a.PageRequestID).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
        Property(a => a.Screen).IsRequired().HasMaxLength(100);
        Property(a => a.HttpMethod).IsRequired().HasMaxLength(10);
    }
}

/// <summary>
/// Confirmation Log configuration class.  Configures the confirmation log class for the db model
/// </summary>
public class ConfirmationLogConfiguration : EntityTypeConfiguration<ConfirmationLog>
{
    /// <summary>
    /// Default constructor
    /// </summary>
    public ConfirmationLogConfiguration()
    {
        //Map to Table
        ToTable("ConfirmationLogs");

        //Primary Key
        HasKey(a => a.ConfirmationID);

        //required fields
        Property(a => a.ConfirmationID).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
        Property(a => a.PageRequestID).IsRequired();
        Property(a => a.ConfirmationNum).IsRequired().HasMaxLength(12);
        Property(a => a.WebActionID).IsRequired();
    }
}

I then create a rather large LINQ query based on this hierarchy. I'll spare that query because it's composed in about 10 steps, and I don't think that's the source of my problem. The problem is, when I run the query, the SQL generated for some reason thinks that the column AuditEventID (the primary key for the base class), exists on the ConfirmationLogs table (the grandchild table). The ConfirmationLogs table has a foreign key to it's parent table (PageRequests), which then has the foreign key to it's parent table (AuditEvents).

My question is, did I set this hierarchy up wrong? Does the "grandchild" table need the foreign key to both it's parent and grandparent for this to function? (if it does I find that unfortunate).

I'm positive that the inheritance relationship is throwing things off because if I don't make ConfirmationLogs a child of PageRequests and configure the relationship to PageRequests with HasRequired()/WithMany(), things work fine.

Any help would be appreciated.

Update

So, after further investigation I think there is a general problem with the way I'm trying to use inheritance. I should note that I'm trying to map code first to an existing database. In the database, I have my AuditEvent table, and a bunch of "child" tables like PageRequest. Page request has it's own primary key called PageRequestID, as well as a foreign key called AuditEventID. The other child tables are setup the same way. In my Configuration class for PageRequest (listed above), I'm trying to map this by using the HasKey function to say that the PageRequestID is the primary key, and assuming that EF will know about the foreign key AuditEventID by convention and inheritance. I should also note that I can write to the DB using the model just fine. If I want to write a PageRequest, I create PageRequest object, populate all the required fields as defined by both the PageRequest and AuditEvent base class, and save through the context. EF creates the AuditEvent record, and the pageRequest record with the FK back to AuditEvent.

What makes me think I'm not using inheritance right is that I allowed EF to create my database for me, using the model and mapping I've created. For the PageRequest table (and all other child tables), EF actually created a primary key called AuditEventID (even though my configuration is telling it to do otherwise). This key is also labeled as a foreign key, and the column that I want to create as a primary key (PageRequestID in this example) is just configured as being required (non-nullable). So it appears that EF taking the primary key from my BASE class and using that as a primary key AND foreign key in my child classes, almost like the concept of the AuditEventID is spread between the parent and child tables. Is there a way to change this behavior?

Answer 1

You are saying this didn't work, and it still expected an AuditRequestID in the table that had the ConfirmationLog object? I'm looking at the reference: Specifying Not to Map a CLR Property to a Column in the Database in http://msdn.microsoft.com/en-us/data/jj591617#1.6

public ConfirmationLogConfiguration()
{
    //Map to Table
    ToTable("ConfirmationLogs");

    //Primary Key
    HasKey(a => a.ConfirmationID);

    //required fields
    Property(a => a.ConfirmationID).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
    Property(a => a.PageRequestID).IsRequired();
    Property(a => a.ConfirmationNum).IsRequired().HasMaxLength(12);
    Property(a => a.WebActionID).IsRequired();

    Ignore(a => a.AuditEventID);
}

Good luck.