Entity Framework: How to use Entity Splitting with different PK?

Imagine you want to create an application which manages product stocks using Northwind but which doesn’t create products.

For this, we want two Entitysets: Product and Supplier with Supplier on read only.

We also want a read only product CategoryName property.

 

How to do this with the least possible amount of work?

To begin with, we will change all Supplier properties set visibility to private. For this, we will modify xml Setter attribute.


Of course, we can do this with EDM designer properties Window (from VS 2008 SP1 Beta).

Then, we will also change the Supplier Product property Setter visibility to private and change the Products Supplier property (get + set) visibility to private.

Next, we will remove Product CategoryID property.

At this point, you should have this csdl:

<!– CSDL content –>

<edmx:ConceptualModels>

    <Schema Namespace=NorthwindEFModel Alias=Self xmlns=http://schemas.microsoft.com/ado/2006/04/edm>

        <EntityContainer Name=NorthwindEFEntities>

            <EntitySet Name=Products EntityType=NorthwindEFModel.Product />

            <EntitySet Name=Suppliers EntityType=NorthwindEFModel.Supplier />

            <AssociationSet Name=FK_Products_Suppliers Association=NorthwindEFModel.FK_Products_Suppliers>

                <End Role=Suppliers EntitySet=Suppliers />

                <End Role=Products EntitySet=Products />

            </AssociationSet>

        </EntityContainer>

        <EntityType Name=Product>

            <Key>

                <PropertyRef Name=ProductID />

            </Key>

            <Property Name=ProductID Type=Int32 Nullable=false />

            <Property Name=ProductName Type=String Nullable=false MaxLength=40 Unicode=true FixedLength=false />

            <Property Name=QuantityPerUnit Type=String MaxLength=20 Unicode=true FixedLength=false />

            <Property Name=UnitPrice Type=Decimal Precision=19 Scale=4 />

            <Property Name=UnitsInStock Type=Int16 />

            <Property Name=UnitsOnOrder Type=Int16 />

            <Property Name=ReorderLevel Type=Int16 />

            <Property Name=Discontinued Type=Boolean Nullable=false />

            <NavigationProperty Name=Supplier Relationship=NorthwindEFModel.FK_Products_Suppliers FromRole=Products ToRole=Suppliers a:SetterAccess=Private xmlns:a=http://schemas.microsoft.com/ado/2006/04/codegeneration />

        </EntityType>

        <EntityType Name=Supplier>

            <Key>

                <PropertyRef Name=SupplierID />

            </Key>

            <Property Name=SupplierID Type=Int32 Nullable=false />

            <Property Name=CompanyName Type=String Nullable=false a:SetterAccess=Private xmlns:a=http://schemas.microsoft.com/ado/2006/04/codegeneration />

            <Property Name=ContactName Type=String Nullable=true a:SetterAccess=Private xmlns:a=http://schemas.microsoft.com/ado/2006/04/codegeneration />

            <Property Name=ContactTitle Type=String Nullable=true a:SetterAccess=Private xmlns:a=http://schemas.microsoft.com/ado/2006/04/codegeneration />

            <Property Name=Address Type=String Nullable=true a:SetterAccess=Private xmlns:a=http://schemas.microsoft.com/ado/2006/04/codegeneration />

            <Property Name=City Type=String Nullable=true a:SetterAccess=Private xmlns:a=http://schemas.microsoft.com/ado/2006/04/codegeneration />

            <Property Name=Region Type=String Nullable=true a:SetterAccess=Private xmlns:a=http://schemas.microsoft.com/ado/2006/04/codegeneration />

            <Property Name=PostalCode Type=String Nullable=true a:SetterAccess=Private xmlns:a=http://schemas.microsoft.com/ado/2006/04/codegeneration />

            <Property Name=Country Type=String Nullable=true a:SetterAccess=Private xmlns:a=http://schemas.microsoft.com/ado/2006/04/codegeneration />

            <Property Name=Phone Type=String Nullable=true a:SetterAccess=Private xmlns:a=http://schemas.microsoft.com/ado/2006/04/codegeneration />

            <Property Name=Fax Type=String Nullable=true a:SetterAccess=Private xmlns:a=http://schemas.microsoft.com/ado/2006/04/codegeneration />

            <Property Name=HomePage Type=String Nullable=true a:SetterAccess=Private xmlns:a=http://schemas.microsoft.com/ado/2006/04/codegeneration />

            <NavigationProperty Name=Products Relationship=NorthwindEFModel.FK_Products_Suppliers FromRole=Suppliers ToRole=Products a:SetterAccess=Private xmlns:a=http://schemas.microsoft.com/ado/2006/04/codegeneration a:GetterAccess=Private />

        </EntityType>

        <Association Name=FK_Products_Suppliers>

            <End Type=NorthwindEFModel.Supplier Role=Suppliers Multiplicity=0..1 />

            <End Type=NorthwindEFModel.Product Role=Products Multiplicity=* />

        </Association>

    </Schema>

</edmx:ConceptualModels>


Now, we just need to add Product CategoryName property.

How to do this? We can use a SQL view instead of Products table and use stored procedures for CUD operations. In the same way, we can also use ssdl view with ssdl functions (for more information, look at my EDM article).  However, we want to do the least possible amount of work and, on this purpose, we can use a better approach.

We will define a partial ssdl view and we will use EntitySplitting to keep the Product part already generated by designer. With this approach, we won’t have to define ssdl CUD functions because we suppose Product CategoryName property is read only.

To do this, in SSDL part, we will define a new EntitySet:

<EntitySet Name=ProductCategoryName EntityType=NorthwindEFModel.Store.ProductCategoryName>

    <DefiningQuery>

        SELECT P.ProductID, C.CategoryName

        FROM Products AS P

        INNER JOIN Categories as C ON C.CategoryID = P.CategoryID

    </DefiningQuery>

</EntitySet>


And a new EntityType (also in SSDL):

<EntityType Name=ProductCategoryName>

    <Key>

        <PropertyRef Name=ProductID />

    </Key>

    <Property Name=ProductID Type=int Nullable=false StoreGeneratedPattern=Identity />

    <Property Name=CategoryName Type=nvarchar MaxLength=15 />

</EntityType>


Now, we can back to designer and add CategoryName Product property (of type string with setter private) and do EntitySplitting on Product EntityType.



That’s all, folks! [:)] You have a read only EntityType Supplier and an EntityType Product with a read only CategoryName property.


Isn’t it too easy to use Entity Framework? [:)]

This entry was posted in 7671, 7674, 7675. Bookmark the permalink.

3 Responses to Entity Framework: How to use Entity Splitting with different PK?

  1. Someone says:

    This post is misleading. You mentioned “different PK”, but Products and ProductCategoryName have the same PK!

  2. Matthieu MEZIL says:

    Not in my DB.

  3. I have two tables. tbl_profiles and tbl_photos.

    Now tbl_profiles has a pk that is in tbl_photos.

    this is the ProfileID of int type.

    tbl_profiles tbl_photos

    ProfileID(PK) PhotoID (PK)

    ……… ProfileID (FK)

    ———————————————–

    I want to create a entity data model with the two tables merged into one.

    With reference to the following article http://blogs.msdn.com/b/bethmassi/archive/2008/10/15/editing-data-from-two-tables-in-a-single-datagridview.aspx

    I am try to merge the table into one. When I compile. I get the following:

    Error 6 Error 3024: Problem in mapping fragments starting at line 234: Must specify mapping for all key properties (tbl_Profile.ProfileID, tbl_Profile.PhotoID) of the EntitySet tbl_Profile.
    C:\owner\wwwroot\OmegaLove\ProfileGenerator\ProfilesPhotos.edmx 235 105 ProfileGenerator

    When copy the fields from tbl_Photos should I copy ProfileID (FK) ?

    Any help would be appreciated.

    Matt

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>