.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


comments powered by Disqus

Featured

  • Vibe Coding Pioneer Advises 'Tight Leash' to Rein In AI BS

    Andrej Karpathy, who started the whole "vibe coding" thing to describe AI-drive software development, is now warning developers to keep tight control of their "new over-eager junior intern savant with encyclopedic knowledge of software, but who also bull* you all the time, has an over-abundance of courage and shows little to no taste for good code."

  • Microsoft Unifies Cloud and AI Development with .NET Aspire and AI Template Updates

    Microsoft announced the availability of .NET Aspire 9.2 and the second preview of the .NET AI Chat Web App template, highlighting new capabilities that bring cloud-native orchestration and AI integration closer together in the .NET ecosystem.

  • Hands On with Microsoft's AI Playground in a Windows App

    Microsoft reintroduced the AI Dev Gallery, a Windows application that serves as a comprehensive playground for AI development using .NET. It simplifies AI development with .NET through interactive samples, easy model downloads, and exportable source code.

  • Vibe Coding Advances in Visual Studio 2022 Previews

    Preview 3 features next edit suggestions -- or tab, tab, tab coding -- following previous previews of GPT-4o code completions and assorted Copilot enhancements.

  • Mastering Modern JavaScript

    Master the latest features of modern JavaScript in a hands-on workshop covering clean code techniques, async programming, templating, and full-stack development with Node.js.

Subscribe on YouTube

Upcoming Training Events

0 AM
Visual Studio Live! San Diego
September 8-12, 2025
Live! 360 Orlando
November 16-21, 2025
Cloud & Containers Live! Orlando
November 16-21, 2025
Data Platform Live! Orlando
November 16-21, 2025
Visual Studio Live! Orlando
November 16-21, 2025