Tip 34 – How to work with Updatable Views
UPDATE: thanks Zeeshan for pointing out that by default only non-nullable columns end up in the key for view backed entities.
Imagine this situation, you have a view in your database, and it is updatable.
Next you decide to use this view with the Entity Framework, so you go ahead and import it.
The resulting entity will look something like this:
As you can see every property icon has a ‘key’ overlay.
Because the entity is based on a view, the EF doesn’t know which columns make up the primary key, so it assumes every non nullable column is part of the primary key.
Fixing the Key
The first step is to change the key. In this case the ID is really the key.
You can do this by opening the EDMX in the XML editor and changing the EntityType so that rather than looking like this:
Where every property is referenced in the <Key>, change it to this:
It is important to note that you have to make this change in both the <edmx:StorageModels> and the <edmx:ConceptualModels> sections of the EDMX, because both models must agree about the shape of the primary key.
Treat the view as a table
At this point you can happily query for Employees use the Entity Framework.
But the Entity Framework won’t allow you to do updates.
The normal way around this is to create stored procedures and use them as modification functions.
But given that the view is already updatable that is obviously not ideal.
Luckily there is a workaround: Simply convince the EF that the view is a table.
To do this you have to change the definition of the EntitySet in the StorageModel. Generally it will start off looking like this:
<EntitySet Name="Employees"
EntityType="Tip34Model.Store.Employees"
store:Type="Views"
store:Schema="dbo"
store:Name="Employees">
<DefiningQuery>SELECT
[Employees].[ID] AS [ID],
[Employees].[Firstname] AS [Firstname],
[Employees].[Surname] AS [Surname],
[Employees].[Email] AS [Email]
FROM [dbo].[Employees] AS [Employees]
</DefiningQuery>
</EntitySet>
In order to treat it as a table replace that with this:
<EntitySet Name="Employees"
EntityType="Tip34Model.Store.Employees"
store:Type="Tables"
Schema="dbo" />
Now you can perform every CRUD operation.
Pretty easy if you ask me.
