Entity Class Inheritance

So far, in all my LINQ to SQL discussion, there has been a single entity class mapped to a single table for any table that has an entity class mapped to it. Thus, the mapping between entity classes and tables has been one-to-one so far.

The example used in this section creates a data model containing Square and Rectangle classes. Geometrically speaking, a square is a rectangle, but a rectangle is not necessarily a square. However, in the data model created for this example, the reverse relationship is true. This class model defines a rectangle to be derived from a square. Therefore, a rectangle is a square, but a square is not necessarily a rectangle. The reasoning for this is explained in the text.

LINQ to SQL also offers an alternative to this, known as entity class inheritance. Entity class inheritance allows a class hierarchy to be mapped to a single database table. For that single database table, there must be a base entity class, and the appropriate entity class attribute mappings for the database table must be specified. That base class will contain all properties common to every class in the hierarchy deriving from the base class, while the derived classes will only contain properties that are specific to that derived class, as is typical with any object model. Here is an example of a base entity class without mapped derived classes:

Example. My Base Entity Class Without Mapped Derived Classes

[Table]
public class Shape
{
  [Column(IsPrimaryKey = true, IsDbGenerated = true,
    DbType = "Int NOT NULL IDENTITY")]
  public int Id;

  [Column(IsDiscriminator = true, DbType = "NVarChar(2)")]
  public string ShapeCode;

  [Column(DbType = "Int")]
  public int StartingX;

  [Column(DbType = "Int")]
  public int StartingY;
}

As you can see, I have specified the Table attribute, and since no Name attribute property has been specified, the base entity class is mapped to table by the same name as the class, so it is mapped to the Shape table. Don't worry that you do not have a Shape table at this time. I will use the DataContext object's CreateDatabase method later to create the database for us. At this time, no derived classes have been mapped. Later, I will come back to this base entity class to map some derived classes.

The idea behind entity class inheritance is that the single database table, Shape, has a database column whose value indicates which entity class the record should be constructed into when it is retrieved by LINQ to SQL. That column is known as the discriminator column and is specified using the Column attribute's IsDiscriminator attribute property.

A value in the discriminator column is known as the discriminator value or discriminator code. When mapping your base entity class to the database table, in addition to the Table attribute, you specify InheritanceMapping attributes to map discriminator codes to classes derived from the base entity class. But at this time, in the preceding Shape class, no inheritance has been mapped.

Notice that I have several public members, each being mapped to a database column, and the database column types have been specified. Specifying the database column types is necessary in my case, because I will be calling the CreateDatabase method later, and to do so, it must know the appropriate type. Also notice that for the ShapeCode member, I have specified that the IsDiscriminator attribute property is set to true, thereby making it the discriminator column. This means the ShapeCode database column will dictate the entity class type used to construct each record into an entity class object.

In this class, I have members for the Id, the ShapeCode, and the starting X and Y coordinates for the shape on the screen. At this time, those are the only members I foresee being common to every shape.

You may then create a class hierarchy by deriving classes from this base class. The derived classes must inherit from the base entity class. The derived classes will not specify the Table attribute but will specify Column attributes for each public member that will be mapped to the database. Here are my derived entity classes:

Example. My Derived Entity Classes

public class Square : Shape
{
    [Column(DBType = "Int")]
    public int Width;
}

public class Rectangle : Square
{
    [Column(DBType = "Int")]
    public int Length;
}

First, for this example, you must forget about the geometric definition for square and rectangle; that is, geometrically speaking, a square is a rectangle, but a rectangle is not necessarily a square. In this entity class inheritance example, because a square's sides must be equal, only one dimension value is needed, width. Since a rectangle needs a width and a length, it will inherit from the square and add a member for the length. In this sense, from a class inheritance perspective, a rectangle is a square, but a square is not a rectangle. While this is backward from the geometric definition, it fits my inheritance entity class model.

The public members of each of those classes are the members deemed specific to each class. For example, since a Square needs a width, it has a Width property. Since the Rectangle inherits from the Square, in addition to the inherited Width property, it needs a Length property.

I now have my derived classes. All I am missing is the mapping between the discriminator values, and the base and derived entity classes. Adding the necessary InheritanceMapping attributes, my base class now looks like this:

Example. My Base Entity Class with Derived Class Mappings

[Table]
[InheritanceMapping(Code = "G", Type = typeof(Shape), IsDefault = true)]
						[InheritanceMapping(Code = "S", Type = typeof(Square))]
						[InheritanceMapping(Code = "R", Type = typeof(Rectangle))]
public class Shape
{
  [Column(IsPrimaryKey = true, IsDbGenerated = true,
    DbType = "Int NOT NULL IDENTITY")]
  public int Id;

  [Column(IsDiscriminator = true, DbType = "NVarChar(2)")]
  public string ShapeCode;

  [Column(DbType = "Int")]
  public int StartingX;

  [Column(DbType = "Int")]
  public int StartingY;
}

The added mappings map the different discriminator values of the discriminator column to entity classes. Since the ShapeCode column is the discriminator column, if a record has the value "G" in that column, that record will get constructed into a Shape class. If a record has an "S" value in the ShapeCode column, that record will get constructed into a Square class. And, if a record has an "R" value in the ShapeCode column, that record will get constructed into a Rectangle class.

Additionally, there must always be a default mapping for when the discriminator column value does not match any discriminator value mapped to an entity class. You specify which mapping is the default with the IsDefault attribute property. In this example, the mapping to the Shape class is the default. So, if a record has the value "Q" in the ShapeCode column, that record will get constructed into a Shape object by default since it doesn't match any of the specified discriminator codes.

That pretty much covers the concept and mappings of entity class inheritance. Now, let's take a look at the entire DataContext:

Example. My Entire DataContext Class

public partial class TestDB : DataContext
{
  public Table<Shape> Shapes;

  public TestDB(string connection) :
    base(connection)
  {
  }

  public TestDB(System.Data.IDbConnection connection) :
    base(connection)
  {
  }

  public TestDB(string connection,
                System.Data.Linq.Mapping.MappingSource mappingSource) :
    base(connection, mappingSource)
  {
  }

  public TestDB(System.Data.IDbConnection connection,
                System.Data.Linq.Mapping.MappingSource mappingSource) :
    base(connection, mappingSource)
  {
  }
}

[Table]
[InheritanceMapping(Code = "G", Type = typeof(Shape), IsDefault = true)]
[InheritanceMapping(Code = "S", Type = typeof(Square))]
[InheritanceMapping(Code = "R", Type = typeof(Rectangle))]
public class Shape
{
  [Column(IsPrimaryKey = true, IsDbGenerated = true,
    DbType = "Int NOT NULL IDENTITY")]
  public int Id;

  [Column(IsDiscriminator = true, DbType = "NVarChar(2)")]
  public string ShapeCode;

  [Column(DbType = "Int")]
  public int StartingX;

  [Column(DbType = "Int")]
  public int StartingY;
}

public class Square : Shape
{
  [Column(DbType = "Int")]
  public int Width;
}

public class Rectangle : Square
{
  [Column(DbType = "Int")]
  public int Length;
}

There is nothing new here other than putting the previously mentioned classes in a [Your]DataContext named TestDB and adding some constructors for it. Now, in Listing 18-3, I will call some code to actually create the database.

Example. Code Creating My Entity Class Inheritance Sample Database

TestDB db = new TestDB(@"Data Source=.SQLEXPRESS;Initial Catalog=TestDB");
db.CreateDatabase();

That code doesn't have any screen output, but if you check your database server, you should see a database named TestDB with a single table named Shape. Check the Shape table to convince yourself that no records exist. Now that we have a table, let's create some data using LINQ to SQL in Listing 18-4.

Example. Code Creating Some Data for My Entity Class Inheritance Sample Database

TestDB db = new TestDB(@"Data Source=.SQLEXPRESS;Initial Catalog=TestDB");

db.Shapes.InsertOnSubmit(new Square { Width = 4 });
db.Shapes.InsertOnSubmit(new Rectangle { Width = 3, Length = 6 });
db.Shapes.InsertOnSubmit(new Rectangle { Width = 11, Length = 5 });
db.Shapes.InsertOnSubmit(new Square { Width = 6 });
db.Shapes.InsertOnSubmit(new Rectangle { Width = 4, Length = 7 });
db.Shapes.InsertOnSubmit(new Square { Width = 9 });

db.SubmitChanges();

There is nothing new in that code. I create my DataContext and entity class objects, and insert those objects into the Shapes table. Then, I call the SubmitChanges method to persist them to the database. After running this code, you should see the records in Table 18-1 in the Shape table in the TestDB database.

Since the Id column is an identity column, the values will change if you run the code more than once.

Now, I will perform a couple of queries on the table. First, in Listing 18-5, I will query for the squares, which will include rectangles since rectangles inherit from squares. Then I will query for just the rectangles:

Example. Code Querying My Entity Class Inheritance Sample Database

TestDB db = new TestDB(@"Data Source=.SQLEXPRESS;Initial Catalog=TestDB");

// First I get all squares which will include rectangles.
IQueryable<Shape> squares = from s in db.Shapes
                            where s is Square
                            select s;

Console.WriteLine("The following squares exist.");
foreach (Shape s in squares)
{
  Console.WriteLine("{0} : {1}", s.Id, s.ToString());
}

//  Now I'll get just the rectangles.
IQueryable<Shape> rectangles = from r in db.Shapes
                               where r is Rectangle
                               select r;

Console.WriteLine("{0}The following rectangles exist.", System.Environment.NewLine);
foreach (Shape r in rectangles)
{
  Console.WriteLine("{0} : {1}", r.Id, r.ToString());
}

In Listing 18-5, I basically perform the same query twice, except in the first one, I only query those records that get instantiated into squares, which includes rectangles due to my class inheritance. In the second query, I query the records that get instantiated into rectangles, which will exclude squares. Here are the results:

The following squares exist.
1 : LINQChapter18.Square
2 : LINQChapter18.Rectangle
3 : LINQChapter18.Rectangle
4 : LINQChapter18.Square
5 : LINQChapter18.Rectangle
6 : LINQChapter18.Square

The following rectangles exist.
2 : LINQChapter18.Rectangle
3 : LINQChapter18.Rectangle
5 : LINQChapter18.Rectangle

Entity class inheritance can be a useful technique for constructing an entity hierarchy from the database.