Working with Database Views

There are two ways to use database views.  The first is to when you have an entity class that is backed by a table in the normal way, but you want to load entities through a view rather than directly from the table.  This allows the same entity type to be materialised from multiple different views, such as AllOrders, OverdueOrders or RecentOrders.  The second is to create an entity class which maps the view, just as if the view were a table.  This allows entities to be materialised from views when there is no concrete table, for example when a view joins multiple tables to provide a business-meaningful presentation of denormalised data.

In both cases, the view must contain a column containing unique identifying values.  As with a table, this column must be called Id, or must be mapped using the Identity Column Name option or TableAttribute.IdColumnName.

Loading Entities Through a View

If an entity backs onto a table, but you sometimes want to load instances through a predefined view for performance reasons, you can associate the view with the entity by dragging the view from Server Explorer onto the entity shape.  This adds a property to the unit of work representing a query through that view.  The property’s name is the view name.  The property can be used as follows:

// ProductsByPriceDescending is a view over the Product table 
using (StoreUnitOfWork unitOfWork = _context.CreateUnitOfWork())
{
  var mostExpensiveProducts = unitOfWork.ProductsByPriceDescending .Take(100)
                                        .ToList();
}

You can also query a view using query objects, bypassing the designer and the generated helper property.  To do this, set Query.ViewName.

// ProductsByPriceDescending is a view over the Product table 
using (IUnitOfWork unitOfWork = _context.CreateUnitOfWork())
{
  Query query = new Query
  {
    ViewName = "ProductsByPriceDescending",
    Page = Page.Limit(100)
  };
  var mostExpensiveProducts = unitOfWork.Find<Product>(query); }

We also offer this ability to perform ad-hoc queries using views if you are using LINQ through the WithViewName extension method.

// ProductsByPriceDescending is a view over the Product table 
using (StoreUnitOfWork unitOfWork = _context.CreateUnitOfWork())
{
  var mostExpensiveProducts = unitOfWork.Products
                                          .WithViewName(“ProductsByPriceDescending”) .Take(100)
                                        .ToList();
}

In either case, the view must have the same schema as the table because you are loading it into the same type of entity.  Entities loaded through a view can be modified or deleted in the normal way, with the changes being applied via the underlying table.

Creating an Entity Class to Map a View

If you only have a view – that is, there is no backing table, or you don’t have access to the backing table – then you can load entities from the view by creating an entity class that maps the view.  To do this, drag the view onto the designer surface (that is, the background).  The designer infers an entity class from the view columns just as it would from table columns, and queries for this kind of entity will be passed to the view just as if the entity were backed by a table.

In most cases, you will not be able to save changes to entities that are backed by views, because databases generally do not allow inserts, updates or deletes against views.  You may therefore wish to mark the entity fields as Load Only so that application code can’t erroneously make changes.  However, if your database supports updateable views, and if your view is updateable, then you can save changes against it in the normal way.

Because a view does not have a primary key, if the identity column is not called Id then the designer will not infer the identity column name.  You will need to fix this up manually in this case.