.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

  • Death of the Dev Machine?

    Here's a takeaway from this week's Ignite 2020 event: An advanced Azure cloud portends the death of the traditional, high-powered dev machine packed with computing, memory and storage components.

  • COVID-19 Is Ignite 2020's Elephant in the Room: 'Frankly, It Sucks'

    As in all things of our new reality, there was no escaping the drastic changes in routine caused by the COVID-19 pandemic during Microsoft's big Ignite 2020 developer/IT pro conference, this week shifted to an online-only event after drawing tens of thousands of in-person attendees in years past.

  • Visual Studio 2019 v16.8 Preview Update Adds Codespaces

    To coincide with the Microsoft Ignite 2020 IT pro/developer event, the Visual Studio dev team shipped a new update, Visual Studio 2019 v16.8 Preview 3.1, with the main attraction being support for cloud-hosted Codespaces, now in a limited beta.

  • Speed Lines Graphic

    New for Blazor: Azure Static Web Apps Support

    With Blazor taking the .NET web development world by storm, one of the first announcements during Microsoft's Ignite 2020 developer/IT event was its new support in Azure Static Web Apps.

  • Entity Framework Core 5 RC1 Is Feature Complete, Ready for Production

    The first release candidate for Entity Framework 5 -- Microsoft's object-database mapper for .NET -- has shipped with a go live license, ready for production.

Upcoming Events