Tip 34 – How to work with Updatable Views

  • Article

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:

Model

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:

EntityKey

Where every property is referenced in the <Key>, change it to this:

EntityKeyUpdated 

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.

Comments

  • Anonymous
    September 01, 2009
    Thank you so much, I was wondering about how to get through this all week :)
  • Anonymous
    September 02, 2009
    Barbaros, you are welcome, I'm just glad this was timely for you!
  • Anonymous
    September 02, 2009
    Since the designer always replaces the SSDL, I presume one would have to re-do this every time one updates from the DB in the designer? The solution is easy -- once. Is there a way to not have to do this on every update?BTW, I'm giving a presentation on the EF next week at the online CodeRage conference:"How to Think Like the Entity Framework (and why you might want to bother learning to do so in the first place)"http://conferences.embarcadero.com/coderage/sessions
  • Anonymous
    September 02, 2009
    Craig,I hear you, the way the designer blows away your SSDL really is unfortunate.I'm not aware of any elegant solution to this problem. Of course that doesn't stop someone writing something to help by using the low-level model inference APIs, and some sort of merging algorithm.But as you and I both know that is not going to a trivial exercise. Maybe an ISV can fill the gap here, someone like Kristofer (Huagati Tools). We really should pester him!Good luck with your presentation!Alex
  • Anonymous
    September 02, 2009
    I thought not every column is marked as primary key.In fact every non nullable column is marked as primary key.
  • Anonymous
    September 02, 2009
    A general solution to the problem of merging custom SSDL with designer-generated SSDL is indeed difficult.On the other hand, I don't see anything to stop the designer from getting the results correct in the first place. In the specific case in your blog post. The designer is clearly aware of the view definition, since it ends up in the DefiningQuery. For simpler views, it should be able to get the primary key information from the provider metadata interfaces. Of course there are views (defined by much more complicated SQL SELECTs) where this is not possible, and for those, it will probably have to give up and do what it currently does. But right now it doesn't even seem to try.Also, I'm not sure who to report this to, but for the past two days posting comments to any msdn blog has silently failed if I'm signed into blogs.msdn.com. I get redirected to the blog home page, instead of seeing my comment posted. If I sign out, on the other hand, posting a comment works just fine.
  • Anonymous
    September 02, 2009
    Craig,I agree the designer should ideally do a better job. When you say the designer is clearly aware of the view generation I think you are wrong there. All the T-SQL in the EntitySet does is reproject columns from the view, it isn't the view definition per say.Per your comment about the commenting problems I'll forward that to someone internally.-Alex
  • Anonymous
    September 02, 2009
    Zeeshan,Yeah you are right. Nice catch, I'll update the post.Alex
  • Anonymous
    September 02, 2009
    OK, that makes sense. Getting the definition, then, would require asking the DB server. Most DB servers can do this, but I don't know if ADO.NET exposes it in a provider-independent way.
  • Anonymous
    October 07, 2009
    Thanks for the detailed description. Unfortunately, one would have to do this whenever the edmx file is regenerated :(.
  • Anonymous
    October 22, 2009
    The comment has been removed
  • Anonymous
    November 26, 2009
    Uhm... sorry but... what happens if you try to delete something?
  • Anonymous
    November 27, 2009
    @HannesNot sure what went wrong for you.  Sorry.@PetruxSo long as the view is an updatable view delete should work just fine. The key is making it updatable.Alex
  • Anonymous
    February 14, 2010
    Will there be any additional support for Views in the .Net 4 release?
  • Anonymous
    February 15, 2010
    @GregI'm not 100% sure.I think there might be some enhancements in the designer so your changes to the SSDL and CSDL are kept in sync when you do an 'Update from Database' but other than that that I don't think there is anything new.Alex
  • Anonymous
    October 25, 2010
    hi how to do crud from user side operations edmx fomat..plz give some link or examplesregardsgopal.s