none
Dealing with Inheritance in SQL with LINQ RRS feed

  • Question

  • I have tables that represents inheritance in a database. In the example below, Person table holds information that both Staff and Guest share. A Staff is represented by a record in Person table and also a record in Staff table. The primary key "Id" of the record in Staff table is a reference to the "Id" of the record in Person table. "TypeCode" in Person table can be either "S" for Staff or "G" for Guest.

    Person

    Column Type
    Id (PK) int
    FirstName nvarchar(30)
    LastName nvarchar(30)
    DateOfBirth datetime
    TypeCode varchar(2)

    Staff

    Column Type
    Id (PK, FK) int
    Joined datetime

    Guest

    Column Type
    Id (PK, FK) int
    Visited datetime

    What I want to do is to use Linq to SQL and do things like the following:

        XDataContext db = new XDataContext();
    
        Guest firstGuest =
        (
            from p in db.Persons
            where p.TypeCode == TypeCode.Guest
            select p
        ).Cast<Guest>().First();
    
        DateTime dateFirstGuestVisited = firstGuest.Visited;
    
        db.Persons.InsertOnSubmit
        (
            new Staff
            {
                FirstName = "Shingo"
                , LastName = "Tamura"
                , DateOfBirth = DateTime.Now
                , Joined = DateTime.Now
            }
        );
    
    

    Just to let you know that I have already seen examples where something like this is done but it uses only a single table that has extra columns to accommodate properties of inherited classes (I see a lot of examples of this on the net), in this case you will have columns are meaningless and redundant to certain types of records.

    I'm sure some people have come across a similar challenge and I'm hoping someone has already done something like this.

    Thursday, July 3, 2008 3:06 AM

Answers

  • I wrote a blog post a while ago exploring a design like the one you have -- you may find it useful (see link below).

     

    First, however, a couple of caveats:

     

    1. Since LINQ to SQL does not directly support the kind of model you wish to develop, my sample app has to step outside the LINQ to SQL box (specifically, I had to write some T-SQL code as well).

     

    2. The app I posted is a sample, and comes with the standard disclaimer - it is meant to give you an idea of the kind of things you can explore, but is not something I actively support.

     

    Regardless, I hope you will find it useful: http://blogs.msdn.com/sbajaj/archive/2008/04/02/tpt-with-linq-to-sql.aspx

     

    Thanks,

     

    --Samir

     

     

    Friday, July 11, 2008 8:24 PM
  • Thanks very much for your reply, Samir. Your solution is quite nice and it works well.

    I've created a view called PersonDetail which combines Person joined with Staff and Person joined with Guest. TypeCode is attached to each record in the view and is no longer a column in the Person table. 'S' for Staff and 'G' for Guest. The column Joined on the Staff table and the column Visited on the Guest table are both of datetime so I've renamed them as DateStamp so that I could do a union all on them.

    CREATE VIEW [dbo].[PersonDetail]
    AS
    SELECT
        p.Id
        , p.FirstName
        , p.LastName
        , p.DateOfBirth
        , s.Joined AS DateStamp
        , 'S' AS TypeCode
    FROM
        dbo.Person AS p
    INNER JOIN
        dbo.Staff AS s ON s.Id = p.Id
    
    UNION ALL
    
    SELECT
        p.Id
        , p.FirstName
        , p.LastName
        , p.DateOfBirth
        , g.Visited AS DateStamp
        , 'G' AS TypeCode
    FROM
        dbo.Person AS p
    INNER JOIN
        dbo.Guest AS g ON g.Id = p.Id
    

    The following is my implementation of a single-table inheritance.

    [Table(Name = "dbo.PersonDetail")]
    [InheritanceMapping(Code = "S", Type = typeof(Staff), IsDefault = true)]
    [InheritanceMapping(Code = "G", Type = typeof(Guest))]
    public partial class Person
    {
        [Column(IsPrimaryKey=true)]
        public int Id { get; set; }
    
        [Column]
        public string FirstName { get; set; }
    
        [Column]
        public string LastName { get; set; }
    
        [Column]
        public DateTime? DateOfBirth { get; set; }
    
        [Column(IsDiscriminator = true)]
        public char TypeCode { get; set; }
    }
    
    public partial class Staff : Person
    {
        [Column(Name = "DateStamp")]
        public DateTime Joined { get; set; }
    }
    
    public partial class Guest : Person
    {
        [Column(Name="DateStamp")]
        public DateTime Visited { get; set; }
    }
    

    And I've written the following very simple DataContext class.

    public partial class PersonDataContext : DataContext
    {
        private static System.Data.Linq.Mapping.MappingSource mappingSource = new AttributeMappingSource();
        public PersonDataContext(string connection) : base(connection, mappingSource){}
        public System.Data.Linq.Table<Person> Persons { get { return this.GetTable<Person>(); } }
    }
    

    I've written a INSTEAD OF trigger for each of INSERT, UPDATE and DELETE operations so that updates will work correctly across tables.

    If you have a lot of inheritances in your database schema it might be a little tedious to maintain the triggers but I find this a pretty good solution/compromise.

    Thanks,

    Shingo

    • Marked as answer by Shingo Tamura Sunday, January 11, 2009 12:08 PM
    Wednesday, July 23, 2008 11:59 AM

All replies

  • LINQ to SQL only supports single table inheritance and not the class table inheritance you are looking for.

     

    [)amien

     

     

     

    Friday, July 11, 2008 7:51 PM
    Moderator
  • I wrote a blog post a while ago exploring a design like the one you have -- you may find it useful (see link below).

     

    First, however, a couple of caveats:

     

    1. Since LINQ to SQL does not directly support the kind of model you wish to develop, my sample app has to step outside the LINQ to SQL box (specifically, I had to write some T-SQL code as well).

     

    2. The app I posted is a sample, and comes with the standard disclaimer - it is meant to give you an idea of the kind of things you can explore, but is not something I actively support.

     

    Regardless, I hope you will find it useful: http://blogs.msdn.com/sbajaj/archive/2008/04/02/tpt-with-linq-to-sql.aspx

     

    Thanks,

     

    --Samir

     

     

    Friday, July 11, 2008 8:24 PM
  • Thanks very much for your reply, Samir. Your solution is quite nice and it works well.

    I've created a view called PersonDetail which combines Person joined with Staff and Person joined with Guest. TypeCode is attached to each record in the view and is no longer a column in the Person table. 'S' for Staff and 'G' for Guest. The column Joined on the Staff table and the column Visited on the Guest table are both of datetime so I've renamed them as DateStamp so that I could do a union all on them.

    CREATE VIEW [dbo].[PersonDetail]
    AS
    SELECT
        p.Id
        , p.FirstName
        , p.LastName
        , p.DateOfBirth
        , s.Joined AS DateStamp
        , 'S' AS TypeCode
    FROM
        dbo.Person AS p
    INNER JOIN
        dbo.Staff AS s ON s.Id = p.Id
    
    UNION ALL
    
    SELECT
        p.Id
        , p.FirstName
        , p.LastName
        , p.DateOfBirth
        , g.Visited AS DateStamp
        , 'G' AS TypeCode
    FROM
        dbo.Person AS p
    INNER JOIN
        dbo.Guest AS g ON g.Id = p.Id
    

    The following is my implementation of a single-table inheritance.

    [Table(Name = "dbo.PersonDetail")]
    [InheritanceMapping(Code = "S", Type = typeof(Staff), IsDefault = true)]
    [InheritanceMapping(Code = "G", Type = typeof(Guest))]
    public partial class Person
    {
        [Column(IsPrimaryKey=true)]
        public int Id { get; set; }
    
        [Column]
        public string FirstName { get; set; }
    
        [Column]
        public string LastName { get; set; }
    
        [Column]
        public DateTime? DateOfBirth { get; set; }
    
        [Column(IsDiscriminator = true)]
        public char TypeCode { get; set; }
    }
    
    public partial class Staff : Person
    {
        [Column(Name = "DateStamp")]
        public DateTime Joined { get; set; }
    }
    
    public partial class Guest : Person
    {
        [Column(Name="DateStamp")]
        public DateTime Visited { get; set; }
    }
    

    And I've written the following very simple DataContext class.

    public partial class PersonDataContext : DataContext
    {
        private static System.Data.Linq.Mapping.MappingSource mappingSource = new AttributeMappingSource();
        public PersonDataContext(string connection) : base(connection, mappingSource){}
        public System.Data.Linq.Table<Person> Persons { get { return this.GetTable<Person>(); } }
    }
    

    I've written a INSTEAD OF trigger for each of INSERT, UPDATE and DELETE operations so that updates will work correctly across tables.

    If you have a lot of inheritances in your database schema it might be a little tedious to maintain the triggers but I find this a pretty good solution/compromise.

    Thanks,

    Shingo

    • Marked as answer by Shingo Tamura Sunday, January 11, 2009 12:08 PM
    Wednesday, July 23, 2008 11:59 AM