.NET Tips and Tricks

Blog archive

Restricting Columns Retrieved in Entity Framework

A couple of months ago, I wrote a column on how to avoid downloading columns in a table that has hundreds of columns or columns containing large objects (or, at least, only downloading those columns when you want them). But that solution only makes sense when getting the columns you want is something that you'll be doing frequently.

If, on the other hand, you have exactly one place in your application where all you want to get is, for example, the Customer's first and last names then there's a simpler solution: Just define a class that has the columns you want.

Two caveats: First, you can't do updates through the objects you've retrieved using this technique. Second, don't expect to get a huge performance gain from this unless you're avoiding retrieving many other columns or the columns you're avoiding are blob columns.

As an example, to get the Customer's first and last name columns I'd begin by defining a class, outside of my Entity Framework model, to hold just those columns:

Public Class CustFirstLastName
   Public Property FirstName As String
   Public Property LastName As String
End Class

Now, I write a LINQ query to retrieve just those two columns by instantiating the class in my LINQ statement's Select clause and setting its properties with values retrieved through Entity Framework. This code assumes that my DbContext object (in the db variable in this example) has a collection called Customers:

Dim lastFirstNames = From c In db.Customers
                     Select New CustFirstLastName With {
							    .FirstName = c.FirstName,
							    .LastName = c.LastName

The SQL generated by Entity Framework to get the data from the database will just grab the FirstName and LastName columns because that's all that's been used in the Select statement.

If you're new to Entity Framework, you probably consider this obvious -- that's what EF should do. But, in the early days of EF, this wasn't the behavior you got: EF always retrieved all the rows specified in the entity class (in this case, whatever class makes up that Customers collection). EF's gotten smarter since then and you can take advantage of it.

But, as I said, my CustFirstLastName class is not part of my entity model. If I make changes to the CustFirstLastName object's properties and call my DbContext object's SaveChanges method those changes will not be transferred back to the database. To update the database, I need to make my changes to whatever objects are in the Customers collection in my sample code.

Posted by Peter Vogel on 12/02/2014 at 1:51 PM

comments powered by Disqus


  • Black White Wave IMage

    Neural Regression Using PyTorch: Training

    The goal of a regression problem is to predict a single numeric value, for example, predicting the annual revenue of a new restaurant based on variables such as menu prices, number of tables, location and so on.

  • Microsoft Ships Visual Studio 2019 v16.9 Servicing Baseline Release

    Microsoft is urging enterprises and professional coders to standardize on the new Visual Studio 2019 v16.9, a servicing baseline release that's guaranteed to receive official support for an extended period.

  • Microsoft Extends Low-Code Push with Power Fx Programming Language

    "Using formulas that are already familiar to hundreds of millions of users, Power Fx allows a broad range of people to bring skills they already know to low code solutions."

  • Microsoft's Tools to Fight Solorigate Attack Are Now Open Source

    Microsoft open sourced homegrown tools it used to check its systems for code related to the recent massive breach of supply chains that the company has named Solorigate.

Upcoming Events