How to: Define a Model with Multiple Entity Sets per Type (Entity Framework)

  • Article

The Entity Data Model (EDM) allows an entity type to be included by multiple entity sets within a single entity container or for an entity type to be included in entity sets in multiple entity containers. Defining multiple entity sets per type (MEST) allows users to streamline their code when databases have partitioning or other such scenarios where multiple tables have the same structure. For more information, see Entity Sets (EDM).

To implement the conceptual schema for the MEST data model

  1. Create a class library project and add a new EDM template.

  2. Implement a Customer entity type that is included in two entity sets for customers in an eastern region, CustomersEast, and a western region, CustomersWest.

  3. Note the difference between the single Customer entity type used in two regional customer entity sets and the two order types, OrderEast and OrderWest, in the OrdersEast and OrdersWest entity sets.

  4. Implement associations between the single Customer type and two regional order types, OrderEast and OrderWest, to reflect the MEST structure. Both associations between the Customer type and regional order types specify the Customer on the association end that has a cardinality of one.

  5. Implement two entity sets that contain the Customer type in the conceptual schema definition language (CSDL) schema. The entity set CustomersEast and the entity set CustomersWest both specify the RegionalCustomersModel.Customer as their entity types.

<?xml version="1.0" encoding="utf-8"?>
<Schema Namespace="RegionalCustomersModel"
              Alias="Self"
              xmlns="https://schemas.microsoft.com/ado/2006/04/edm">

        <EntityContainer Name="RegionalCustomersEntities">

          <EntitySet Name="CustomersEast" 
                     EntityType="RegionalCustomersModel.Customer" />
          <EntitySet Name="CustomersWest" 
                     EntityType="RegionalCustomersModel.Customer" />
          <EntitySet Name="OrdersEast" 
                     EntityType="RegionalCustomersModel.OrderEast" />
          <EntitySet Name="OrdersWest" 
                     EntityType="RegionalCustomersModel.OrderWest" />

          <AssociationSet Name="FK_OrderEast_Customer"
                 Association="RegionalCustomersModel.FK_OrderEast_Customer">
            <End Role="Customer" EntitySet="CustomersEast" />
            <End Role="OrderEast" EntitySet="OrdersEast" />
          </AssociationSet>

          <AssociationSet Name="FK_OrderWest_Customer"
                 Association="RegionalCustomersModel.FK_OrderWest_Customer">
            <End Role="Customer" EntitySet="CustomersWest" />
            <End Role="OrderWest" EntitySet="OrdersWest" />
          </AssociationSet>

        </EntityContainer>

        <EntityType Name="Customer">
          <Key>
            <PropertyRef Name="CustomerId" />
          </Key>
          <Property Name="CustomerId" Type="Int32" Nullable="false" />
          <Property Name="Name" Type="String" Nullable="false" />
          <Property Name="TotalPurchases" Type="Decimal" Nullable="false" />

          <NavigationProperty Name="OrdersEast"
          Relationship="RegionalCustomersModel.FK_OrderEast_Customer"
          FromRole="Customer" ToRole="OrderEast" />

          <NavigationProperty Name="OrdersWest"
          Relationship="RegionalCustomersModel.FK_OrderWest_Customer"
          FromRole="Customer" ToRole="OrderWest" />
        </EntityType>

        <EntityType Name="OrderEast">
          <Key>
            <PropertyRef Name="OrderId" />
          </Key>
          <Property Name="OrderId" Type="Int32" Nullable="false" />
          <Property Name="OrderTotal"
                    Type="Decimal" Nullable="false" />
          <Property Name="Tax" Type="Decimal" />
          <NavigationProperty Name="Customer"
            Relationship="RegionalCustomersModel.FK_OrderEast_Customer"
              FromRole="OrderEast" ToRole="Customer" />
        </EntityType>

        <EntityType Name="OrderWest">
          <Key>
            <PropertyRef Name="OrderId" />
          </Key>
          <Property Name="OrderId" Type="Int32" Nullable="false" />
          <Property Name="OrderTotal" Type="Decimal" Nullable="false" />
          <Property Name="Tax" Type="Decimal" />
          <NavigationProperty Name="Customer"
            Relationship="RegionalCustomersModel.FK_OrderWest_Customer"
               FromRole="OrderWest" ToRole="Customer" />
        </EntityType>

        <Association Name="FK_OrderEast_Customer">
          <End Role="Customer"
               Type="RegionalCustomersModel.Customer" Multiplicity="1" />
          <End Role="OrderEast"
               Type="RegionalCustomersModel.OrderEast" Multiplicity="*" />
        </Association>

        <Association Name="FK_OrderWest_Customer">
          <End Role="Customer"
               Type="RegionalCustomersModel.Customer" Multiplicity="1" />
          <End Role="OrderWest"
               Type="RegionalCustomersModel.OrderWest" Multiplicity="*" />
        </Association>

      </Schema>

To implement the storage schema for a MEST data model

  1. Implement separate entity types corresponding to the separate tables for CustomerEast and CustomerWest.

  2. Note that there are two customer tables in the storage schema unlike the single Customer entity type conceptual schema.

<?xml version="1.0" encoding="utf-8"?>
<Schema Namespace="RegionalCustomersModel.Store"
              Alias="Self"
              Provider="System.Data.SqlClient"
              ProviderManifestToken="2005"
             xmlns="https://schemas.microsoft.com/ado/2006/04/edm/ssdl">

        <EntityContainer Name="dbo">

          <EntitySet Name="CustomerEast"
             EntityType="RegionalCustomersModel.Store.CustomerEast" />
          <EntitySet Name="CustomerWest"
             EntityType="RegionalCustomersModel.Store.CustomerWest" />
          <EntitySet Name="OrderEast"
             EntityType="RegionalCustomersModel.Store.OrderEast" />
          <EntitySet Name="OrderWest"
             EntityType="RegionalCustomersModel.Store.OrderWest" />

          <AssociationSet Name="FK_OrderEast_CustomerEast"
           Association="RegionalCustomersModel.Store.FK_OrderEast_CustomerEast">
            <End Role="CustomerEast" EntitySet="CustomerEast" />
            <End Role="OrderEast" EntitySet="OrderEast" />
          </AssociationSet>

          <AssociationSet Name="FK_OrderWest_CustomerWest"
           Association="RegionalCustomersModel.Store.FK_OrderWest_CustomerWest">
            <End Role="CustomerWest" EntitySet="CustomerWest" />
            <End Role="OrderWest" EntitySet="OrderWest" />
          </AssociationSet>

        </EntityContainer>

        <EntityType Name="CustomerEast">
          <Key>
            <PropertyRef Name="CustomerId" />
          </Key>
          <Property Name="CustomerId" Type="int" Nullable="false" />
          <Property Name="Name" Type="nvarchar"
                    Nullable="false" MaxLength="50" />
          <Property Name="TotalPurchases" Type="money" Nullable="false" />
        </EntityType>

        <EntityType Name="CustomerWest">
          <Key>
            <PropertyRef Name="CustomerId" />
          </Key>
          <Property Name="CustomerId" Type="int" Nullable="false" />
          <Property Name="Name" Type="nvarchar"
                    Nullable="false" MaxLength="50" />
          <Property Name="TotalPurchases" Type="money" Nullable="false" />
        </EntityType>

        <EntityType Name="OrderEast">
          <Key>
            <PropertyRef Name="OrderId" />
          </Key>
          <Property Name="OrderId" Type="int" Nullable="false" />
          <Property Name="CustomerId" Type="int" Nullable="false" />
          <Property Name="OrderTotal" Type="money" Nullable="false" />
          <Property Name="Tax" Type="money" />
        </EntityType>

        <EntityType Name="OrderWest">
          <Key>
            <PropertyRef Name="OrderId" />
          </Key>
          <Property Name="OrderId" Type="int" Nullable="false" />
          <Property Name="CustomerId" Type="int" Nullable="false" />
          <Property Name="OrderTotal" Type="money" Nullable="false" />
          <Property Name="Tax" Type="money" />
        </EntityType>

        <Association Name="FK_OrderEast_CustomerEast">
          <End Role="CustomerEast"
           Type="RegionalCustomersModel.Store.CustomerEast" Multiplicity="1" />
          <End Role="OrderEast" Type="RegionalCustomersModel.Store.OrderEast"
               Multiplicity="*" />
          <ReferentialConstraint>
            <Principal Role="CustomerEast">
              <PropertyRef Name="CustomerId" />
            </Principal>
            <Dependent Role="OrderEast">
              <PropertyRef Name="CustomerId" />
            </Dependent>
          </ReferentialConstraint>
        </Association>

        <Association Name="FK_OrderWest_CustomerWest">
          <End Role="CustomerWest"
            Type="RegionalCustomersModel.Store.CustomerWest"
               Multiplicity="1" />
          <End Role="OrderWest"
               Type="RegionalCustomersModel.Store.OrderWest"
               Multiplicity="*" />
          <ReferentialConstraint>
            <Principal Role="CustomerWest">
              <PropertyRef Name="CustomerId" />
            </Principal>
            <Dependent Role="OrderWest">
              <PropertyRef Name="CustomerId" />
            </Dependent>
          </ReferentialConstraint>
        </Association>

      </Schema>

To generate the database using SQL Server Management Studio

  1. Use the following script with SQL Server Management Studio to generate the database used in this example and the example in How to: Define a Model with Table-per-Type Inheritance (Entity Framework).

  2. Point to New on the File menu and then click Database Engine Query to create the SchoolData database and schema with SQL Server Management Studio.

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

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

USE [master]
GO
CREATE DATABASE [RegionalCustomersMEST] 
GO
USE [RegionalCustomersMEST]
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].[CustomerWest]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[CustomerWest](
     [CustomerId] [int] NOT NULL,
     [Name] [nvarchar](50) NOT NULL,
     [TotalPurchases] [money] NOT NULL,
 CONSTRAINT [PK_CustomerWest] PRIMARY KEY CLUSTERED 
(
     [CustomerId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) 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].[CustomerEast]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[CustomerEast](
     [CustomerId] [int] NOT NULL,
     [Name] [nvarchar](50) NOT NULL,
     [TotalPurchases] [money] NOT NULL,
 CONSTRAINT [PK_CustomerEast] PRIMARY KEY CLUSTERED 
(
     [CustomerId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) 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].[OrderWest]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[OrderWest](
     [OrderId] [int] NOT NULL,
     [CustomerId] [int] NOT NULL,
     [OrderTotal] [money] NOT NULL,
     [Tax] [money] NULL,
 CONSTRAINT [PK_OrderWest] PRIMARY KEY CLUSTERED 
(
     [OrderId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) 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].[OrderEast]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[OrderEast](
     [OrderId] [int] NOT NULL,
     [CustomerId] [int] NOT NULL,
     [OrderTotal] [money] NOT NULL,
     [Tax] [money] NULL,
 CONSTRAINT [PK_OrderEast] PRIMARY KEY CLUSTERED 
(
    [OrderId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_OrderWest_CustomerWest]') AND parent_object_id = OBJECT_ID(N'[dbo].[OrderWest]'))
ALTER TABLE [dbo].[OrderWest]  WITH CHECK ADD  CONSTRAINT [FK_OrderWest_CustomerWest] FOREIGN KEY([CustomerId])
REFERENCES [dbo].[CustomerWest] ([CustomerId])
GO
ALTER TABLE [dbo].[OrderWest] CHECK CONSTRAINT [FK_OrderWest_CustomerWest]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_OrderEast_CustomerEast]') AND parent_object_id = OBJECT_ID(N'[dbo].[OrderEast]'))
ALTER TABLE [dbo].[OrderEast]  WITH CHECK ADD  CONSTRAINT [FK_OrderEast_CustomerEast] FOREIGN KEY([CustomerId])
REFERENCES [dbo].[CustomerEast] ([CustomerId])
GO
ALTER TABLE [dbo].[OrderEast] CHECK CONSTRAINT [FK_OrderEast_CustomerEast]

To implement the mapping specification for the MEST model

  1. Map the single Customer entity to separate tables for CustomerEast and CustomerWest as shown below.

  2. Note the separate entity sets for CustomersEast and CustomersWest in the entity container mapping.

<?xml version="1.0" encoding="utf-8"?>
<Mapping Space="C-S"
               xmlns="urn:schemas-microsoft-com:windows:storage:mapping:CS">

        <EntityContainerMapping
          StorageEntityContainer="dbo"
          CdmEntityContainer="RegionalCustomersEntities">

          <EntitySetMapping Name="CustomersEast">
            <EntityTypeMapping TypeName="RegionalCustomersModel.Customer">
              <MappingFragment StoreEntitySet="CustomerEast">
                <ScalarProperty Name="CustomerId" ColumnName="CustomerId" />
                <ScalarProperty Name="Name" ColumnName="Name" />
                <ScalarProperty Name="TotalPurchases"
                                ColumnName="TotalPurchases" />
              </MappingFragment>
            </EntityTypeMapping>
          </EntitySetMapping>

          <EntitySetMapping Name="CustomersWest">
            <EntityTypeMapping TypeName="RegionalCustomersModel.Customer">
              <MappingFragment StoreEntitySet="CustomerWest">
                <ScalarProperty Name="CustomerId" ColumnName="CustomerId" />
                <ScalarProperty Name="Name" ColumnName="Name" />
                <ScalarProperty Name="TotalPurchases"
                                ColumnName="TotalPurchases" />
              </MappingFragment>
            </EntityTypeMapping>
          </EntitySetMapping>

          <EntitySetMapping Name="OrdersEast" StoreEntitySet="OrderEast"
                            TypeName="RegionalCustomersModel.OrderEast">
            <ScalarProperty Name="OrderId" ColumnName="OrderId" />
            <ScalarProperty Name="OrderTotal" ColumnName="OrderTotal" />
            <ScalarProperty Name="Tax" ColumnName="Tax" />
          </EntitySetMapping>

          <EntitySetMapping Name="OrdersWest" StoreEntitySet="OrderWest"
                            TypeName="RegionalCustomersModel.OrderWest">
            <ScalarProperty Name="OrderId" ColumnName="OrderId" />
            <ScalarProperty Name="OrderTotal" ColumnName="OrderTotal" />
            <ScalarProperty Name="Tax" ColumnName="Tax" />
          </EntitySetMapping>

          <AssociationSetMapping Name="FK_OrderEast_Customer"
                 TypeName="RegionalCustomersModel.FK_OrderEast_Customer"
                 StoreEntitySet="OrderEast">
            <EndProperty Name="Customer">
              <ScalarProperty Name="CustomerId" ColumnName="CustomerId" />
            </EndProperty>
            <EndProperty Name="OrderEast">
              <ScalarProperty Name="OrderId" ColumnName="OrderId" />
            </EndProperty>
            <Condition ColumnName="CustomerId" IsNull="false" />
          </AssociationSetMapping>

          <AssociationSetMapping Name="FK_OrderWest_Customer"
                 TypeName="RegionalCustomersModel.FK_OrderWest_Customer"
                 StoreEntitySet="OrderWest">
            <EndProperty Name="Customer">
              <ScalarProperty Name="CustomerId" ColumnName="CustomerId" />
            </EndProperty>
            <EndProperty Name="OrderWest">
              <ScalarProperty Name="OrderId" ColumnName="OrderId" />
            </EndProperty>
            <Condition ColumnName="CustomerId" IsNull="false" />
          </AssociationSetMapping>

        </EntityContainerMapping>

      </Mapping>

See Also

Tasks

How to: Create and Execute Object Queries using Multiple Entity Sets per Type (Entity Framework)
How to: Add an Object to a Specific Entity Set (Entity Framework)