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