SSDL View and CUD operations

As you can see, I blog a lot on ssdl views. But what does happen when you want to CUD an entity which maps on a table and (with Entity Splitting) a SSDL view?

Fisrt if you change only the properties mapped on the columns of the table and then if you want to save your changes on DB, all is ok. Why? Because EF doesn’t generate an UPDATE per table / view mapped systematically but it generates one per table / view modified.

The SSDL view are Read Only. So you will have some exceptions if you want to insert or delete an entity mapped on an SSDL View or if you want to change properties mapped on SSDL View columns and then save it.

So how can we do? In this case, you need to define yourself SSDL Functions (which can use stored procedures).

Imagine this is my SSDL before adding SSDL Functions:

<EntityContainer Name=TestModelStoreContainer>

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

    <EntitySet Name=StupidTest EntityType=TestModel.Store.StupidTest >

        <DefiningQuery>

            SELECT Id, 1 AS VALUE

            FROM Test

        </DefiningQuery>

    </EntitySet>

</EntityContainer>

<EntityType Name=Test>

    <Key>

        <PropertyRef Name=Id />

    </Key>

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

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

</EntityType>

<EntityType Name=StupidTest>

    <Key>

        <PropertyRef Name=Id />

    </Key>

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

    <Property Name=Value Type=int />

</EntityType>


Test entity type has three properties:

  • Id (Key)
  • Name
  • Value (RO)

Now, I will define the three SSDL Functions:

<Function Name=InsertTest IsComposable=false>

    <CommandText>INSERT INTO Test VALUES(@Id, @Name)</CommandText>

    <Parameter Name=Id Type=int Mode=In />

    <Parameter Name=Name Type=nvarchar Mode=In />

</Function>

<Function Name=UpdateTest IsComposable=false>

    <CommandText>UPDATE Test SET Name = @Name WHERE Id = @Id</CommandText>

    <Parameter Name=Id Type=int Mode=In />

    <Parameter Name=Name Type=nvarchar Mode=In />

</Function>

<Function Name=DeleteTest IsComposable=false>

    <CommandText>DELETE Test WHERE Id = @Id</CommandText>

    <Parameter Name=Id Type=int Mode=In />

</Function>

And then, map them on the entity type CUD operations:

<EntitySetMapping Name=Tests>

    <EntityTypeMapping TypeName=IsTypeOf(TestModel.Test)>

        <MappingFragment StoreEntitySet=Test>

            <ScalarProperty Name=Id ColumnName=Id />

            <ScalarProperty Name=Name ColumnName=Name />

        </MappingFragment>

        <MappingFragment StoreEntitySet=StupidTest>

            <ScalarProperty Name=Value ColumnName=Value />

            <ScalarProperty Name=Id ColumnName=Id />

        </MappingFragment>

    </EntityTypeMapping>

    <EntityTypeMapping TypeName=TestModel.Test>

        <ModificationFunctionMapping>

            <InsertFunction FunctionName=TestModel.Store.InsertTest >

                <ScalarProperty Name=Name ParameterName=Name />

                <ScalarProperty Name=Id ParameterName=Id />

            </InsertFunction>

            <UpdateFunction FunctionName=TestModel.Store.UpdateTest>

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

                <ScalarProperty Name=Id ParameterName=Id Version=Original />

            </UpdateFunction>

            <DeleteFunction FunctionName=TestModel.Store.DeleteTest>

                <ScalarProperty Name=Id ParameterName=Id />

            </DeleteFunction>

        </ModificationFunctionMapping>

    </EntityTypeMapping>

</EntitySetMapping>

It is interesting to note that UPDATE doesn’t include Value in its parameters but if I modify Value property, there is no problem, no request is executed in DB (which is what we wanted).

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

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>