Editable Views in Entity Framework

Last time I explained how you could create an association between a view and a table inentity framework.  Now let’s take it a step further.  

The inherent ‘problem’ with working with a view as opposed to working with a table is that a view is read only.  Yet out of the box, Entity Framework can work with this view like it would with a table and update and delete rows out of this view.

      I will be explaining two ways for updating your view in this post:  

1. For simple views we can just remove the defining query
2. For more advanced views we can map stored procedures

A cautious word: only do this with the simplest of views, or even better, write stored procedures that will do this for you.  I’m just explaining the possibility that exists in Entity Framework to do this and not really encouraging the use of updating views.

So that being said, let’s get down to business.

Removing the defining query

Open you edmx in your xml editor and then search for the line that describes your View:

          <EntitySet store:Name="vw_Person" EntityType="Model.Store.vw_Person" store:Type="Views" store:Schema="dbo" store:Name="vw_Person">

            <DefiningQuery>

               store:Name="vw_Person"

              SELECT

              [vw_Person].[UserId] AS [UserId],

              [vw_Person].[PersonId] AS [PersonId],

              [vw_Person].[FirstName] AS [FirstName],

              [vw_Person].[LastName] AS [LastName],

              [vw_Person].[Sex] AS [Sex],

              [vw_Person].[Titel] AS [Titel],

              [vw_Person].[Email] AS [Email],

              [vw_Person].[Street] AS [Street],

              [vw_Person].[Street2] AS [Street2],

              [vw_Person].[Zipcode] AS [Zipcode],

              [vw_Person].[City] AS [City],

              [vw_Person].[Country] AS [Country],

              [vw_Person].[CountryCode] AS [CountryCode],

              [vw_Person].[OrganisatieNaam] AS [OrganisatieNaam],

              [vw_Person].[Rrn] AS [Rrn],

              [vw_Person].[OnlineModified] AS [OnlineModified],

              [vw_Person].[DatePasswordSent] AS [DatePasswordSent],

              [vw_Person].[CreateDate] AS [CreateDate],

              [vw_Person].[CountryId] AS [CountryId],

              [vw_Person].[ModifyDate] AS [ModifyDate],

              [vw_Person].[Image] AS [Image]

              FROM [dbo].[vw_Person] AS [vw_Person]

            </DefiningQuery>

          </EntitySet>

In order to make it updatable you have to remove the defining query.  When that is done also remove the store:Name attribute and  then remove the store prefix from the store:Schema attribute. When this is done the EntitySet element should look something like this:

          <EntitySet Name="vw_Person" EntityType="Model.Store.vw_Person" store:Type="Views" Schema="dbo" />

Now save the edmx and try to update your view like you would with any other entity.

This method will only work when your view is updatable.  When your view contains a calculated value or something similar then this method will fall short.  In this case we’ll have to manually assign the stored procedures that are responsible for updating the entity.

Using stored procedures

So in my example I have made a simple stored procedure that will update the values of the Person view.  I then import the stored procedure by clicking ‘Update Model from Database’ and then selecting the appropriate stored procedure to add.

Once the stored procedure is added, click on your view and go to the ‘Mapping Details’ toolwindow.  On the left side of this toolwindow you can select ‘Map Entity to Functions’.  In here is where you can define your Update, Insert and Delete functions that Entity Framework should use when doing these operations.

Voila that was it, see you next time ;-)