如何:通过每个层次结构一个表继承以定义模型(实体框架)
在 实体数据模型 (EDM) 中可以通过多种方式实现继承。每个层次结构一个表 (table-per-hierarchy) 方法在存储中使用一个表来维护继承层次结构中所有类型的数据。本节包含用于在每个层次结构一个表方案中实现的简单继承层次结构的架构和映射。
在 EDM 中,每个层次结构一个表模型中继承层次结构的概念架构在派生类型的声明中包括 BaseType 属性指定。将分别声明每个 EntityType,即使存储模型只使用一个表来管理此方案的数据也是如此。EntityContainer 的声明只包含基类型的 EntitySet 声明。
本示例中的关联在基类型之上实现,因为关联的定义指的是 EntitySet 声明。派生类型在 EntityContainer 中没有 EntitySet 声明。
为每个层次结构一个表继承层次结构实现概念架构
创建一个类库项目,并添加一个新的 ADO.NET 实体数据模型的空模型。
用 XML 编辑器打开 .edmx 文件。
通过声明 Person 实体基类型和从基类型派生 Student、Instructor 和 Administrator 实体,实现概念架构定义语言 (CSDL)。
使用如下所示的语法。Student、Instructor 和 Administrator 类型继承 Person 基类型的属性。Student 类型添加 EnrollmentDate 属性。Instructor 添加 HireDate 属性。
将 AdminDate 添加到 Administrator 实体,以指示此人员成为系管理员的日期。
实现 Person 基类型和由名为 People 的 EntitySet 在逻辑上包含的派生类型 Student 和 Instructor。
<!-- CSDL content -->
<edmx:ConceptualModels>
<Schema xmlns="https://schemas.microsoft.com/ado/2006/04/edm"
Namespace="SchoolDataLib" Alias="Self">
<EntityContainer Name="SchoolDataLibContainer">
<EntitySet Name="Departments"
EntityType="SchoolDataLib.Department" />
<EntitySet Name="People"
EntityType="SchoolDataLib.Person" />
<AssociationSet Name="FK_Department_Administrator"
Association="SchoolDataLib.FK_Department_Administrator">
<End Role="Person" EntitySet="People" />
<End Role="Department" EntitySet="Departments" />
</AssociationSet>
</EntityContainer>
<EntityType Name="Department">
<!--Base type table-per-type inheritance-->
<Key>
<PropertyRef Name="DepartmentID" />
</Key>
<Property Name="DepartmentID" Type="Int32" Nullable="false" />
<Property Name="Name" Type="String" Nullable="false" />
<Property Name="Budget" Type="Decimal" Nullable="false" />
<Property Name="StartDate" Type="DateTime" Nullable="false" />
<NavigationProperty Name="Administrator"
Relationship="SchoolDataLib.FK_Department_Administrator"
FromRole="Department" ToRole="Person" />
</EntityType>
<EntityType Name="DeptBusiness"
BaseType="SchoolDataLib.Department">
<Property Name="LegalBudget"
Type="Decimal" Nullable="false" />
<Property Name="AccountingBudget"
Type="Decimal" Nullable="false" />
</EntityType>
<EntityType Name="DeptEngineering"
BaseType="SchoolDataLib.Department">
<Property Name="FiberOpticsBudget"
Type="Decimal" Nullable="false" />
<Property Name="LabBudget"
Type="Decimal" Nullable="false" />
</EntityType>
<EntityType Name="DeptMusic"
BaseType="SchoolDataLib.Department">
<Property Name="TheaterBudget"
Type="Decimal" Nullable="false" />
<Property Name="InstrumentBudget"
Type="Decimal" Nullable="false" />
</EntityType>
<Association Name="FK_Department_Administrator">
<End Role="Person"
Type="SchoolDataLib.Person" Multiplicity="0..1" />
<End Role="Department"
Type="SchoolDataLib.Department" Multiplicity="*" />
</Association>
<EntityType Name="Person">
<!--Base type table-per-hierarchy inheritance-->
<Key>
<PropertyRef Name="PersonID" />
</Key>
<Property Name="PersonID" Type="Int32" Nullable="false" />
<Property Name="FirstName" Type="String" Nullable="false" />
<Property Name="LastName" Type="String" Nullable="false" />
<NavigationProperty Name="Department"
Relationship="SchoolDataLib.FK_Department_Administrator"
FromRole="Person" ToRole="Department" />
</EntityType>
<EntityType Name="Student" BaseType="SchoolDataLib.Person">
<Property Name="EnrollmentDate" Type="DateTime" />
</EntityType>
<EntityType Name="Instructor" BaseType="SchoolDataLib.Person">
<Property Name="HireDate" Type="DateTime" />
</EntityType>
<EntityType Name="Administrator" BaseType="SchoolDataLib.Person">
<Property Name="AdminDate" Type="DateTime" />
</EntityType>
</Schema>
</edmx:ConceptualModels>
为每个层次结构一个表继承层次结构实现存储元数据
在存储中为层次结构中的 Student、Instructor 和 Administrator 类型实现一个表。
声明从 Person 基类型派生的三种类型。只需 Person 表即可包含所有派生类型的数据。最终属性 PersonCategory 是鉴别器列。其值用于指定这些类型之一的派生实例是 Student、Instructor 还是 Administrator。
<!-- SSDL content -->
<edmx:StorageModels>
<Schema xmlns="https://schemas.microsoft.com/ado/2006/04/edm/ssdl"
Namespace="SchoolDataLib.Target"
Alias="Self" Provider="System.Data.SqlClient"
ProviderManifestToken="2005">
<EntityContainer Name="dbo">
<EntitySet Name="Department"
EntityType="SchoolDataLib.Target.Department" />
<EntitySet Name="DeptBusiness"
EntityType="SchoolDataLib.Target.DeptBusiness" />
<EntitySet Name="DeptEngineering"
EntityType="SchoolDataLib.Target.DeptEngineering" />
<EntitySet Name="DeptMusic"
EntityType="SchoolDataLib.Target.DeptMusic" />
<EntitySet Name="Person"
EntityType="SchoolDataLib.Target.Person" />
<AssociationSet Name="FK_Department_Administrator"
Association="SchoolDataLib.Target.FK_Department_Administrator">
<End Role="Person" EntitySet="Person" />
<End Role="Department" EntitySet="Department" />
</AssociationSet>
</EntityContainer>
<EntityType Name="Department">
<Key>
<PropertyRef Name="DepartmentID" />
</Key>
<Property Name="DepartmentID" Type="int" Nullable="false" />
<Property Name="Name" Type="nvarchar"
Nullable="false" MaxLength="50" />
<Property Name="Budget" Type="money" Nullable="false" />
<Property Name="StartDate" Type="datetime" Nullable="false"/>
<Property Name="Administrator" Type="int" />
</EntityType>
<EntityType Name="DeptBusiness">
<Key>
<PropertyRef Name="BusinessDeptID" />
</Key>
<Property Name="BusinessDeptID"
Type="int" Nullable="false" />
<Property Name="LegalBudget"
Type="money" Nullable="false" />
<Property Name="AccountingBudget"
Type="money" Nullable="false" />
</EntityType>
<EntityType Name="DeptEngineering">
<Key>
<PropertyRef Name="EngineeringDeptID" />
</Key>
<Property Name="EngineeringDeptID"
Type="int" Nullable="false" />
<Property Name="FiberOpticsBudget"
Type="money" Nullable="false" />
<Property Name="LabBudget"
Type="money" Nullable="false" />
</EntityType>
<EntityType Name="DeptMusic">
<Key>
<PropertyRef Name="DeptMusicID" />
</Key>
<Property Name="DeptMusicID"
Type="int" Nullable="false" />
<Property Name="TheaterBudget"
Type="money" Nullable="false" />
<Property Name="InstrumentBudget"
Type="money" Nullable="false" />
</EntityType>
<EntityType Name="Person">
<Key>
<PropertyRef Name="PersonID" />
</Key>
<Property Name="PersonID"
Type="int" Nullable="false" />
<Property Name="FirstName"
Type="nvarchar" Nullable="false" MaxLength="50" />
<Property Name="LastName"
Type="nvarchar" Nullable="false" MaxLength="50" />
<Property Name="HireDate" Type="datetime" />
<Property Name="EnrollmentDate" Type="datetime" />
<Property Name="AdminDate" Type="datetime" />
<Property Name="PersonCategory"
Type="smallint" Nullable="false" />
</EntityType>
<Association Name="FK_Department_Administrator">
<End Role="Person"
Type="SchoolDataLib.Target.Person" Multiplicity="0..1"/>
<End Role="Department"
Type="SchoolDataLib.Target.Department" Multiplicity="*"/>
<ReferentialConstraint>
<Principal Role="Person">
<PropertyRef Name="PersonID" />
</Principal>
<Dependent Role="Department">
<PropertyRef Name="Administrator" />
</Dependent>
</ReferentialConstraint>
</Association>
</Schema>
</edmx:StorageModels>
使用 SQL Server Management Studio 生成数据库
在 SQL Server Management Studio 中使用以下脚本以生成在本示例和示例如何:通过每种类型一个表继承以定义模型(实体框架) 中使用的数据库。
指向“文件”菜单上的**“新建”,然后单击“数据库引擎查询”**以使用 SQL Server Management Studio 创建 SchoolData 数据库和架构。
在“连接到数据库引擎”对话框中键入 localhost 或其他 SQL Server 实例的名称,然后单击**“连接”**。
将以下 Transact-SQL 脚本粘贴到查询窗口中,然后单击“执行”。
USE [master]
GO
CREATE DATABASE [SchoolData]
GO
USE [SchoolData]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DeptBusiness]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[DeptBusiness](
[BusinessDeptID] [int] NOT NULL,
[LegalBudget] [money] NOT NULL,
[AccountingBudget] [money] NOT NULL,
CONSTRAINT [PK_DeptBusiness] PRIMARY KEY CLUSTERED
(
[BusinessDeptID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DeptEngineering]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[DeptEngineering](
[EngineeringDeptID] [int] NOT NULL,
[FiberOpticsBudget] [money] NOT NULL,
[LabBudget] [money] NOT NULL,
CONSTRAINT [PK_DeptEngineering] PRIMARY KEY CLUSTERED
(
[EngineeringDeptID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DeptMusic]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[DeptMusic](
[DeptMusicID] [int] NOT NULL,
[TheaterBudget] [money] NOT NULL,
[InstrumentBudget] [money] NOT NULL,
CONSTRAINT [PK_DeptMusic] PRIMARY KEY CLUSTERED
(
[DeptMusicID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Course]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Course](
[CourseID] [int] NOT NULL,
[Title] [nvarchar](100) NOT NULL,
[StartDate] [datetime] NOT NULL,
[EndDate] [datetime] NOT NULL,
[Credits] [int] NULL,
CONSTRAINT [PK_Course] PRIMARY KEY CLUSTERED
(
[CourseID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Person]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Person](
[PersonID] [int] NOT NULL,
[FirstName] [nvarchar](50) NOT NULL,
[LastName] [nvarchar](50) NOT NULL,
[HireDate] [datetime] NULL,
[EnrollmentDate] [datetime] NULL,
[PersonCategory] [smallint] NOT NULL,
[AdminDate] [datetime] NULL,
CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED
(
[PersonID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Enrollment]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Enrollment](
[EnrollmentID] [int] NOT NULL,
[CourseID] [int] NOT NULL,
[StudentID] [int] NOT NULL,
CONSTRAINT [PK_Enrollment] PRIMARY KEY CLUSTERED
(
[EnrollmentID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CourseInstructor]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[CourseInstructor](
[CourseInstructorID] [int] NOT NULL,
[CourseID] [int] NOT NULL,
[InstructorID] [int] NOT NULL,
CONSTRAINT [PK_CourseInstructor] PRIMARY KEY CLUSTERED
(
[CourseInstructorID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Department]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Department](
[DepartmentID] [int] NOT NULL,
[Name] [nvarchar](50) NOT NULL,
[Budget] [money] NOT NULL,
[StartDate] [datetime] NOT NULL,
[Administrator] [int] NULL,
CONSTRAINT [PK_Department] PRIMARY KEY CLUSTERED
(
[DepartmentID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Enrollment_Course]') AND parent_object_id = OBJECT_ID(N'[dbo].[Enrollment]'))
ALTER TABLE [dbo].[Enrollment] WITH CHECK ADD CONSTRAINT [FK_Enrollment_Course] FOREIGN KEY([CourseID])
REFERENCES [dbo].[Course] ([CourseID])
GO
ALTER TABLE [dbo].[Enrollment] CHECK CONSTRAINT [FK_Enrollment_Course]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Enrollment_Student]') AND parent_object_id = OBJECT_ID(N'[dbo].[Enrollment]'))
ALTER TABLE [dbo].[Enrollment] WITH CHECK ADD CONSTRAINT [FK_Enrollment_Student] FOREIGN KEY([StudentID])
REFERENCES [dbo].[Person] ([PersonID])
GO
ALTER TABLE [dbo].[Enrollment] CHECK CONSTRAINT [FK_Enrollment_Student]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_CourseInstructor_Course]') AND parent_object_id = OBJECT_ID(N'[dbo].[CourseInstructor]'))
ALTER TABLE [dbo].[CourseInstructor] WITH CHECK ADD CONSTRAINT [FK_CourseInstructor_Course] FOREIGN KEY([CourseID])
REFERENCES [dbo].[Course] ([CourseID])
GO
ALTER TABLE [dbo].[CourseInstructor] CHECK CONSTRAINT [FK_CourseInstructor_Course]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_CourseInstructor_Instructor]') AND parent_object_id = OBJECT_ID(N'[dbo].[CourseInstructor]'))
ALTER TABLE [dbo].[CourseInstructor] WITH CHECK ADD CONSTRAINT [FK_CourseInstructor_Instructor] FOREIGN KEY([InstructorID])
REFERENCES [dbo].[Person] ([PersonID])
GO
ALTER TABLE [dbo].[CourseInstructor] CHECK CONSTRAINT [FK_CourseInstructor_Instructor]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Department_Administrator]') AND parent_object_id = OBJECT_ID(N'[dbo].[Department]'))
ALTER TABLE [dbo].[Department] WITH CHECK ADD CONSTRAINT [FK_Department_Administrator] FOREIGN KEY([Administrator])
REFERENCES [dbo].[Person] ([PersonID])
GO
ALTER TABLE [dbo].[Department] CHECK CONSTRAINT [FK_Department_Administrator]
为每个层次结构一个表继承层次结构实现映射规范
将每个层次结构一个表概念架构的实体映射到存储架构中的实体。使用单个表来包含在此每个层次结构一个表示例中定义的三种类型的数据。映射架构用映射规范语言 (MSL) 编写。
在每个层次结构一个表继承的这一示例中,使用鉴别器列以指定层次架构中的类型。此架构中的 Condition 行包含 Column 属性和 Value 属性。值 Value 为 0 的 PersonCategoryColumn 指示基类型 Person。Value 为 1 指示 Student 类型;Value 为 2 指示 Instructor 类型;而 Value 为 3 指示 Administrator 类型。
将层次结构中的所有这三个 EntityTypeMapping 段映射到名为 People 的单个 EntitySetMapping 之下。完整的映射规范如下所示。
<!-- C-S mapping content -->
<edmx:Mappings>
<Mapping xmlns="urn:schemas-microsoft-com:windows:storage:mapping:CS"
Space="C-S">
<Alias Key="Model" Value="SchoolDataLib" />
<Alias Key="Target" Value="SchoolDataLib.Target" />
<EntityContainerMapping CdmEntityContainer="SchoolDataLibContainer"
StorageEntityContainer="dbo">
<!-- Mapping for table-per-type inheritance-->
<EntitySetMapping Name="Departments">
<EntityTypeMapping
TypeName="IsTypeOf(SchoolDataLib.Department)">
<MappingFragment StoreEntitySet="Department">
<ScalarProperty
Name="DepartmentID" ColumnName="DepartmentID" />
<ScalarProperty Name="Name" ColumnName="Name" />
<ScalarProperty Name="Budget" ColumnName="Budget" />
<ScalarProperty
Name="StartDate" ColumnName="StartDate" />
</MappingFragment>
</EntityTypeMapping>
<EntityTypeMapping TypeName="SchoolDataLib.DeptBusiness">
<MappingFragment StoreEntitySet="DeptBusiness">
<ScalarProperty Name="DepartmentID"
ColumnName="BusinessDeptID" />
<ScalarProperty Name="AccountingBudget"
ColumnName="AccountingBudget" />
<ScalarProperty Name="LegalBudget"
ColumnName="LegalBudget" />
</MappingFragment>
</EntityTypeMapping>
<EntityTypeMapping TypeName="SchoolDataLib.DeptEngineering">
<MappingFragment StoreEntitySet="DeptEngineering">
<ScalarProperty Name="DepartmentID"
ColumnName="EngineeringDeptID" />
<ScalarProperty Name="FiberOpticsBudget"
ColumnName="FiberOpticsBudget" />
<ScalarProperty Name="LabBudget"
ColumnName="LabBudget" />
</MappingFragment>
</EntityTypeMapping>
<EntityTypeMapping TypeName="SchoolDataLib.DeptMusic">
<MappingFragment StoreEntitySet="DeptMusic">
<ScalarProperty Name="DepartmentID"
ColumnName="DeptMusicID" />
<ScalarProperty Name="TheaterBudget"
ColumnName="TheaterBudget" />
<ScalarProperty Name="InstrumentBudget"
ColumnName="InstrumentBudget" />
</MappingFragment>
</EntityTypeMapping>
</EntitySetMapping>
<!--Mapping for table-per-hierarchy inheritance-->
<EntitySetMapping Name="People">
<EntityTypeMapping TypeName="SchoolDataLib.Person">
<MappingFragment StoreEntitySet="Person">
<ScalarProperty Name="PersonID" ColumnName="PersonID"/>
<ScalarProperty Name="FirstName" ColumnName="FirstName"/>
<ScalarProperty Name="LastName" ColumnName="LastName"/>
<Condition ColumnName="PersonCategory" Value="0" />
</MappingFragment>
</EntityTypeMapping>
<EntityTypeMapping TypeName="SchoolDataLib.Student">
<MappingFragment StoreEntitySet="Person">
<ScalarProperty Name="PersonID" ColumnName="PersonID" />
<ScalarProperty Name="FirstName" ColumnName="FirstName" />
<ScalarProperty Name="LastName" ColumnName="LastName" />
<ScalarProperty
Name="EnrollmentDate" ColumnName="EnrollmentDate" />
<Condition ColumnName="PersonCategory" Value="1" />
</MappingFragment>
</EntityTypeMapping>
<EntityTypeMapping TypeName="SchoolDataLib.Instructor">
<MappingFragment StoreEntitySet="Person">
<ScalarProperty Name="PersonID" ColumnName="PersonID" />
<ScalarProperty Name="FirstName" ColumnName="FirstName" />
<ScalarProperty Name="LastName" ColumnName="LastName" />
<ScalarProperty Name="HireDate" ColumnName="HireDate" />
<Condition ColumnName="PersonCategory" Value="2" />
</MappingFragment>
</EntityTypeMapping>
<EntityTypeMapping TypeName="SchoolDataLib.Administrator">
<MappingFragment StoreEntitySet="Person">
<ScalarProperty Name="PersonID" ColumnName="PersonID" />
<ScalarProperty Name="FirstName" ColumnName="FirstName" />
<ScalarProperty Name="LastName" ColumnName="LastName" />
<ScalarProperty Name="AdminDate" ColumnName="AdminDate" />
<Condition ColumnName="PersonCategory" Value="3" />
</MappingFragment>
</EntityTypeMapping>
</EntitySetMapping>
<AssociationSetMapping Name="FK_Department_Administrator"
TypeName="SchoolDataLib.FK_Department_Administrator"
StoreEntitySet="Department">
<EndProperty Name="Person">
<ScalarProperty Name="PersonID" ColumnName="Administrator" />
</EndProperty>
<EndProperty Name="Department">
<ScalarProperty Name="DepartmentID" ColumnName="DepartmentID" />
</EndProperty>
<Condition ColumnName="Administrator" IsNull="false" />
</AssociationSetMapping>
</EntityContainerMapping>
</Mapping>
</edmx:Mappings>
另请参见
任务
如何:使用每个层次结构一个表继承创建和执行对象查询(实体框架)
如何:通过每个层次结构一个表继承添加和修改对象(实体框架)
如何:使用每个层次结构一个表继承创建和执行对象查询(实体框架)