EDM can save your hair :-)

I am doing a training this week and one of my student told me that his DB can be used with EF. Indeed, his base isn’t great for EF:

  • a table Bases with 4 columns:
    • Id, int, Identity (PK)
    • Key, int, not null
    • Value, int, not null
    • Text, nvarchar

He explained me that Value is an int between 1 and 9 which can’t be changed and Key is Id * 10 + Value

  • a table Inheriteds with 3 columns:
    • Id, int, Identity (PK)
    • Key, int, not null
    • Text2, nvarchar

His Key column is the same as the Bases one and so, in fact, he has a relationship 1 -> 0..1 between Bases and Inheriteds.

  • a table Descriptions with 4 columns:
    • Id, int, Identity (PK)
    • Key, int, not null
    • Language, nchar
    • Text, nvarchar

His Key column is the same as the Bases one and he has a relationship 1 -> * between Bases and Descriptions, as if Descriptions PK was Key and Language.

In CSDL part, he wanted to have:

  • an asbtract entity type Base (mapped on Bases)
  • an entity type Entity which inherits Base and which is mapped on Inheriteds
  • an entity Description
  • an 1 -> * association between Base and Description

Is it possible to do this scenario with EF? Barack Obama knows the answer: yes we can! [:)]

So how to do this?

  • First, in the designer, after importing tables and renaming generated entity types, you delete the Id property of each.
  • Then, you set Base.Key, Description.Key and Description.Language as EntityKey.
  • You set Base entity key abstract
  • You delete Entity.Key
  • You add inheritance between Entity and Base and you fix the Entity mapping (ie: you map Key column on Base.Key)
  • You add association between Base and Description

Of course, at this point, your model isn’t correct.

To fix it, you have to change the SSDL and to simulate a DB as you want (without Id and with Key as PK).

As Key is computed, you change his StoreGeneratedPattern to Computed.

Then you have to manage the CUD operations of Base entity type because you have to compute the KeyValue.

Base is abstract so you have to define CUD operations for its inherited entity types (Entity here).

<EntityContainer Name=TestModelStoreContainer>

    <EntitySet Name=Bases EntityType=TestModel.Store.Bases store:Type=Tables Schema=dbo />

    <EntitySet Name=Descriptions EntityType=TestModel.Store.Descriptions store:Type=Tables Schema=dbo />

    <EntitySet Name=Inheriteds EntityType=TestModel.Store.Inheriteds store:Type=Tables Schema=dbo />

</EntityContainer>

<EntityType Name=Bases>

    <Key>

        <PropertyRef Name=Key />

    </Key>

    <!–<Property Name=”Id” Type=”int” Nullable=”false” StoreGeneratedPattern=”Identity” />–>

    <Property Name=Key Type=int Nullable=false StoreGeneratedPattern=Computed />

    <Property Name=Value Type=int Nullable=false />

    <Property Name=Text Type=nvarchar MaxLength=50 />

</EntityType>

<EntityType Name=Descriptions>

    <Key>

        <PropertyRef Name=Key />

        <PropertyRef Name=Language />

    </Key>

    <!–<Property Name=”Id” Type=”int” Nullable=”false” StoreGeneratedPattern=”Identity” />–>

    <Property Name=Key Type=int Nullable=false />

    <Property Name=Language Type=nchar Nullable=false MaxLength=20 />

    <Property Name=Text Type=nvarchar MaxLength=50 />

</EntityType>

<EntityType Name=Inheriteds>

    <Key>

        <PropertyRef Name=Key />

    </Key>

    <!–<Property Name=”Id” Type=”int” Nullable=”false” StoreGeneratedPattern=”Identity” />–>

    <Property Name=Key Type=int Nullable=false StoreGeneratedPattern=Computed />

    <Property Name=Text2 Type=nvarchar MaxLength=50 />

</EntityType>

 

<Function Name=InsertEntity IsComposable=false>

    <CommandText>

        INSERT INTO Bases

        ([Key], Value ,[Text])

        VALUES

        (-1, @Value, @Text)

 

        DECLARE @IdValue int

        SET @IdValue = SCOPE_IDENTITY()

 

        DECLARE @KeyValue int

        SET @KeyValue = @IdValue * 10 + @Value

 

        UPDATE Bases

        SET [Key] = @KeyValue

        WHERE Id = @IdValue

 

        INSERT INTO Inheriteds

        ([Key], Text2)

        VALUES

        (@KeyValue, @Text2)

 

        SELECT @KeyValue AS KeyValue

    </CommandText>

    <Parameter Name=Value Type=int />

    <Parameter Name=Text Type=nvarchar MaxLength=50 />

    <Parameter Name=Text2 Type=nvarchar MaxLength=50 />

</Function>

<Function Name=UpdateEntity IsComposable=false>

    <CommandText>

        UPDATE Bases

        SET [Text] = @Text

        WHERE [Key] = @Key

 

        UPDATE Inheriteds

        SET Text2 = @Text2

        WHERE [Key] = @Key

    </CommandText>

    <Parameter Name=Key Type=int />

    <Parameter Name=Text Type=nvarchar MaxLength=50 />

    <Parameter Name=Text2 Type=nvarchar MaxLength=50 />

</Function>

<Function Name=DeleteEntity IsComposable=false>

    <CommandText>

        DELETE Inheriteds

        WHERE [Key] = @Key

 

        DELETE Bases

        WHERE [Key] = @Key

    </CommandText>

    <Parameter Name=Key Type=int />

</Function>


Now, I have to map Entity entity type with my SSDL functions:

<EntitySetMapping Name=BaseSet>

    <EntityTypeMapping TypeName=IsTypeOf(TestModel.Base)>

        <MappingFragment StoreEntitySet=Bases>

            <ScalarProperty Name=Key ColumnName=Key />

            <ScalarProperty Name=Value ColumnName=Value />

            <ScalarProperty Name=Text ColumnName=Text />

        </MappingFragment>

    </EntityTypeMapping>

    <EntityTypeMapping TypeName=TestModel.Entity>

        <MappingFragment StoreEntitySet=Inheriteds>

            <ScalarProperty Name=Key ColumnName=Key />

            <ScalarProperty Name=Text2 ColumnName=Text2 />

        </MappingFragment>

        <ModificationFunctionMapping>

            <InsertFunction FunctionName=TestModel.Store.InsertEntity>

                <ScalarProperty Name=Value ParameterName=Value/>

                <ScalarProperty Name=Text ParameterName=Text/>

                <ScalarProperty Name=Text2 ParameterName=Text2/>

                <ResultBinding Name=Key ColumnName=KeyValue/>

            </InsertFunction>

            <UpdateFunction FunctionName=TestModel.Store.UpdateEntity>

                <ScalarProperty Name=Key ParameterName=Key Version=Current/>

                <ScalarProperty Name=Text ParameterName=Text Version=Current/>

                <ScalarProperty Name=Text2 ParameterName=Text2 Version=Current/>

            </UpdateFunction>

            <DeleteFunction FunctionName=TestModel.Store.DeleteEntity>

                <ScalarProperty Name=Key ParameterName=Key/>

            </DeleteFunction>

        </ModificationFunctionMapping>

    </EntityTypeMapping>

</EntitySetMapping>



And that’s all. I can now have a good object conception for my entities without concerning myselft with the DB constraints.

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

2 Responses to EDM can save your hair :-)

  1. Evan Smith says:

    Actually, I am pulling my hair out. How do I in the EDM Modeling tool in Visual Studio 2008, tell the entity to allow the db to let the db generate the newsequentialid?

    Evan Smith
    Evan@ecsweb.com

  2. Matthieu MEZIL says:

    In the SSDL, you have an attribute StoreGeneratedPattern per column. Set it to Identity.

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>