Leveraging Views in Entity Framework
A typical screen in your user interface probably combines data from several different tables. To handle that, I typically have my back-end processes generate Data Transfer Objects (DTOs) which combine data from several different tables. To load these DTOs with the data they need, I retrieve rows from multiple tables and pull selected columns from each table to populate the properties on my DTOs.
A reader recently pointed out to me that I could drastically simplify that retrieval code by leveraging SQL views. A SQL view pulls together multiple tables (and selected columns from those tables) into a single package. The key feature of a SQL view is that both SQL Server and Entity Framework have a great deal of difficulty in telling the difference between a view and a table.
The major difference between views and tables is that views are read-only: you can't use them to add, update, or delete rows. However, that's less of a problem than you might think, especially in Web based applications.
In an ASP.NET MVC application, for example, my code that retrieves the data to build the DTO will go in a Get method; The code to do the deletes and updates will go in Delete or Post method. While I need to retrieve data from all of the tables to build my DTO often, in my update/delete methods, I only need to work with a subset of those tables (none of the lookup tables, for example, and only those tables that the user changed). In other words, my Get and Delete/Post methods look very different, anyway.
It makes sense, therefore, to create views in SQL Server that correspond to the DTOs I will return from my back-end server. With those views in place, in my Get methods I can retrieve rows through the view with a single trip to the database. This strategy also allows SQL Server do some optimization on the retrieval which isn't possible if I retrieve the tables individually.
In Entity Framework you add a View to your DbContext object with the same code you use to add a table. This example adds a view named MySQLView to a DbContext:
Public Class AdventureWorksLTEntities
Inherits DbContext
Public Property MySQLView As DbSet(of MySQLView)
End Class
The code to process a View is identical to the code you'd use to process a Table. You can even update the properties on View, but your code will throw an exception when you call the SaveChanges method (the message is distinctly unhelpful: It says that you can't change the property because it's part of the entity's key).
Posted by Peter Vogel on 02/25/2015