• Home
  • Tutorials
  • Development Tools
  • Contact Us

Developing Software

Mastering Software Craftsmanship

Increasing the Performance of Entity Framework with Projection Queries

23rd August 2014 by @developingsoft

If you’re not using Entity Framework projection queries, your application is certain to run like a big fat slimy slug! In this post, we will look at what projection queries are, how to write them, and when you should use them.

What is a projection query?

Projection queries improve the efficiency of your application, by only retrieving specific fields from your database.

A projection query lets you select only the data that is required from your models. It is done, by loading the data into a custom or anonymous type.

This improves the performance of your application, especially when the fields you are not retrieving contain large amounts of data.

A standard Entity Framework query

Lets say, you have a Post model that contains the fields: Id, Title, and Body. If you wanted to return a list of posts, a standard query would return all the fields. The following example shows this in action:

// returns a list of Posts with all the fields
var posts = context.Posts.ToList();

SQL generated by query
SQL Generated by query

As you can see, the above way of querying the database is not ideal. You wouldn’t want to retrieve the body field, when you are only listing post titles.

Projection query to the rescue

Now lets say, you only wanted to get a list of posts that contain Id and Title fields. This is where you should use a projection query.

The following example shows a projection query, where the data is loaded into a new custom type called PostRow:

// first we create a new class to contain less fields
public class PostRow
{
    public int Id { get; set; }
    public string Title { get; set; }
}

// returns posts with only the Id and Title
var posts = context.Posts.Select(p => new PostRow
{
    Id = p.Id,
    Title = p.Title
}).ToList();

SQL generated by projection query
SQL Generated by projection query

The projection query above excludes the Body field. This will make your application much quicker.

But what if you don’t want to create a custom type?

You might find that you don’t need to create a new custom type to do your projection query. If that is the case, you can change the query above so that the projection is done into an anonymous type. This would look like this:

// returns posts with only the Id and Title
var posts = context.Posts.Select(p => new
{
    Id = p.Id,
    Title = p.Title
}).ToList();

The only difference with the code above, is the PostRow has been removed. The posts variable will contain a list of anonymous objects that contain an Id and a Title.

You can then run through the anonymous list doing whatever manipulations you want.

When should you use projection queries?

As you can see, projection queries are not that hard to write. Yes, its more code than the standard query, but the performance gains are worth it.

The best time to use projection queries, is any time that you need to load certain fields from an entity. An example that comes to mind, is when you want to display a list of blog posts that you can edit.

Final thoughts

If your application is running slow, and you haven’t used projection queries. Give them a try, they will, increase your app performance.

Also, make sure you turn off lazy loading, it’s a silent killer.

Further Reading

More detailed discussion on Entity Framework can be found in the following publications:

  • Lerman, J. 2010. Programming Entity Framework. 2nd ed. O’Reilly Media.
  • Lerman, J. and Miller, R. 2011. Programming Entity Framework: Code First. O’Reilly Media.
  • Lerman, J. and Miller, R. 2012. Programming Entity Framework: DbContext. O’Reilly Media.
Share this on:

Filed Under: Tutorials Tagged With: C#, Entity Framework

Search

Advertisement

Newsletter

Subscribe now to receive practical tips on how to become a better software developer.

Free - No Spam - 100% Email Privacy

Featured Posts

Abstract Factory Pattern: C# Example Using the Unity Game Engine

23 Software Design Patterns That Will Make You a More Effective Programmer

How to Deploy an ASP.NET Core Website to Ubuntu with Git

How to Run an ASP.NET Core Website in Production on Ubuntu Linux

How to Install the Edimax Wireless nano USB Adapter on Windows IoT Core for Raspberry Pi

How to Convert a Post Title into a Friendly URL (Slug) in C#

How to Convert Markdown to HTML in ASP.NET Core

How to Send an E-Mail with ASP.NET Core and Mailgun

How to Generate a Sitemap in ASP.NET MVC and ASP.NET Core

How to Create an MD5 Hash of a String in C# and Displaying a Gravatar Image

© 2014–2023 Developing SoftwareTerms • Privacy