Many development teams and database administrators use views to create an abstraction layer on top of a physical data model. In this article I'll show you how to build an Entity Framework 4.0 (EF4) model upon such a set of views. I'll create a couple of views in SQL Server -some of them indexed-, import them in a model using the Visual Studio designer, decorate them with different types of associations (foreign key and mapped table), and finally attack the model with some Linq queries. This is how the model looks like:

Creating the views

Views that are directly consumed by your application should be stable. That's why I prefer such views to be declared with the SCHEMABINDING option:

The SCHEMABINDING option protects a view against modifications to the schema of the underlying tables, at least modifications that would invalidate that view. E.g. it becomes impossible to drop a column on which the view relies:

Optional step: indexing the views

Sometimes it makes sense to persist the view on disk and let SQL Server make sure that its content remains in sync with the underlying tables. This is very useful for complex views (with lots of joins and calculations) on stable tables. All we need to do is creating a clustered index on the view:

Importing the views

You import a view into an entity model just like you import a table. But views -even indexed ones- can not have primary or foreign keys in the database, so there's no metadata to import. The visual designer overcompensates this by inferring a key composed of all non-nullable columns. This is not a good idea: the first thing you need to do is define the primary key of the view:

before	after

Now do the same steps for the Address view:

Defining 1-to-1 or 1-to-many relationships

In a table, you would express 1-to-1 or 1-to-many relationships by creating a foreign key relationship. In an entity model, you can do the same with views. For starters, define a new association between two views. The model looks good now, and IntelliSense will help you while building Linq queries against it. However, your missing an important ingredient: the physical model doesn't know to solve the association: 

When creating the association, don't forget to check the 'add foreign key properties' box:

If necessary, you can refine the data store binding by clicking on the ellipsis next to 'referential constraint' in the properties window:

After that, you need to remove the view from the designer. The view is used as entity ànd as association, EF4 does not like that:

Defining many-to-many relationships

Many-to-many relations are generally implemented through an intermediate table. A many-to-many relationship between two views is built exactly the same way. The AdventureWorks2008 database has an intermediate table between Address and BusinesEntity (= Person): BusinessEntityAddress. Unfortunately we can't use this table to carry the association. Strangely enough the entity framework requires that all its physical (SSDL) primary key fields should be mapped. Using that table as the glue between Persons and Addresses yields the following error:

As a work around, you could define the link table as a view:

Then add it to the entity model, and map the association to it. The foreign key checkbox will be disabled for many-to-many associations:

Both types of associations were created using the designer. I didn't need to manually tweak the SSDL-part of the model. So when we upgrade it from the database they will remain intact.

Querying the views

For Linq it doesn't matter where the data comes from, so you use the views like you would use a table:

This gives the following result:

If EF4 performance matters to you, you might want to (re-)read this article.

Source

Here's the sample project, all SQL queries are included: U2UConsult.EF4.Views.Sample.zip (14,33 kb)

Enjoy!