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

            }
        }
    }

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

LINQ to SQL is the simplest.

Additional resources