Increasing the Performance of Entity Framework with Projection Queries • Developing Software

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

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

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.