Condividi tramite

How to: Define a Model with Associations Between Derived Types (Entity Framework)

  • Articolo

In the Entity Data Model, entities that are derived types can have associations with other derived types or with base types. The data model that is implemented in this topic uses the inheritance hierarchies shown in the topics How to: Define a Model with Table-per-Type Inheritance and How to: Define a Model with Table-per-Hierarchy Inheritance. The example in this topic modifies the association in the earlier table-per-hierarchy model by associating the derived Administrator type directly to the Department type instead of to the base type Person from which Administrator is derived. The second modification in this topic is a new association between DeptEngineering and DeptBusines, which are two entity types derived from the Department type.

You can implement this example by using the Entity Data Model Designer or by manually implementing the schemas. The basic model is most easily generated from the database by using the Entity Data Model Wizard. All but one of the required modifications can be made either in the designer or by editing the schemas in an XML editor.

Create the Database Used by the Storage Model

Use the following script with SQL Server Management Studio to generate the database that is used in this example. This is the same script that is used in the example How to: Define a Model with Table-per-Type Inheritance (Entity Framework). If you created the database for that example, go to Modify Database to Support Associations with Table-per-Type Derived Types.

To generate the database by using SQL Server Management Studio

  1. In SQL Server Management Studio, on the File menu, point to New, and then select Database Engine Query.

  2. Type either localhost or the name of another instance of SQL Server in the Connect to Database Engine dialog box, and then click Connect.

  3. Paste the following Transact-SQL script in the query window, and then click Execute.

    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]

Modify Database to Support Associations with Table-per-Type Derived Types

Use the following script in SQL Server Management Studio to modify the database that you created in the previous procedure. This script adds a foreign key to the table DeptEngineering to support associations with derived types.

To add foreign keys to support Table-per-Type derived types

  1. In SQL Server Management Studio, on the File menu, point to New, and then select Database Engine Query.

  2. Type either localhost or the name of another instance of SQL Server in the Connect to Database Engine dialog box, and then click Connect.

  3. Paste the following Transact-SQL script in the query window, and then click Execute.

    USE [SchoolData]
GO
ALTER TABLE [dbo].[DeptEngineering]
ADD [RelatedBusinessDept] int
GO
ALTER TABLE [dbo].[DeptEngineering]  
WITH CHECK ADD  CONSTRAINT [FK_DeptEngineering_DeptBusiness] 
FOREIGN KEY([RelatedBusinessDept])
REFERENCES [dbo].[DeptBusiness] ([BusinessDeptID])
GO
ALTER TABLE [dbo].[DeptEngineering] 
CHECK CONSTRAINT [FK_DeptEngineering_DeptBusiness]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', 
@value=N'Supports derived type mapping.' , 
@level0type=N'SCHEMA',
@level0name=N'dbo', 
@level1type=N'TABLE',
@level1name=N'DeptEngineering', 
@level2type=N'CONSTRAINT',
@level2name=N'FK_DeptEngineering_DeptBusiness'

Implement Project and Data Model

To implement the project and data model before modifications

  1. Create a new console application project in Visual Studio named DerivedTypesAssociations.

  2. On the Project menu, select Add New Item, and add an ADO.NET Entity Data Model.

  3. Name the new data model SchoolData, and then click Add.

  4. In the Entity Data Model Wizard, select Generate from database and then click Next.

  5. In the Choose Your Data Connection dialog box, select or create a connection to the SchoolData database.

  6. Accept the default name for the object context, SchoolDataEntities, and then click Next.

  7. In the Choose Your Data Objects dialog box, select the tables named Person, Department, DeptBusiness, and DeptEngineering.

  8. Accept the default name, SchoolDataModel, for the data model, and then click Finish.

Create Derived Types and Associations

After you complete the previous procedures, you can make modification to the data model that will be required to implement derived types and associations between derived types. The following steps modify the two entities, DeptEngineering and DeptBusiness, to make them table-per-type entities that are derived from the Department base class. In the following procedure, an Administrator type is derived from the Person type and an association is created between the derived Administrator type and the base type Department. The results of all these changes in the schemas can be seen in the complete edmx file at the end of the topic.

To create Person derived types and associations

  1. Rename the entity set Person to People.

  2. Create a new entity derived from Person named Administrator.

  3. Set the BaseType attribute of the Administrator entity to Person.

  4. Delete the AdminDate property of the Person entity.

  5. Add a property AdminDate of type DateTime to the new Administrator entity.

  6. Set the Nullable attribute of the AdminDate property to false.

  7. Map the AdminDate column of the Person table to the AdminDate property of the Administrator entity.

  8. Add a condition to the mapping: <Condition ColumnName="AdminDate" IsNull="false">. You can do this in the designer by opening the Mapping Details window and adding the condition or by adding the condition to the edmx file in an XML editor.

  9. Delete the association created by the wizard between Department and Person.

  10. Create a new association between Department and Administrator.

  11. Set both End attributes of the DepartmentAdministrator association to 1.

  12. Map the association between Department and Administrator by mapping the Administrator property of the Department table to the Administrator column and the DepartmentID property to the DepartmentID column.

  13. Add a condition to the DepartmentAdministrator association: <Condition ColumnName="Administrator" IsNull="False" />. This is the only step that you will have to do by manually editing the mapping schema in an XML editor.

To create Department derived types and associations

  1. Rename the entity set Department to Departments.

  2. Delete the key properties of DeptEngineering and DeptBusiness.

  3. Set the BaseType attribute of DeptEngineering to Department.

  4. Set the BaseType attribute of DeptBusiness to Department.

  5. Map the EngineeringDeptID column of the DeptEngineering table to the DepartmentID property of the Department entity.

  6. Map the BusinessDeptID column of the DeptBusiness table to the DepartmentID property of the Department entity.

  7. Set both End attributes of the association FK_DeptEngineering_DeptBusiness to 0..1.

  8. Map the DeptEngineering.DepartmentID of the association FK_DeptEngineering_DepartmentBusiness to the EngineeringDeptID column.

  9. Map the DeptBusiness.DepartmentID of the association FK_DeptEngineering_DeptBusiness to the RelatedBusinessDept column.

The following complete edmx file contains the storage schema, the conceptual schema, and the mapping specification for the associations with derived types that are implemented in this topic.

<?xml version="1.0" encoding="utf-8"?>
<edmx:Edmx Version="1.0" xmlns:edmx="https://schemas.microsoft.com/ado/2007/06/edmx">
  <!-- EF Runtime content -->
  <edmx:Runtime>
    <!-- SSDL content -->
    <edmx:StorageModels>
      <Schema Namespace="SchoolDataModel.Store" Alias="Self" Provider="System.Data.SqlClient" ProviderManifestToken="2005" xmlns:store="https://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator" xmlns="https://schemas.microsoft.com/ado/2006/04/edm/ssdl">
        <EntityContainer Name="SchoolDataModelStoreContainer">
          <EntitySet Name="Department" EntityType="SchoolDataModel.Store.Department" store:Type="Tables" Schema="dbo" />
          <EntitySet Name="DeptBusiness" EntityType="SchoolDataModel.Store.DeptBusiness" store:Type="Tables" Schema="dbo" />
          <EntitySet Name="DeptEngineering" EntityType="SchoolDataModel.Store.DeptEngineering" store:Type="Tables" Schema="dbo" />
          <EntitySet Name="Person" EntityType="SchoolDataModel.Store.Person" store:Type="Tables" Schema="dbo" />
          <AssociationSet Name="FK_Department_Administrator" Association="SchoolDataModel.Store.FK_Department_Administrator">
            <End Role="Person" EntitySet="Person" />
            <End Role="Department" EntitySet="Department" />
          </AssociationSet>
          <AssociationSet Name="FK_DeptEngineering_DeptBusiness" Association="SchoolDataModel.Store.FK_DeptEngineering_DeptBusiness">
            <End Role="DeptBusiness" EntitySet="DeptBusiness" />
            <End Role="DeptEngineering" EntitySet="DeptEngineering" />
          </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" />
          <Property Name="RelatedBusinessDept" Type="int" />
        </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="PersonCategory" Type="smallint" Nullable="false" />
          <Property Name="AdminDate" Type="datetime" />
        </EntityType>
        <Association Name="FK_Department_Administrator">
          <End Role="Person" Type="SchoolDataModel.Store.Person" Multiplicity="0..1" />
          <End Role="Department" Type="SchoolDataModel.Store.Department" Multiplicity="*" />
          <ReferentialConstraint>
            <Principal Role="Person">
              <PropertyRef Name="PersonID" />
            </Principal>
            <Dependent Role="Department">
              <PropertyRef Name="Administrator" />
            </Dependent>
          </ReferentialConstraint>
        </Association>
        <Association Name="FK_DeptEngineering_DeptBusiness">
          <End Role="DeptBusiness" Type="SchoolDataModel.Store.DeptBusiness" Multiplicity="0..1" />
          <End Role="DeptEngineering" Type="SchoolDataModel.Store.DeptEngineering" Multiplicity="*" />
          <ReferentialConstraint>
            <Principal Role="DeptBusiness">
              <PropertyRef Name="BusinessDeptID" />
            </Principal>
            <Dependent Role="DeptEngineering">
              <PropertyRef Name="RelatedBusinessDept" />
            </Dependent>
          </ReferentialConstraint>
        </Association>
      </Schema>
    </edmx:StorageModels>
    <!-- CSDL content -->
    <edmx:ConceptualModels>
      <Schema Namespace="SchoolDataModel" Alias="Self" xmlns="https://schemas.microsoft.com/ado/2006/04/edm">
        <EntityContainer Name="SchoolDataEntities">
          <EntitySet Name="Departments" EntityType="SchoolDataModel.Department" />
          <EntitySet Name="People" EntityType="SchoolDataModel.Person" />
          <AssociationSet Name="FK_DeptEngineering_DeptBusiness" Association="SchoolDataModel.FK_DeptEngineering_DeptBusiness">
            <End Role="DeptBusiness" EntitySet="Departments" />
            <End Role="DeptEngineering" EntitySet="Departments" />
          </AssociationSet>
          <AssociationSet Name="DepartmentAdministrator" Association="SchoolDataModel.DepartmentAdministrator">
            <End Role="Administrator" EntitySet="People" />
            <End Role="Department" EntitySet="Departments" />
          </AssociationSet>
        </EntityContainer>
        <EntityType Name="Department">
          <Key>
            <PropertyRef Name="DepartmentID" />
          </Key>
          <Property Name="DepartmentID" Type="Int32" Nullable="false" />
          <Property Name="Name" Type="String" Nullable="false" MaxLength="50" Unicode="true" FixedLength="false" />
          <Property Name="Budget" Type="Decimal" Nullable="false" Precision="19" Scale="4" />
          <Property Name="StartDate" Type="DateTime" Nullable="false" />
          <NavigationProperty Name="Administrator" Relationship="SchoolDataModel.DepartmentAdministrator" FromRole="Department" ToRole="Administrator" />
        </EntityType>
        <EntityType Name="DeptBusiness" BaseType="SchoolDataModel.Department">
          <Property Name="LegalBudget" Type="Decimal" Nullable="false" Precision="19" Scale="4" />
          <Property Name="AccountingBudget" Type="Decimal" Nullable="false" Precision="19" Scale="4" />
          <NavigationProperty Name="DeptEngineering" Relationship="SchoolDataModel.FK_DeptEngineering_DeptBusiness" FromRole="DeptBusiness" ToRole="DeptEngineering" />
        </EntityType>
        <EntityType Name="DeptEngineering" BaseType="SchoolDataModel.Department">
          <Property Name="FiberOpticsBudget" Type="Decimal" Nullable="false" Precision="19" Scale="4" />
          <Property Name="LabBudget" Type="Decimal" Nullable="false" Precision="19" Scale="4" />
          <NavigationProperty Name="DeptBusiness" Relationship="SchoolDataModel.FK_DeptEngineering_DeptBusiness" FromRole="DeptEngineering" ToRole="DeptBusiness" />
        </EntityType>
        <EntityType Name="Person">
          <Key>
            <PropertyRef Name="PersonID" />
          </Key>
          <Property Name="PersonID" Type="Int32" Nullable="false" />
          <Property Name="FirstName" Type="String" Nullable="false" MaxLength="50" Unicode="true" FixedLength="false" />
          <Property Name="LastName" Type="String" Nullable="false" MaxLength="50" Unicode="true" FixedLength="false" />
          <Property Name="HireDate" Type="DateTime" />
          <Property Name="EnrollmentDate" Type="DateTime" />
          <Property Name="PersonCategory" Type="Int16" Nullable="false" />
        </EntityType>
        <Association Name="FK_DeptEngineering_DeptBusiness">
          <End Role="DeptBusiness" Type="SchoolDataModel.DeptBusiness" Multiplicity="0..1" />
          <End Role="DeptEngineering" Type="SchoolDataModel.DeptEngineering" Multiplicity="0..1" />
        </Association>
        <EntityType Name="Administrator" BaseType="SchoolDataModel.Person" >
          <Property Name="AdminDate" Type="DateTime" Nullable="false" />
          <NavigationProperty Name="Department" Relationship="SchoolDataModel.DepartmentAdministrator" FromRole="Administrator" ToRole="Department" />
        </EntityType>
        <Association Name="DepartmentAdministrator">
          <End Type="SchoolDataModel.Administrator" Role="Administrator" Multiplicity="1" />
          <End Type="SchoolDataModel.Department" Role="Department" Multiplicity="1" />
        </Association>
      </Schema>
    </edmx:ConceptualModels>
    <!-- C-S mapping content -->
    <edmx:Mappings>
      <Mapping Space="C-S" xmlns="urn:schemas-microsoft-com:windows:storage:mapping:CS">
        <EntityContainerMapping StorageEntityContainer="SchoolDataModelStoreContainer" CdmEntityContainer="SchoolDataEntities">
          <EntitySetMapping Name="Departments">
            <EntityTypeMapping TypeName="IsTypeOf(SchoolDataModel.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="IsTypeOf(SchoolDataModel.DeptEngineering)">
              <MappingFragment StoreEntitySet="DeptEngineering">
                <ScalarProperty Name="DepartmentID" ColumnName="EngineeringDeptID" />
                <ScalarProperty Name="FiberOpticsBudget" ColumnName="FiberOpticsBudget" />
                <ScalarProperty Name="LabBudget" ColumnName="LabBudget" />
              </MappingFragment>
            </EntityTypeMapping>
            <EntityTypeMapping TypeName="IsTypeOf(SchoolDataModel.DeptBusiness)">
              <MappingFragment StoreEntitySet="DeptBusiness">
                <ScalarProperty Name="DepartmentID" ColumnName="BusinessDeptID" />
                <ScalarProperty Name="LegalBudget" ColumnName="LegalBudget" />
                <ScalarProperty Name="AccountingBudget" ColumnName="AccountingBudget" />
              </MappingFragment>
            </EntityTypeMapping>
          </EntitySetMapping>
          <EntitySetMapping Name="People">
            <EntityTypeMapping TypeName="IsTypeOf(SchoolDataModel.Person)">
              <MappingFragment StoreEntitySet="Person">
                <ScalarProperty Name="PersonID" ColumnName="PersonID" />
                <ScalarProperty Name="FirstName" ColumnName="FirstName" />
                <ScalarProperty Name="LastName" ColumnName="LastName" />
                <ScalarProperty Name="HireDate" ColumnName="HireDate" />
                <ScalarProperty Name="EnrollmentDate" ColumnName="EnrollmentDate" />
                <ScalarProperty Name="PersonCategory" ColumnName="PersonCategory" />
              </MappingFragment>
            </EntityTypeMapping>
            <EntityTypeMapping TypeName="IsTypeOf(SchoolDataModel.Administrator)">
              <MappingFragment StoreEntitySet="Person">
                <ScalarProperty Name="PersonID" ColumnName="PersonID" />
                <ScalarProperty Name="AdminDate" ColumnName="AdminDate" />
                <Condition ColumnName="AdminDate" IsNull="false" />
              </MappingFragment>
            </EntityTypeMapping>
          </EntitySetMapping>
          <AssociationSetMapping Name="DepartmentAdministrator" TypeName="SchoolDataModel.DepartmentAdministrator" StoreEntitySet="Department">
            <EndProperty Name="Administrator">
              <ScalarProperty Name="PersonID" ColumnName="Administrator" />
            </EndProperty>
            <EndProperty Name="Department">
              <ScalarProperty Name="DepartmentID" ColumnName="DepartmentID" />
            </EndProperty>
          </AssociationSetMapping>
          <AssociationSetMapping Name="FK_DeptEngineering_DeptBusiness" TypeName="SchoolDataModel.FK_DeptEngineering_DeptBusiness" StoreEntitySet="DeptEngineering" >
            <EndProperty Name="DeptBusiness">
              <ScalarProperty Name="DepartmentID" ColumnName="RelatedBusinessDept" />
            </EndProperty>
            <EndProperty Name="DeptEngineering">
              <ScalarProperty Name="DepartmentID" ColumnName="EngineeringDeptID" />
            </EndProperty>
            <Condition ColumnName="RelatedBusinessDept" IsNull="false" />
          </AssociationSetMapping>
        </EntityContainerMapping>
      </Mapping>
    </edmx:Mappings>
  </edmx:Runtime>
  <!-- EF Designer content (DO NOT EDIT MANUALLY BELOW HERE) -->
  <edmx:Designer xmlns="https://schemas.microsoft.com/ado/2007/06/edmx">
    <edmx:Connection>
      <DesignerInfoPropertySet>
        <DesignerProperty Name="MetadataArtifactProcessing" Value="EmbedInOutputAssembly" />
      </DesignerInfoPropertySet>
    </edmx:Connection>
    <edmx:Options>
      <DesignerInfoPropertySet>
        <DesignerProperty Name="ValidateOnBuild" Value="true" />
      </DesignerInfoPropertySet>
    </edmx:Options>
    <!-- Diagram content (shape and connector positions) -->
    <edmx:Diagrams>
      <Diagram Name="SchoolData">
        <EntityTypeShape EntityType="SchoolDataModel.Department" Width="1.5" PointX="3" PointY="1.125" Height="1.7566536458333335" IsExpanded="true" />
        <EntityTypeShape EntityType="SchoolDataModel.DeptBusiness" Width="1.5" PointX="7.375" PointY="3.875" Height="1.4279589843749996" IsExpanded="true" />
        <EntityTypeShape EntityType="SchoolDataModel.DeptEngineering" Width="1.5" PointX="5" PointY="3.875" Height="1.4279589843749996" IsExpanded="true" />
        <EntityTypeShape EntityType="SchoolDataModel.Person" Width="1.5" PointX="0.75" PointY="0.875" Height="2.085348307291667" IsExpanded="true" />
        <AssociationConnector Association="SchoolDataModel.FK_DeptEngineering_DeptBusiness" ManuallyRouted="false">
          <ConnectorPoint PointX="7.375" PointY="4.5889794921875" />
          <ConnectorPoint PointX="6.5" PointY="4.5889794921875" />
        </AssociationConnector>
        <EntityTypeShape EntityType="SchoolDataModel.Administrator" Width="1.5" PointX="0.5" PointY="4.5" Height="0.9349169921875" />
        <InheritanceConnector EntityType="SchoolDataModel.Administrator">
          <ConnectorPoint PointX="1.375" PointY="2.960348307291667" />
          <ConnectorPoint PointX="1.375" PointY="4.5" />
        </InheritanceConnector>
        <AssociationConnector Association="SchoolDataModel.DepartmentAdministrator">
          <ConnectorPoint PointX="2" PointY="5.1318058268229167" />
          <ConnectorPoint PointX="3.75" PointY="5.1318058268229167" />
          <ConnectorPoint PointX="3.75" PointY="2.8816536458333339" />
        </AssociationConnector>
      </Diagram>
    </edmx:Diagrams>
  </edmx:Designer>
</edmx:Edmx>

Example

The following application code is used to display and create instances of the associations with derived types that are implemented in this topic. The first segment of code creates new people and departments. Then a foreach loop reads departments and associations and displays their data. The code inside the if conditional statement only creates a new instance of the association between the derived types DeptEngineering and DeptBusiness if an instance of the association does not already exist in storage.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Objects;

namespace DerivedTypesAssociations
{
    class Program
    {
        static void Main(string[] args)
        {
            string[] firstNames = { "François", "Syed", "Erik", "Michael", "James",
                                  "Mark", "Satomi", "Phyllis", "Andreas", "Kari"};
            string[] lastNames = { "Ajenstat", "Abbas", "Andersen", "Allen", "Alvord",
                                 "Hassall", "Hayakawa", "Harris", "Hauser", "Hensien" };

            try
            {
                using (SchoolDataEntities objCtx = new SchoolDataEntities())
                {
                    int count1Depts = 0;
                    foreach (Department d in objCtx.Departments)
                        count1Depts++;

                    for(int i=count1Depts; i < 9; i++)
                    {
                        DeptEngineering newEngDept = new DeptEngineering();

                        newEngDept.DepartmentID = i + 1;
                        newEngDept.FiberOpticsBudget = 6000.00M;
                        newEngDept.LabBudget = 12000.00M;
                        newEngDept.Budget = newEngDept.FiberOpticsBudget + 
                            newEngDept.LabBudget;
                        newEngDept.Name = "Engineering Dept " + 
                            newEngDept.DepartmentID.ToString();
                        newEngDept.StartDate = DateTime.Now;

                        Administrator newAdmin1 = new Administrator();
                        newAdmin1.AdminDate = DateTime.Now;
                        newAdmin1.FirstName = firstNames[i];
                        newAdmin1.LastName = lastNames[i];
                        newAdmin1.PersonCategory = 3;
                        int count1People = 0; 
                        foreach (Person p in objCtx.People)
                            count1People++;

                        newAdmin1.PersonID = count1People + 1;

                        objCtx.AddToPeople(newAdmin1);
                        newEngDept.Administrator = newAdmin1;
                        objCtx.AddToDepartments(newEngDept);

                        objCtx.SaveChanges();
                    }

                    foreach (DeptEngineering dept in 
                        objCtx.Departments.OfType<DeptEngineering>())
                    {
                        Console.WriteLine(dept.Name);
                        dept.AdministratorReference.Load();
                        if (dept.Administrator != null)
                            Console.WriteLine("\tAdministrator: " + 
                                dept.Administrator.LastName);
                        dept.DeptBusinessReference.Load();
                        if (dept.DeptBusiness != null)
                        {
                            dept.DeptBusiness.AdministratorReference.Load();
                            Console.WriteLine("\tRelated Business School: " + 
                                dept.DeptBusiness.DepartmentID +
                                "Business School Administrator: " + 
                                dept.DeptBusiness.Administrator.LastName);

                        }
                    }

                    DeptEngineering departmentToLink;

                    ObjectParameter deptParam = new ObjectParameter("p", 5);
                    if (objCtx.Departments.OfType<DeptEngineering>().Where(
                        "it.DepartmentID = @p", deptParam).Any<DeptEngineering>())
                    {
                        departmentToLink =
                        objCtx.Departments.OfType<DeptEngineering>().
                        Where("it.DepartmentID = @p", deptParam).First<DeptEngineering>();

                        if (departmentToLink != null)
                            departmentToLink.DeptBusinessReference.Load();

                        if (departmentToLink.DeptBusiness == null)
                        {
                            DeptBusiness newDeptBusiness = new DeptBusiness();
                            int countDepts = 0; 
                            foreach (Department d in objCtx.Departments) 
                                countDepts++;

                            newDeptBusiness.DepartmentID = countDepts + 1;
                            newDeptBusiness.AccountingBudget = 5000.00M;
                            newDeptBusiness.LegalBudget = 18000.00M;
                            newDeptBusiness.Name = "Business Department Id: " + 
                                newDeptBusiness.DepartmentID;
                            newDeptBusiness.Budget = newDeptBusiness.AccountingBudget + 
                                newDeptBusiness.LegalBudget;
                            newDeptBusiness.StartDate = DateTime.Now;

                            Administrator newAdmin = new Administrator();
                            newAdmin.AdminDate = DateTime.Now;
                            newAdmin.FirstName = "Sagiv";
                            newAdmin.LastName = "Hadaya";
                            newAdmin.PersonCategory = 3;
                            int countPeople = 0; 
                            foreach (Person p in objCtx.People) 
                                countPeople++;

                            newAdmin.PersonID = countPeople + 1;

                            objCtx.AddToPeople(newAdmin);
                            newDeptBusiness.Administrator = newAdmin;
                            objCtx.AddToDepartments(newDeptBusiness);

                            objCtx.SaveChanges();

                            departmentToLink.DeptBusiness = newDeptBusiness;

                            objCtx.SaveChanges();

                        }
                    }
                }

            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.InnerException.ToString());
            }
        }
    }
}

See Also

Tasks

How to: Define a Model with Table-per-Type Inheritance (Entity Framework)
How to: Define a Model with Table-per-Hierarchy Inheritance (Entity Framework)