modiX modiX - 3 months ago 116
C# Question

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

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 = "admin@example.org";

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_Load
of 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

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.

Comments