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]
CREATE
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"> --><!--<DefiningQuery>SELECT [vAddr].[AddressID] AS [AddressID], [vAddr].[AddressLine1] AS [AddressLine1], [vAddr].[City] AS [City], [vAddr].[StateProvince] AS [StateProvince], [vAddr].[CountryRegion] AS [CountryRegion], [vAddr].[PostalCode] AS [PostalCode] FROM [SalesLT].[vAddr] AS [vAddr]</DefiningQuery>--> <!--</EntitySet>--> </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); } } }
// [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); } } }