How to split a Single table across multiple Entities using Entity Framework designer

by Jagadish Pulakhandam on 5/23/2012 8:42:52 AM
Rated 0 from 0 votes
Brief: Demonstrates on creating multiple entities based on a single table (splitting multiple entities across single table).
Posted to: Programming Entity Framework (Basics) - Examples and Videos
Add to DiggAdd to del.icio.usAdd to FURLAdd to RedditAdd to YahooAdd to BlinklistAdd to GoogleAdd to ma.gnoliaAdd to ShadowsAdd to Technorati

Attached video/source code demonstrates the following:
  • How to split a single table across Multiple Entities (while developing Entity Model using Entity Framework Designer)
    • How to simplify/cut down huge entities to smaller entities (by splitting entities)
  • How to add an Entity and map it to a table manually (from VS toolbox)
  • How to add a Primary Key Column to an Entity
  • How to move columns from one entity to another (and map them accordingly using "Mapping Details" window)
  • How to create Association between two Entities manually (from VS toolbox)
  • How to add Referential Integrity for an Association available between two Entities
  • How to configure Navigation Properties between two Entities
  • How to Bind Entities/EntitySets to ASP.NET GridView (using EntityDataSource)
  • How to include properties from other entities (using Navigation Properties) during data binding (using EntityDataSource)
  • How to access "Navigation Properties" using entity "Select" (with EntityDataSource)
  • All of the above, with no code-behind
NOTE: The entire model is developed on a single table "Customers" in Northwind Database.

Screen shots:

1. Accessing basic information of Customers (just Customer Names):



2. Accessing contact information of Customers (but no Customer Names):



3. Navigation from Parent to Child - Accessing basic information of Customers along with City and Country from Contact information available in another entity (using Navigation Property "CustomerCommunication" from "Customer" entity):



3. Navigation from Child to Parent - Accessing contact information of Customers along with names available in another entity (using Navigation Property "Customer" from "CustomerCommunication" entity):



Model:

<?xml version="1.0" encoding="utf-8"?>
<edmx:Edmx Version="2.0" xmlns:edmx="http://schemas.microsoft.com/ado/2008/10/edmx">
  <!-- EF Runtime content -->
  <edmx:Runtime>
    <!-- SSDL content -->
    <edmx:StorageModels>
      <Schema Namespace="NorthwindModel.Store" Alias="Self" Provider="System.Data.SqlClient" ProviderManifestToken="2008" xmlns:store="http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator" xmlns="http://schemas.microsoft.com/ado/2009/02/edm/ssdl">
        <EntityContainer Name="NorthwindModelStoreContainer">
          <EntitySet Name="Customers" EntityType="NorthwindModel.Store.Customers" store:Type="Tables" Schema="dbo" />
        </EntityContainer>
        <EntityType Name="Customers">
          <Key>
            <PropertyRef Name="CustomerID" />
          </Key>
          <Property Name="CustomerID" Type="nchar" Nullable="false" MaxLength="5" />
          <Property Name="CompanyName" Type="nvarchar" Nullable="false" MaxLength="40" />
          <Property Name="ContactName" Type="nvarchar" MaxLength="30" />
          <Property Name="ContactTitle" Type="nvarchar" MaxLength="30" />
          <Property Name="Address" Type="nvarchar" MaxLength="60" />
          <Property Name="City" Type="nvarchar" MaxLength="15" />
          <Property Name="Region" Type="nvarchar" MaxLength="15" />
          <Property Name="PostalCode" Type="nvarchar" MaxLength="10" />
          <Property Name="Country" Type="nvarchar" MaxLength="15" />
          <Property Name="Phone" Type="nvarchar" MaxLength="24" />
          <Property Name="Fax" Type="nvarchar" MaxLength="24" />
        </EntityType>
      </Schema>
    </edmx:StorageModels>
    <!-- CSDL content -->
    <edmx:ConceptualModels>
      <Schema Namespace="NorthwindModel" Alias="Self" xmlns:annotation="http://schemas.microsoft.com/ado/2009/02/edm/annotation" xmlns="http://schemas.microsoft.com/ado/2008/09/edm">
        <EntityContainer Name="NorthwindEntities" annotation:LazyLoadingEnabled="true">
          <EntitySet Name="Customers" EntityType="NorthwindModel.Customer" />
          <EntitySet Name="CustomerCommunications" EntityType="NorthwindModel.CustomerCommunication" />
          <AssociationSet Name="CustomerCustomerCommunication" Association="NorthwindModel.CustomerCustomerCommunication">
            <End Role="Customer" EntitySet="Customers" />
            <End Role="CustomerCommunication" EntitySet="CustomerCommunications" />
          </AssociationSet>
        </EntityContainer>
        <EntityType Name="Customer">
          <Key>
            <PropertyRef Name="CustomerID" />
          </Key>
          <Property Name="CustomerID" Type="String" Nullable="false" MaxLength="5" Unicode="true" FixedLength="true" />
          <Property Name="CompanyName" Type="String" Nullable="false" MaxLength="40" Unicode="true" FixedLength="false" />
          <Property Name="ContactName" Type="String" MaxLength="30" Unicode="true" FixedLength="false" />
          <NavigationProperty Name="CustomerCommunication" Relationship="NorthwindModel.CustomerCustomerCommunication" FromRole="Customer" ToRole="CustomerCommunication" />
          </EntityType>
        <EntityType Name="CustomerCommunication">
          <Key>
            <PropertyRef Name="CustomerID" />
          </Key>
          <Property Type="String" Name="CustomerID" Nullable="false" annotation:StoreGeneratedPattern="None" MaxLength="5" FixedLength="true" Unicode="true" />
          <Property Type="String" Name="ContactTitle" MaxLength="30" FixedLength="false" Unicode="true" />
          <Property Type="String" Name="Address" MaxLength="60" FixedLength="false" Unicode="true" />
          <Property Type="String" Name="City" MaxLength="15" FixedLength="false" Unicode="true" />
          <Property Type="String" Name="Region" MaxLength="15" FixedLength="false" Unicode="true" />
          <Property Type="String" Name="PostalCode" MaxLength="10" FixedLength="false" Unicode="true" />
          <Property Type="String" Name="Country" MaxLength="15" FixedLength="false" Unicode="true" />
          <Property Type="String" Name="Phone" MaxLength="24" FixedLength="false" Unicode="true" />
          <Property Type="String" Name="Fax" MaxLength="24" FixedLength="false" Unicode="true" />
          <NavigationProperty Name="Customer" Relationship="NorthwindModel.CustomerCustomerCommunication" FromRole="CustomerCommunication" ToRole="Customer" />
        </EntityType>
        <Association Name="CustomerCustomerCommunication">
          <End Type="NorthwindModel.Customer" Role="Customer" Multiplicity="1" />
          <End Type="NorthwindModel.CustomerCommunication" Role="CustomerCommunication" Multiplicity="1" />
          <ReferentialConstraint>
            <Principal Role="Customer">
              <PropertyRef Name="CustomerID" />
            </Principal>
            <Dependent Role="CustomerCommunication">
              <PropertyRef Name="CustomerID" />
            </Dependent>
          </ReferentialConstraint>
        </Association>
      </Schema>
    </edmx:ConceptualModels>
    <!-- C-S mapping content -->
    <edmx:Mappings>
      <Mapping Space="C-S" xmlns="http://schemas.microsoft.com/ado/2008/09/mapping/cs">
        <EntityContainerMapping StorageEntityContainer="NorthwindModelStoreContainer" CdmEntityContainer="NorthwindEntities">
          <EntitySetMapping Name="Customers"><EntityTypeMapping TypeName="NorthwindModel.Customer"><MappingFragment StoreEntitySet="Customers">
            <ScalarProperty Name="CustomerID" ColumnName="CustomerID" />
            <ScalarProperty Name="CompanyName" ColumnName="CompanyName" />
            <ScalarProperty Name="ContactName" ColumnName="ContactName" />
            </MappingFragment></EntityTypeMapping></EntitySetMapping>
          <EntitySetMapping Name="CustomerCommunications">
            <EntityTypeMapping TypeName="IsTypeOf(NorthwindModel.CustomerCommunication)">
              <MappingFragment StoreEntitySet="Customers">
                <ScalarProperty Name="Fax" ColumnName="Fax" />
                <ScalarProperty Name="Phone" ColumnName="Phone" />
                <ScalarProperty Name="Country" ColumnName="Country" />
                <ScalarProperty Name="PostalCode" ColumnName="PostalCode" />
                <ScalarProperty Name="Region" ColumnName="Region" />
                <ScalarProperty Name="City" ColumnName="City" />
                <ScalarProperty Name="Address" ColumnName="Address" />
                <ScalarProperty Name="ContactTitle" ColumnName="ContactTitle" />
                <ScalarProperty Name="CustomerID" ColumnName="CustomerID" />
              </MappingFragment>
            </EntityTypeMapping>
          </EntitySetMapping>
        </EntityContainerMapping>
      </Mapping>
    </edmx:Mappings>
  </edmx:Runtime>
</edmx:Edmx>

WebForm1.aspx:

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm1.aspx.cs" Inherits="WebApplication1.WebForm1" %>
 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
 
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
     
        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
            DataKeyNames="CustomerID" DataSourceID="dscrcCustomers">
            <Columns>
                <asp:BoundField DataField="CustomerID" HeaderText="CustomerID" ReadOnly="True"
                    SortExpression="CustomerID" />
                <asp:BoundField DataField="CompanyName" HeaderText="CompanyName"
                    SortExpression="CompanyName" />
                <asp:BoundField DataField="ContactName" HeaderText="ContactName"
                    SortExpression="ContactName" />
            </Columns>
        </asp:GridView>
        <asp:EntityDataSource ID="dscrcCustomers" runat="server"
            ConnectionString="name=NorthwindEntities"
            DefaultContainerName="NorthwindEntities" EnableFlattening="False"
            EntitySetName="Customers">
        </asp:EntityDataSource>
     
    </div>
    </form>
</body>
</html>

WebForm2.aspx:

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm2.aspx.cs" Inherits="WebApplication1.WebForm2" %>
 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
 
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
     
        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
            DataSourceID="dsrcCusComms">
            <Columns>
                <asp:BoundField DataField="CustomerID" HeaderText="CustomerID" ReadOnly="True"
                    SortExpression="CustomerID" />
                <asp:BoundField DataField="ContactTitle" HeaderText="ContactTitle"
                    ReadOnly="True" SortExpression="ContactTitle" />
                <asp:BoundField DataField="City" HeaderText="City" ReadOnly="True"
                    SortExpression="City" />
                <asp:BoundField DataField="Country" HeaderText="Country" ReadOnly="True"
                    SortExpression="Country" />
                <asp:BoundField DataField="Fax" HeaderText="Fax" ReadOnly="True"
                    SortExpression="Fax" />
            </Columns>
        </asp:GridView>
        <asp:EntityDataSource ID="dsrcCusComms" runat="server"
            ConnectionString="name=NorthwindEntities"
            DefaultContainerName="NorthwindEntities" EnableFlattening="False"
            EntitySetName="CustomerCommunications"
            Select="it.[CustomerID], it.[ContactTitle], it.[City], it.[Country], it.[Fax]">
        </asp:EntityDataSource>
     
    </div>
    </form>
</body>
</html>

WebForm3.aspx:

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm3.aspx.cs" Inherits="WebApplication1.WebForm3" %>
 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
 
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
     
        <asp:GridView ID="GridView1" runat="server" DataSourceID="dsrcCusComm">
        </asp:GridView>
        <asp:EntityDataSource ID="dsrcCusComm" runat="server"
            ConnectionString="name=NorthwindEntities"
            DefaultContainerName="NorthwindEntities" EnableFlattening="False"
            EntitySetName="Customers"
            Select="it.[CustomerID], it.[CompanyName], it.[ContactName], it.[CustomerCommunication].[City], it.[CustomerCommunication].[Country]">
        </asp:EntityDataSource>
     
    </div>
    </form>
</body>
</html>

WebForm4.aspx:

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm4.aspx.cs" Inherits="WebApplication1.WebForm4" %>
 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:GridView ID="GridView1" runat="server" DataSourceID="dsrcCusComm">
        </asp:GridView>
        <asp:EntityDataSource ID="dsrcCusComm" runat="server"
            ConnectionString="name=NorthwindEntities"
            DefaultContainerName="NorthwindEntities" EnableFlattening="False"
            EntitySetName="CustomerCommunications"
            Select="it.[CustomerID], it.[ContactTitle], it.[City], it.[Country], it.[Customer].[CompanyName], it.[Customer].[ContactName]">
        </asp:EntityDataSource>
    </div>
    </form>
</body>
</html>



Join the .NET Code Central Community and join the discussion!
Signing-up is FREE and quick. Do it now, we want to hear your opinion
0

Rated 0 from 0 votes ( login  to rate)
DotnetKicks DotnetKicksDe DotNetShoutout

Attachments / Source Code
You need to Login or Join for FREE to download the following

Video/Screen Recording (may not have audio narration/annotations)
You need to Login or Join for FREE to download the following