"A dependent property in a ReferentialConstraint is mapped to a store-generated column." on a persisted computed column (EntityFramework DB first)

Question

I've implemented a simulated table inheritance construct in my SQL-Server based on the article Implementing Table Inheritance in SQL Server.

Besides the fact to use simple 1 to 0...1 relationships you create another constraint to a type table that lists all possible children types of the base table as explained in the article in the paragraph "Modeling One-to-Either Constraints".

Every of your child tables contains a TYPE field that has a ComputedColumnSpecification with a persisted number that represents the ID of the type in the type table. Due the fact the TYPE field is part of the constraint, it will make sure only one child can be created to the base data set.

For better understanding I've created a sample database that is used to describe the problem with a matching ASP.NET solution. To replicate the issue at your local environment, create a database called "PLAYGROUND" before executing this script:

USE [PLAYGROUND]
GO
/****** Object:  Table [dbo].[USER] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[USER](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [TYPE__ID] [int] NOT NULL,
    [Enabled] [bit] NOT NULL,
    [Username] [nvarchar](32) NOT NULL,
    [Password] [nchar](32) NOT NULL,
    [Email] [nvarchar](255) NOT NULL,
 CONSTRAINT [PK_USER] 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]

GO
/****** Object:  Table [dbo].[NATURAL_USER] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[NATURAL_USER](
    [ID] [int] NOT NULL,
    [TYPE]  AS ((1)) PERSISTED NOT NULL,
    [BirthDate] [date] NOT NULL,
 CONSTRAINT [PK_NATURAL_USER] 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]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[JURIDICAL_USER] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[JURIDICAL_USER](
    [ID] [int] NOT NULL,
    [TYPE]  AS ((2)) PERSISTED NOT NULL,
    [CompanyName] [nvarchar](256) NOT NULL,
    [RegistrationNo] [nvarchar](max) NOT NULL,
    [Description] [nvarchar](max) NOT NULL,
 CONSTRAINT [PK_LEGAL_USER] 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]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[USER_T] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[USER_T](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [TYPE] [nvarchar](32) NOT NULL,
 CONSTRAINT [PK_USER_T] 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]

GO
/****** Object:  Index [IX_USER] ******/
ALTER TABLE [dbo].[USER] ADD  CONSTRAINT [IX_USER] UNIQUE NONCLUSTERED 
(
    [Username] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object:  Index [PK_USER_TYPE] ******/
CREATE UNIQUE NONCLUSTERED INDEX [PK_USER_TYPE] ON [dbo].[USER]
(
    [ID] ASC,
    [TYPE__ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON

GO
/****** Object:  Index [IX_USER_T] ******/
ALTER TABLE [dbo].[USER_T] ADD  CONSTRAINT [IX_USER_T] UNIQUE NONCLUSTERED 
(
    [TYPE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON

GO
/****** TYPE DATA ******/
SET IDENTITY_INSERT [dbo].[USER_T] ON 
GO
INSERT [dbo].[USER_T] ([ID], [TYPE]) VALUES (2, N'JURIDICAL_USER')
GO
INSERT [dbo].[USER_T] ([ID], [TYPE]) VALUES (1, N'NATURAL_USER')
GO
SET IDENTITY_INSERT [dbo].[USER_T] OFF
GO
/****** Contraints ******/
ALTER TABLE [dbo].[JURIDICAL_USER]  WITH CHECK ADD  CONSTRAINT [FK_JURIDICAL_USER___USER] FOREIGN KEY([ID])
REFERENCES [dbo].[USER] ([ID])
GO
ALTER TABLE [dbo].[JURIDICAL_USER] CHECK CONSTRAINT [FK_JURIDICAL_USER___USER]
GO
ALTER TABLE [dbo].[JURIDICAL_USER]  WITH CHECK ADD  CONSTRAINT [FK_JURIDICAL_USER___USER___TYPEVALIDATION] FOREIGN KEY([ID], [TYPE])
REFERENCES [dbo].[USER] ([ID], [TYPE__ID])
GO
ALTER TABLE [dbo].[JURIDICAL_USER] CHECK CONSTRAINT [FK_JURIDICAL_USER___USER___TYPEVALIDATION]
GO
ALTER TABLE [dbo].[NATURAL_USER]  WITH CHECK ADD  CONSTRAINT [FK_NATURAL_USER___USER] FOREIGN KEY([ID])
REFERENCES [dbo].[USER] ([ID])
GO
ALTER TABLE [dbo].[NATURAL_USER] CHECK CONSTRAINT [FK_NATURAL_USER___USER]
GO
ALTER TABLE [dbo].[NATURAL_USER]  WITH CHECK ADD  CONSTRAINT [FK_NATURAL_USER___USER___TYPEVALIDATION] FOREIGN KEY([TYPE])
REFERENCES [dbo].[USER_T] ([ID])
GO
ALTER TABLE [dbo].[NATURAL_USER] CHECK CONSTRAINT [FK_NATURAL_USER___USER___TYPEVALIDATION]
GO
ALTER TABLE [dbo].[USER]  WITH CHECK ADD  CONSTRAINT [FK_USER___USER_T] FOREIGN KEY([TYPE__ID])
REFERENCES [dbo].[USER_T] ([ID])
GO
ALTER TABLE [dbo].[USER] CHECK CONSTRAINT [FK_USER___USER_T]
GO
USE [master]
GO
ALTER DATABASE [PLAYGROUND] SET  READ_WRITE 
GO

The table USER is the base table and the tables NATURAL_USER and JURIDICAL_USER are its children. USER_T is the type table of USER.

Now, in my ASP.NET application using the EntityFramework 6 I try to create a new user the following way:

using (PLAYGROUNDEntities model = new PLAYGROUNDEntities())
{
    USER user = new USER();
    user.Username = "admin";
    user.Password = "RANDOMHASH#123456";
    user.Email = "[email protected]";

    user.NATURAL_USER = new NATURAL_USER();
    user.NATURAL_USER.BirthDate = new DateTime(1980, 01, 01);

    model.USER.Add(user);
    model.SaveChanges();
}

And on model.SaveChanges(); I get the exception:

A dependent property in a ReferentialConstraint is mapped to a store-generated column. Column: 'TYPE'.

Sample solution: https://dl.dropboxusercontent.com/u/55589036/zzzOther/Playground.zip (the sample code is in the Page_Loadof the Default.aspx.cs.

I understand, the EntityFramework tries to set the column field and fails, because it's store generated (persisted). This even happens when I set user.NATURAL_USER.TYPE = 1;.

I tried to override OnModelCreating to attach my own rule and define the both TYPE columns as Computed, but OnModelCreating is never called, because I do EDMX-after and I want to stick to this.

So, this entity model is generated based on the database and I'd like to keep it this way, plus I don't want to edit any code when I update my model again, every time.

Also, I think the table inheritance concept is very well implemented on the database layer, because it does not use triggers. I want to keep it trigger-free.

How can I solve this problem?

Answer 1

I know nothing about EF, but I'd create your TYPE columns as normal columns, not calculated, not persisted.

Then I'd set their default value to the required value and add a CHECK constraint to make sure that it can't be changed.

The rest of your T-SQL script where your set up foreign keys remains the same.

For example, for NATURAL_USER it would look like this:

CREATE TABLE [dbo].[NATURAL_USER](
    [ID] [int] NOT NULL,
    [TYPE] [int] NOT NULL,
    [BirthDate] [date] NOT NULL,
CONSTRAINT [PK_NATURAL_USER] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
))
GO

ALTER TABLE [dbo].[NATURAL_USER]  WITH CHECK 
ADD  CONSTRAINT [CK_NATURAL_USER] CHECK  (([TYPE]=(1)))
GO

ALTER TABLE [dbo].[NATURAL_USER] 
CHECK CONSTRAINT [CK_NATURAL_USER]
GO

ALTER TABLE [dbo].[NATURAL_USER] 
ADD  CONSTRAINT [DF_NATURAL_USER_TYPE]  DEFAULT ((1)) FOR [TYPE]
GO

Answer 2

I have made a terrible mistake when implementing the approach, but it worked before. I messed up the constraint FK_NATURAL_USER___USER___TYPEVALIDATION, accidentally.

It should have been build like the FK_JURIDICAL_USER___USER___TYPEVALIDATION constraint.

The EF is able to handle the persisted columns. The problem was it tried to write to the PK of [USER_T] that should not be apart of the constraint at all.

I'm sorry for all people who wasted time on this.