How to create an updateable view with ADO Entity Framework and with LINQ to SQL

Article
10/03/2008

Creating an update-able view with ADO Entity Framework (EF) or LINQ to SQL (L2S) is a fairly advanced topic and not directly associated with Dynamic Data. At the end of the article I have a sample console application to verify the modified L2S data model allows updates on a view. To create an update-able view, you must modify the wizard (or other tool) generated XML file (data model). Each time you generate a new data model (for example when the schema changes), you will need to reapply these steps.

ADO Entity Framework (EF) makes views Read Only via the <DefiningQuery> element. You make the data model view update-able by removing the <DefiningQuery> element and making a few minor changes. Note the example below is a very simple view on one table and includes the primary key.

This is what I did to make an update-able view for the AdventureWorksLT DB

CREATE

VIEW [SalesLT].[vAddr]
AS
SELECT AddressID,[AddressLine1],[City],[StateProvince],[CountryRegion],[PostalCode]
FROM [AdventureWorksLT2008].[SalesLT].[Address]

The next line shows this view is update-able (at least from T-SQL)

UPDATE vAddr SET PostalCode = '54321'
WHERE addressID > 11382 AND
StateProvince = 'WA'

(18 row(s) affected)

Edit the EF SSDL, comment out the <DefiningQuery> , remove store: prefix from Schema="SalesLT" and remove store:Name="vAddr" . The commented/changed code below

EntitySet Name="Address" EntityType="AdventureWorksLT2008Model.Store.Address" store:Type="Tables" Schema="SalesLT" />
<EntitySet Name="vAddr" EntityType="AdventureWorksLT2008Model.Store.vAddr" store:Type="Views" Schema="SalesLT" />

<!--

<EntitySet Name="vAddr" EntityType="AdventureWorksLT2008Model.Store.vAddr" store:Type="Views" store:Schema="SalesLT" store:Name="vAddr"> -->

EntityContainer>

LINQ to SQL is the simplest.

Using the view above,

Simply change the following line in the wizard generated code to use AutoSync = AutoSync.OnInsert in lieu of AutoSync=AutoSync.Always on the AddressID property.

// [Column(Storage="_AddressID", AutoSync=AutoSync.Always, DbType="Int NOT NULL IDENTITY", IsPrimaryKey=true, IsDbGenerated=true)]
[Column(Storage = "_AddressID", AutoSync = AutoSync.OnInsert, DbType = "Int NOT NULL IDENTITY", IsPrimaryKey = true, IsDbGenerated = true)]
public int AddressID

The following example shows how to test the view from a console application.

 using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Linq;
using System.Data.Linq.Mapping;

namespace updateableView {

        public class T {
            public LTDataContext db;
            //    readonly string con = "Data Source=bing0;Initial Catalog=AdventureWorksLT2008;Integrated Security=True";
            readonly string con = "Data Source=bing0;Initial Catalog=AdventureWorksLT2008;" +
                                   "Persist Security Info=True;User ID=sa;Password=*(IU89iu";

            public T() {
                db = new LTDataContext(con);
            }

            public void addAddr(string city) {

                vAddr adr = new vAddr();
                adr.AddressLine1 = "1234 N St.";
                adr.City = city;
                adr.PostalCode = "99966";
                adr.StateProvince = "Mt";
                adr.CountryRegion = "None";

                db.vAddrs.InsertOnSubmit(adr);
                db.SubmitChanges();
            }

            public void tq(string city) {

                Table<vAddr> addr = db.GetTable<vAddr>();
                var q = from c in addr
                        where c.City == city
                        select c;

                foreach (var cst in q)
                    Console.WriteLine("id = {0}, City = {1}", cst.AddressID, cst.City);
            }
        }

        class Program {
            static void Main(string[] args) {
                T tdb = new T();
                string city = "GF";
                tdb.addAddr(city);
                tdb.tq(city);

            }
        }
    }

Comments

Anonymous
February 18, 2009
Please post corrections/new submissions to the Dynamic Data Forum . Put FAQ Submission/Correction in
Anonymous
March 23, 2009
Whilst at Q-Con a few weeks ago someone asked me about how to map two database tables to one entity in
Anonymous
June 03, 2009
Hi Rick, I manually created an association (one of the class in the association points to a view) in ado entity framework. And I am scaffolding the entire model. Everything works except when I go into the insert of the parent table that refers to a many-relationship of the child (which is a view) and I don't see a dropdown. Is this because views are not editable? But I am not trying to edit it, I simply want a dropdown to refer to a child data. I tried your method of changing the EDMX file to think the view is a table but that didn't work either.
Anonymous
October 26, 2010
hey, Can you please explain the below part bit more: Edit the EF SSDL, comment out the <DefiningQuery> , remove store: prefix from Schema="SalesLT" and remove store:Name="vAddr" . The commented/changed code below <EntitySet Name="Address" EntityType="AdventureWorksLT2008Model.Store.Address" store:Type="Tables" Schema="SalesLT" /> <EntitySet Name="vAddr" EntityType="AdventureWorksLT2008Model.Store.vAddr" store:Type="Views" Schema="SalesLT" /> As such, the above statements seem to be very confusing.
Anonymous
June 14, 2012
The solution for Entity Framework has a BIG flaw, once you update your model (i.e. edmx) for any reasons, designer will update the view metadata and all of the manual changes will be gone.
Anonymous
August 25, 2013
The comment has been removed

Share via

How to create an updateable view with ADO Entity Framework and with LINQ to SQL

LINQ to SQL is the simplest.

Comments

Additional resources