February 26, 2017 at 13:00

Implement OOP inheritance in classes working with SQL and MS Entity Framework

This article is about creating a data model that would beautifully fit into SQL and contain the “right” OOP inheritance. I must say that this problem arose at different times at different projects, and it was also solved there 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 in it - an empty data model, into which to add classes on the basis of which the database will be generated, you will get something like the following (the tool is Visual Studio 2012):


After creation, the following data model will reside in SQL Server:


Well, very optimally, I must admit. The only thing that bothers is the specific table names. Here are the appropriate scripts for creating the database tables obtained using 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]

Only table names confuse this approach.

Approach # 2: Classification


This method shows how they did before, when the sky was higher, and dinosaurs still wrote programs in Fortran. (To admit, 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 the scripts and database-first data model, since they are quite trivial. The downside of this approach is obvious. As soon as classes B and C increase the number of columns that are not related to the ancestor of A (especially if they are char [] fields - of constant size type), then the disk space occupied by the table begins to grow sharply despite the fact that the percentage of useful information in In this cemetery, the byte is proportionally reduced. Normalization? - no, they didn’t hear ... Unfortunately, for historical reasons (for example, to maintain backward compatibility), such schemes are still found in large enterprise-projects that have been developed for several years. But in new developments, this is clearly not worth doing. You are welcome…

Approach # 3: Polymorphic View


Creating a view over tables that have the same fields in the code can be represented using the interface (view view in the code) and classes implementing it (table view in the code). Two pluses. The first is that there are no such problems with inefficient use of disk space as in the previous approach. Second: you can use indexes and other things that accelerate the unloading of data from the database. Minus - the code for SQL queries to select and add data will have to be written with pens. Here, for example, is the sample code from such a 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 fields of tables B and C do not allow such a query. You can also put in the receipt of these very BBB and CCC columns, as a result of which the answer with a bunch of NULLs will become very similar to the Classification option:

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


Personally, my two-wheeled pedal-steering solution consists in creating a separate table for each descendant class, which will be connected to the parent class table with 1-to-1 relationships.


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

Since I like to use the Entity Framework in my projects, I have to make extra efforts to create an appropriate class structure. In parallel with the classes from the Entity folder, where the database-first generated code falls, there is also the BusinessLogic folder, the classes in which have already more distinct connections. Here's how to do the conversion code “Entity Framework → Business Logic” and “Business Logic → Entity Framework”.

  1. Create the IA interface in the Entity folder.

    public interface IA
{
	A A
	{ get; }
	EntityReference AReference
	{ get; }
}

  2. We inherit from it the auto-generated classes B and C, lying in the same folder.
  3. We create an enum with a name of type AClassEnum, into which we rewrite practically all the rows from the Class table.
  4. In the “BusinessLogic” folder, create the abstract A, B: A and C: A classes. (By the way, making A abstract is not necessary - it just happened to me because of the requirements.)
  5. We write about the following:

    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 /*внимание – эта опция не обязательна и зависит от вашей модели данных*/);
		}
		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,
		};
	}
}
// аналогично для класса C

Advantages compared to:

  • ... the default approach is more beautiful table names
  • ... a “classification” table - less data
  • ... a view - everything is beautifully imported into the Entity Framework

It is clear that the proposed approach is never a “golden bullet." Moreover, the default method works so well. But I think that he can still be useful to someone in any specific circumstances.
Tags:

Also popular now: