.NET Tips and Tricks

Blog archive

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 at 2:20 PM


comments powered by Disqus

Featured

  • Java on Visual Studio Code Going Cloud Native

    Cloud-native development figures prominently in a new roadmap published by Microsoft's Java on Visual Studio Code dev team.

  • Speed Lines Graphic

    Quantum-Inspired Annealing Using C# or Python

    Dr. James McCaffrey of Microsoft Research explains a new idea that slightly modifies standard simulated annealing by borrowing ideas from quantum mechanics.

  • Visual Studio 2022 v17.1 Preview 3 Improves Web Tools

    Microsoft quietly shipped Visual Studio 2022 v17.1 Preview 3 with enhancements to web tools.

  • Progress Telerik Adds 20-Plus Components for Blazor, .NET MAUI and WinUI

    The R1 2022 release of Progress Telerik development tooling adds more than 20 new components to the Blazor, .NET MAUI and WinUI offerings.

Upcoming Events