.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

  • What's New in Visual Studio 2019 v16.5 Preview 2

    The second preview of Visual Studio 2019 v16.5 has arrived with improvements across the flagship IDE, including the core experience and different development areas such as C++, Python, web, mobile and so on.

  • C# Shows Strong in Tech Skills Reports

    Microsoft's C# programming language continues to show strong in tech industry skills reports, with the most recent examples coming from a skills testing company and a training company.

  • Color Shards

    Sharing Data and Splitting Components in Blazor

    ASP.NET Core Version 3.1 has at least two major changes that you'll want to take advantage of. Well, Peter thinks you will. Depending on your background, your response to one of them may be a resounding “meh.”

  • Architecture Small Graphic

    Microsoft Ships Preview SDK, Guidance for New Dual-Screen Mobile Era

    Microsoft announced a new SDK and developer guidance for dealing with the new dual-screen mobile era, ushered in by the advent of ultra-portable devices such as the Surface Duo.

  • How to Create a Machine Learning Decision Tree Classifier Using C#

    After earlier explaining how to compute disorder and split data in his exploration of machine learning decision tree classifiers, resident data scientist Dr. James McCaffrey of Microsoft Research now shows how to use the splitting and disorder code to create a working decision tree classifier.

.NET Insight

Sign up for our newsletter.

Terms and Privacy Policy consent

I agree to this site's Privacy Policy.

Upcoming Events