This tutorial is about Projection queries in EF Core. We use them to create a query that selects specific columns from a set of entities. The projection queries 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. The Projection Queries results can return an anonymous type or a Concrete type.
Database:
The Database for this tutorial is taken from the chinook database.Source Code:
The source code of this project available in GitHub. It also contains the script of the database
Table of Contents
Projection Queries in EF Core
What is Projection query
Consider the following query
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | public void WithoutProjection() { using (ChinookContext db = new ChinookContext()) { var customers = db.Customer; foreach (var p in customers) { Console.WriteLine("{0} {1} Email ID : {2}", p.FirstName, p.LastName, p.Email); } } Console.WriteLine("Press any key to continue"); Console.ReadKey(); } |
SQL Query
1 2 3 4 5 | //Query SELECT [c].[CustomerId], [c].[Address], [c].[City], [c].[Company], [c].[Country], [c].[Email], [c].[Fax], [c].[FirstName], [c].[LastName], [c].[Phone], [c].[PostalCode], [c].[State], [c].[SupportRepId] FROM [Customer] AS [c] |
The above query returns the list of customers from the customers table. The type (or entity Type) being queried here is Customer. This query returns the collection of Customers, which is the same as that of the type being queried.
Now, look at the query that EF Core sends to the database. It includes all the columns from the Customers table. While in our code we use only FirstName, LastName & Email. We do not need those extra columns. Depending on the size of the customer’s table, we may be moving a large amount of unnecessary data. It also comes with performance costs like CPU usage, I/O, network bandwidth, etc.
Hence it makes sense only to retrieve FirstName, LastName & Email. But we do not have a type which matches them. This is where EF Core Projection Query comes into play. The EF Core lets us create queries that retrieve only the selected fields and returns the Custom type.
There are two ways you can project the results of an EF Core Query. You can use projection to return a Concrete Type or an Anonymous Type.
Projecting into Concrete Type
To Project into a Concrete type, we first need to define a type with all the fields we want in the result as shown below.
1 2 3 4 5 6 7 8 | public class customerModel { public string FirstName { get; set; } public string LastName { get; set; } public string Email { get; set; } } |
Method Syntax
We use the Select method and a lambda expression to map the fields to a new customerModel. As you can see from the code below the final query contains only the required fields.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | public void ProjectToConcreteType() { //Method Syntax using (ChinookContext db = new ChinookContext()) { List<customerModel> Customers = db.Customer .Select(p => new customerModel { FirstName = p.FirstName, LastName = p.LastName, Email= p.Email }) .ToList(); foreach (var p in Customers) { Console.WriteLine("{0} {1} Email ID : {2}", p.FirstName, p.LastName, p.Email); } } } |
SQL Query
1 2 3 4 5 | //Query SELECT [c].[FirstName], [c].[LastName], [c].[Email] FROM [Customer] AS [c] |
Query Syntax
The following is the same query but in Query Syntax.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | public void ProjectToConcreteType() { //Query Syntax using (ChinookContext db = new ChinookContext()) { List<customerModel> Customers = (from p in db.Customer select new customerModel { FirstName = p.FirstName, LastName = p.LastName, Email = p.Email }) .ToList(); foreach (var p in Customers) { Console.WriteLine("{0} {1} Email ID : {2}", p.FirstName, p.LastName, p.Email); } } } |
Projecting into Anonymous Types
Anonymous type is a C# feature that enables us to create variables without declaring their type. We use the var keyword to declare the Anonymous type. The compiler infers the properties of the type by its usage. They provide an easy way to create a new type without initializing them.
The Projection queries can load the data into the Anonymous Type as shown below.
Method Syntax
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | public void ProjectToAnonymousType() { //Method Syntax using (ChinookContext db = new ChinookContext()) { var Customers = db.Customer. Select(p => new { FirstName = p.FirstName, LastName = p.LastName, Email = p.Email }) .ToList(); foreach (var p in Customers) { Console.WriteLine("{0} {1} Email ID : {2}", p.FirstName, p.LastName, p.Email); } } } |
Query Syntax
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | public void ProjectToAnonymousType() { //Query Syntax using (ChinookContext db = new ChinookContext()) { //List<customerModel> var Customers = (from p in db.Customer select new { FirstName = p.FirstName, LastName = p.LastName, Email = p.Email }) .ToList(); foreach (var p in Customers) { Console.WriteLine("{0} {1} Email ID : {2}", p.FirstName, p.LastName, p.Email); } } } |
References
Summary
The Projection queries improve the efficiency of your application by retrieving only the required data from the database. We can project to a concrete type or to an anonymous type
This page would really benefit from a much more complex example of projection where the model class has multiple levels of child records. It also should clearly discuss (or mention) the considerations to decide between trying to project to your existing model class vs an anonymous class. As a big bonus, can projecting a list of anonymous classes be mapped to a list of the model class?