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" 
  [Employees].[ID] AS [ID],
  [Employees].[Firstname] AS [Firstname],
  [Employees].[Surname] AS [Surname],
  [Employees].[Email] AS [Email]
  FROM [dbo].[Employees] AS [Employees]

In order to treat it as a table replace that with this:

<EntitySet Name="Employees" 
           Schema="dbo" />

Now you can perform every CRUD operation.

Pretty easy if you ask me.

Comments (16)

  1. Barbaros says:

    Thank you so much, I was wondering about how to get through this all week 🙂

  2. Alex D James says:

    Barbaros, you are welcome, I’m just glad this was timely for you!

  3. Craig Stuntz says:

    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)"


  4. Alex D James says:


    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!


  5. Zeeshan Hirani says:

    I thought not every column is marked as primary key.In fact every non nullable column is marked as primary key.

  6. Craig Stuntz says:

    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.

  7. Alex D James says:


    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.


  8. Alex D James says:


    Yeah you are right. Nice catch, I’ll update the post.


  9. Craig Stuntz says:

    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.

  10. Fayssal El Moufatich says:

    Thanks for the detailed description. Unfortunately, one would have to do this whenever the edmx file is regenerated :(.

  11. that trick didnt it for me

    {"__type":"DataServiceResult:DomainServices","IsDomainServiceException":false,"ErrorCode":500,"ErrorMessage":"Fehler beim Ausführen der Befehlsdefinition. Details finden Sie in der internen Ausnahme.","StackTrace":" bei System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)rn bei System.Data.Objects.Internal.ObjectQueryExecutionPlan.Execute[TResultType](ObjectContext context, ObjectParameterCollection parameterValues)rn bei System.Data.Objects.ObjectQuery1.GetResults(Nullable1 forMergeOption)rn bei System.Data.Objects.ObjectQuery1.System.Collections.Generic.IEnumerableu003cTu003e.GetEnumerator()rn bei System.Data.Objects.ObjectQuery1.GetEnumeratorInternal()rn bei System.Data.Objects.ObjectQuery.System.Collections.IEnumerable.GetEnumerator()rn bei System.Web.Ria.DataServiceQueryRequest.FlattenGraph(IEnumerable list, List1 result, HashSet1 visited, DomainServiceDescription domainServiceDescription)rn bei System.Web.Ria.DataServiceQueryRequest.Invoke(DomainService domainService)rn bei System.Web.Ria.DataService.System.Web.IHttpHandler.ProcessRequest(HttpContext context)"}

  12. petrux says:

    Uhm… sorry but… what happens if you try to delete something?

  13. Alex D James says:


    Not sure what went wrong for you.  Sorry.


    So long as the view is an updatable view delete should work just fine. The key is making it updatable.


  14. Greg Hollywood says:

    Will there be any additional support for Views in the .Net 4 release?

  15. Alex D James says:


    I’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.


  16. GOPAL says:

    hi how to do crud from user side operations edmx fomat..

    plz give some link or examples