Querying in EF Core uses the LINQ to Entities to query the data. In this tutorial, we will learn how to use the Select clause to query to select all the rows. Use of ToList method to execute the query and use the foreach loop to loop through the rows. How to filter using the Where clause. Sort the results using the OrderBy& ThenBy Clause. To sort in descending order using the orderByDescending & ThenByDescending methods. Also, look at how to sort on multiple fields, etc. You can write the EF Core query in two ways. One is Method Syntax & the other one is Query Syntax. In this tutorial, we will show you how to write EF Core Query using both the syntaxes.
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
You can refer this tutorial
- EF Core Console Application
- Entity Framework Core database first Reverse Engineer Existing Database
- Logging in EF Core
Table of Contents
Select All Rows
Selecting all the data is the simplest form of the query you can write in Entity Framework Core (EF Core). The following example returns all the Customers from the Chinook database.
Method Syntax
The method syntax is a series of c# method calls each chained together using Fluent Interface. They take the lambda expressions as the parameters. The syntax looks more like C# statements
1 2 3 4 5 6 7 8 9 10 11 12 | 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); } } |
We start the query by getting a reference to the Context ChinookContext db = new ChinookContext()
The db.Customer return the DbSet property of the Customer entity. The statement var customers = db.Customer; assigns it to the customers local variable. Note that no query is not sent to the database at this time.
In the next line we iterate through the customers using a foreach (var p in customers) loop. As soon as we start the loop, the query is EF core sends the query to the database to retrieve the data. Since there is no Where clause, the query retrieves all the records.
Finally we print the results to the console.
Query Syntax
The following Query written in Query syntax. It also returns all the Customers from the Chinook database.
1 2 3 4 5 6 7 8 9 10 11 12 | using (ChinookContext db = new ChinookContext()) { var customers = from c in db.Customer select c; foreach (var p in customers) { Console.WriteLine("{0} {1} Email ID : {2}", p.FirstName, p.LastName, p.Email); } } |
Here also we start by getting a reference to the Context ChinookContext db = new ChinookContext()
The query from c in db.Customer select c almost looks like SQL Query. The normal SQL queries start with the select keyword. The LINQ Queries starts with the from Clause. The select clause comes last.
From Clause
The Query Syntax must start from a from clause. The from clause must specify
- The data source: In the above example
db.Customeris the data source. - The range variable: In the above example,
cis the range variable. The range variable temporarily holds a value from the data source.
Select Clause
The select clause returns the final result. The LINQ Queries must end with a select clause or a join clause.
Return Type
We store the query in the variable var customers. We call this variable as Query Variable. It stores the query rather than the results of the query. The return type must be of type IEnumerable or IQueryable
As with the method syntax, EF Core does not execute the query until it needs the first result. It sends the query to database, once we start iterating of the foreach loop.
ToList() method
You can also use the ToList() method to get the list of all customers. Calling the ToList() method on the query variable forces the query to execute immediately. We call this behavior as eager loading.
Method Syntax
1 2 3 4 5 6 7 8 9 10 11 12 | using (ChinookContext db = new ChinookContext()) { var customers = db.Customer.ToList(); //Query is executed here 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 | using (ChinookContext db = new ChinookContext()) { var customers = (from c in db.Customer select c).ToList(); //Query is executed here foreach (var p in customers) { Console.WriteLine("{0} {1} Email ID : {2}", p.FirstName, p.LastName, p.Email); } } |
Filtering the Query using Where
We use the Where clause filter the records. It is very similar the where clause in SQL Query.
Method Syntax
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | //Method Syntax using (ChinookContext db = new ChinookContext()) { var customers = db.Customer .Where(f => f.FirstName.StartsWith("A")) .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 | using (ChinookContext db = new ChinookContext()) { var customers = (from c in db.Customer where c.FirstName.StartsWith("A") select c).ToList(); foreach (var p in customers) { Console.WriteLine("{0} {1} Email ID : {2}", p.FirstName, p.LastName, p.Email); } } |
Sorting the Results Using OrderBy
Method Syntax
We use OrderBy…ThenBy clause to sort the result. Orderby takes a lamda expression as shown in the example below. You can sort on multiple fields using the ThenBy method. By default, they sort the results in ascending order. The sort in descending order you can use OrderByDescending & ThenByDescending methods.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | //Method Syntax using (ChinookContext db = new ChinookContext()) { var customers = db.Customer .OrderBy(f => f.FirstName) .ToList(); foreach (var p in customers) { Console.WriteLine("{0} {1} Email ID : {2}", p.FirstName, p.LastName, p.Email); } } |
Use OrderByDescending to sort in Descending order.
1 2 3 4 5 | var customers = db.Customer .OrderByDescending(f => f.FirstName) .ToList(); |
Use ThenBy or ThenByDescending clause to sort the results on multiple fields.
1 2 3 4 5 6 7 | var customers = db.Customer .OrderByDescending(f => f.FirstName) .ThenBy(f => f.LastName) .ThenByDescending(f=> f.Email) .ToList(); |
Query Syntax
We use the OrderBy clause to sort the results in Query Syntax.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | //Query Syntax using (ChinookContext db = new ChinookContext()) { var customers = (from c in db.Customer orderby c.FirstName select c).ToList(); foreach (var p in customers) { Console.WriteLine("{0} {1} Email ID : {2}", p.FirstName, p.LastName, p.Email); } } |
You can specify the descending clause along with the orderby to specify how the sorting must take place. Also add the multiple fields as shown below.
1 2 3 4 5 | var customers = (from c in db.Customer orderby c.FirstName descending , c.LastName select c).ToList(); |
References
Summary
We use LINQ to Entities to Query Data in EF Core. We Learned to use select all rows. Use of ToList method. Filter using the Where clause. Sort the results on multiple fields using the OrderBy & ThenBy clause in ascending Order. Use orderByDescending & ThenByDescending methods for sort in descending order.