Implementing OOP Inheritance in Classes that work with SQL and Entity Framework

This article is about creating a data model that would nicely fit SQL and contain “proper” OOP inheritance. I must say that I encountered this problem at various times on different projects, and I solved it in different ways. The names of the approaches are taken from the terminology that has developed on the relevant projects.

Approach # 1: Default

The easiest way is to completely trust the Entity Framework mechanism. If you create an empty project and an empty data model in it, add classes to the model, that will serve as a basis for a database to be generated, you will get something like this (the tool is Visual Studio 2012):

After that, the following data model will exist on SQL Server:

Well, rather optimal, I must admit. The only confusing thing is the specific names of the tables. Here are the corresponding scripts for creating database tables obtained with the “Tasks/Generate scripts” tool:

CREATE TABLE [dbo].[ASet](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[AAA] [nvarchar](max) NOT NULL,
 CONSTRAINT [PK_ASet] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

CREATE TABLE [dbo].[ASet_C](
	[CCC] [nvarchar](max) NOT NULL,
	[Id] [int] NOT NULL,
 CONSTRAINT [PK_ASet_C] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

CREATE TABLE [dbo].[ASet_B](
	[BBB] [nvarchar](max) NOT NULL,
	[Id] [int] NOT NULL,
 CONSTRAINT [PK_ASet_B] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]


ALTER TABLE [dbo].[ASet_C]  WITH CHECK ADD  CONSTRAINT [FK_C_inherits_A] FOREIGN KEY([Id]) REFERENCES [dbo].[ASet] ([Id]) ON DELETE CASCADE
ALTER TABLE [dbo].[ASet_C] CHECK CONSTRAINT [FK_C_inherits_A]

ALTER TABLE [dbo].[ASet_B]  WITH CHECK ADD  CONSTRAINT [FK_B_inherits_A] FOREIGN KEY([Id]) REFERENCES [dbo].[ASet] ([Id]) ON DELETE CASCADE
ALTER TABLE [dbo].[ASet_B] CHECK CONSTRAINT [FK_B_inherits_A]

The only thing that confuses me in this approach is the table names.

Approach # 2: Classification

This method shows the approach used in the past when the programs were developed on FORTRAN. (To be honest, it seemed to me that in the era of MS SQL Server 2005 and Visual Studio 2008, I got exactly this result using the “Generate Database from Model” from the Entity Framework.)

I will omit scripts and a database-first data model because they are quite trivial. The downside of this approach is obvious. As soon as the number of columns not belonging to parent A grow up in classes B and C (especially if they are char[] fields of constant size), the disk space occupied by the table begins to grow sharply, while the share of useful information in this byte cemetery is proportionally reduced. Normalization? – No, have not heard of that… Unfortunately, for historical reasons (for example, to maintain the backward compatibility), such schemes still can be found in large enterprise projects, the development of which has been going on for several years. But in new developments, this is clearly not the best idea.

Approach # 3: Polymorphic View

In the code, the creation of a view based on the tables, which have the same fields, can be represented using an interface (representing a view in the code) and the classes implementing the interface (representing a table in the code). There are two advantages. The first is that there are no such problems with the inefficient use of disk space, as in the previous approach. The second is: you can use indexes and other things that accelerate the unloading of data from the database. The disadvantage is that you will have to manually write SQL queries for retrieving and adding data. For example, here is a sample code from this view:

CREATE VIEW [A] AS SELECT * FROM (
SELECT [AID] AS ID, 1 AS [ClassID], [AAA] FROM [B]
UNION ALL
SELECT [AID] AS ID, 2 AS [ClassID], [AAA] FROM [C]
) Q

Obviously, the query does not allow selecting fields of tables B and C. You can change the query to additionally select columns BBB and CCC, whereby the result with a bunch of NULLs will become very similar to the Classification approach:

CREATE VIEW [A] AS SELECT * FROM (
SELECT [AID] AS ID, 1 AS [ClassID], [AAA], [BBB], NULL AS [CCC] FROM [B]
UNION ALL
SELECT [AID] AS ID, 2 AS [ClassID], [AAA] , NULL AS [BBB], [CCC] FROM [C]
) Q

Approach # 4: Hierarchical Tables

My own approach is to create a separate table for each child class that will be linked to the parent class table by one-to-one relationships.

Obviously, the integrity of this scheme has to be maintained with the help of triggers that will cut records from the parent table while deleting the corresponding child ones (and vice versa) and control the addition/editing of records so that a child from table X corresponds to a record from the parent with type “X”, and not “Y”, for example.

Since I like to use Entity Framework in my projects, I have to make extra efforts to create the appropriate class structure. Along with the classes from the “Entity” folder, where the database-first generated code gets, there is also the “BusinessLogic” folder, the classes in which have more clear links. Here’s how the “Entity Framework → Business Logic” and “Business Logic → Entity Framework” conversion code is implemented.

1. Create the IA interface in the “Entity” folder.

public interface IA
{
	A A
	{ get; }

	EntityReference<A> AReference
	{ get; }
}Inherit therefrom the auto-generated classes B and C, which are in the same folder.

2. Inherit therefrom the auto-generated classes B and C, which are in the same folder.
3. Create enum with a name such as AClassEnum, in which all the rows from the Class table are written.
4. In the “BusinessLogic” folder, create the abstract A, B:A and C:A classes. (By the way, it’s not necessary to make A abstract – I just do it because of the requirements.)
5. Write something like this:

public abstract class A
{
	public long ID
	{ get; set; }

	public abstract ClassEnum Class
	{ get; }

	public string AAA
	{ get; set; }

	protected A()
	{ }

	protected A(Entity.IA a)
	{
		if (!a.AReference.IsLoaded)
		{
			a.AReference.Load(MergeOption.NoTracking /*Attention - this option is optional and depends on your data model*/);
		}
		if (a.A.ClassID != (byte) Class)
		{
			throw new Exception("Class type {0} instead of {1}!", a.A.Class, (ClassEnum) a.A.ClassID));
		}
		ID = a.A.ID;
	}


	public Entity. A CreateA()
	{
		return new Entity.A
		{
			ClassID = (byte) Class,
		};
	}
}

public class B : A
{
	public string BBB
	{ get; set; }

	public override ClassEnum Class
	{ get { return ClassEnum.B; } }

	public B()
		: base()
	{ }

	public B(Entity.B b)
		: base(b)
	{
		BBB = b.BBB;
	}

	public override Entity.B ToEntity()
	{
		return new Entity.B
		{
			A = CreateA(),
			BBB = BBB,
		};
	}
}

// Similarly for class C

Advantages compared to:

1. 1. • … the default approach – more attractive table names
      • … the “classification” table – a smaller amount of data
      • … view – everything is neatly imported into Entity Framework

It is clear that the suggested approach is never a “universal remedy”. Especially given that the “by default” method works out so well. But I think that my approach can come in handy in some specific circumstances.