Mapping a derived class to a table in Linq-to-SQL

Question

I have an abstract base class for audit properties. For brevity say it has one property

Public MustInherit Class AbstractAuditableEntity  
  ...  
  Public Property CreatedTime() As DateTimeOffset 
  ...
End Class

And then my auditable domain objects inherit from this class

Public Class Source  
  Inherits AbstractAuditableEntity  
  ...        
  Public Property SourceId() As String  
  ...
End Class

I have the following table DDL to which I want to map my domain object "Source". Essentially the relationship between each (concrete) domain object and table is 1-1, with each table having the required audit column.

CREATE TABLE Source  
(  
   SourceID VARCHAR(10) NOT NULL,   
   CreatedTime   DATETIMEOFFSET(3) NOT NULL,  
   CONSTRAINT PK_Source PRIMARY KEY (SourceID))  
GO

Using an external mapping file my first attempt to map the class to the table would foolishly be:

<?xml version="1.0" encoding="utf-8"?>
<Database Name="" xmlns="http://schemas.microsoft.com/linqtosql/mapping/2007"&gt;
  <Table Name="Source" Member="Sources">
    <Type Name ="Source">
      <Column Name="SourceID" Member="SourceID" IsPrimaryKey="true" CanBeNull="false"/>
      <Column Name="CreatedTime" Member="CreatedTime" />      
    </Type>
  </Table>
</Database>

However this generates the following exception:

The column or association 'CreatedTime' in the mapping had no corresponding member in type 'Source'. Mapping members from above root type is not supported.

Within the context of my persistence layer I am not trying to represent an inheritance hierarchy as such, but within the context of my application I am simply using a base class to provided properties required by all my domain objects. With a lot of fiddling with my mapping file (including mapping the audit columns to the base AbstractAuditableEntity type) and reading around, I am unable to achieve what I perceive as quite a straighforward ORM task.

Any thoughts or suggestions would be most welcome! Thanks

Answer 1

I'm guessing that you are trying to emulate auditing fields like Ruby on Rails updated_on, created_on. If so, here is how I accomplished something similar using this post as a starting point http://weblogs.asp.net/stevesheldon/archive/2008/02/23/a-method-to-handle-audit-fields-using-linq-to-sql.aspx

I implemented an interface in the Models namespace like so:

public interface IAuditable
{
    DateTime CreatedOn { get; set; }
    string CreatedBy { get; set; }
    DateTime? ChangedOn { get; set; }
    string ChangedBy { get; set; }
}

And then extended the partial classes of the data entities that had these fields:

public partial class DataModelIWantToAudit : IAuditable
{
}

And then overrode SubmitChanges on the DataContext to check for the implementation of the interface with the magic of Linq OfType<>:

public override void SubmitChanges(ConflictMode failureMode)
{         
    //Updates
    foreach (var updatedModel in GetChangeSet().Updates.OfType<IAuditable>())
    {
        updatedModel.ChangedOn = DateTime.Now;
        updatedModel.ChangedBy = Membership.GetUser().UserName;
    }

    //Inserts
    foreach (var insertedModel in GetChangeSet().Inserts.OfType<IAuditable>())
    {
        insertedModel.CreatedOn = DateTime.Now;
        insertedModel.CreatedBy = Membership.GetUser().UserName;
    }

    base.SubmitChanges(failureMode);
}

Hope that helps! -Kelly

Answer 2

Kelly showed a great sample of how to do it - but you've basically hit one of the limitations of Linq-to-SQL.

It works great if you database table map more or less 1:1 to your domain objects. But it's weak and causes a lot of extra work once this is no longer the case.

In such a case, as soon as you have domain object inheritance and other things that need to be mapped to database tables, you're best bet would be to check out ADO.NET Entity Framework instead. The EF is specifically designed to handle these things - if you ever think "I need to map my objects ......" then you should think EF! :-)

Granted, the current EF shipping in .NET 3.5 SP1 has its warts and annoyances, but the EF 4 that is part of the .NET 4.0 wave (which should ship before the end of this year 2009), should solve a great many of those warts!

Check out the ADO.NET Entity Framework team blog for some teasers of what EF4 will bring us all!

Marc