Projection Queries in Entity Framework

We use Projection queries in Entity Framework to create a query that selects from a set of entities in your model but returns results that are of a different type. This is also known as the query projection. We use Projection Queries to create a query that selects specific columns from a database table. Thy can return an anonymous type or a Concrete type.

Projection Query in EF

The Examples in this Tutorial use the AdventureWorks database. You can download it from the link provided. We need to create entity models for this database. This can be done by reverse engineering the database. The following tutorials should help you

What is Projection query

Consider the following query

The above query returns the list of products from the product table. The type (or entity Type) being queried here is Product. This query returns the collection of products, which is same as the type being queried.

What if you want to retrieve only the Product name & Price from the product table?. But the corresponding type consisting of only name & Price does not exist in our model. This is where the Entity Framework Projection Queries are used. It lets you create queries that retrieve only the selected fields and returns the Custom type.

There are two ways you can project the results of a LINQ to Entities query. You can use projection to return a Concrete Type or an Anonymous Type.

Projecting into Concrete Type

You can write a Projection query that returns a Concrete Type. To do that you have to create a Custom Type customProduct as shown below. The customProduct class has the properties, which you want to return from the product table

Change the select clause in the query to map the result to the customProduct and the specify the return type as IEnumerable<customProduct> or IQueryable<customProduct>

The above query returns the collection of customProduct. Query using the Method Syntax is almost similar and is as shown below

Method Syntax

Projecting into Anonymous Types

Anonymous types provide an easy way to create a new type without initializing them. Use the var keyword to create an anonymous type. The compiler infers the type of the property from its usage. Projection queries load the data into this anonymous Type

In the above example, the return type products are an anonymous type. The query returns the collection of products. The returned type contains only two properties Name & Price of the product.

Method Syntax

Summary

The Projection queries improve the efficiency of your application by retrieving only the required data from the database.

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Scroll to Top