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


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”> –>
<!–
<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);

}
}
}

Comments (6)

  1. Please post corrections/new submissions to the Dynamic Data Forum . Put FAQ Submission/Correction in

  2. Whilst at Q-Con a few weeks ago someone asked me about how to map two database tables to one entity in

  3. splusq says:

    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.

  4. Hari says:

    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.

  5. Evan says:

    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.

  6. Dave says:

    Evan is correct. This is a huge flaw. Also, generate database wants to create a new table same as view and this should be optional at best and ideally, should remember the actual view text if database first was used. I use this technique to check my prod database is in sync with my test database by creating a new db from the edmx and then comparing them.