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.
Table of Contents
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
1 2 3 4 5 6 7 8 9 10 11 12 | using (AdventureWorks db = new AdventureWorks()) { var products = from e in db.Products select e; foreach (Product p in products) { Console.WriteLine("{0} {1} {2} ", p.ProductID, p.Name , p.ListPrice); } } |
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
1 2 3 4 5 6 | public class customProduct { public string Name { get; set; } public decimal Price { get; set; } } |
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>
1 2 3 4 5 6 7 8 9 10 11 12 | using (AdventureWorks db = new AdventureWorks()) { IEnumerable<customProduct> customProducts = from p in db.Products select new customProduct { Name = p.Name, Price = p.ListPrice }; foreach (customProduct p in customProducts) { Console.WriteLine("{0} {1} ", p.Name, p.Price); } } |
The above query returns the collection of customProduct. Query using the Method Syntax is almost similar and is as shown below
Method Syntax
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | //Projecting to Concrete Type (Method syntax) using (AdventureWorks db = new AdventureWorks()) { IEnumerable<customProduct> products = db.Products. Select(p => new customProduct { Name = p.Name, Price = p.ListPrice }); foreach (customProduct p in products) { Console.WriteLine("{0} {1} ", p.Name, p.Price); } } |
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
1 2 3 4 5 6 7 8 9 10 11 | using (AdventureWorks db = new AdventureWorks()) { var products = from p in db.Products select new { Name = p.Name, Price = p.ListPrice }; foreach (var p in products) { Console.WriteLine("{0} {1} ", p.Name, p.Price); } } |
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
1 2 3 4 5 6 7 8 9 10 11 | using (AdventureWorks db = new AdventureWorks()) { var products = db.Products.Select(p => new { Name=p.Name,Price=p.ListPrice}); foreach (var p in products) { Console.WriteLine("{0} {1} ", p.Name, p.Price); } } |
Summary
The Projection queries improve the efficiency of your application by retrieving only the required data from the database.
