Querying in Entity Framework Core

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

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

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.

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.

Best Entity Framework Core Books
The Best EF Core Books, which helps you to get started with EF Core  

From Clause

The Query Syntax must start from a from clause. The from clause must specify

  • The data source: In the above example db.Customer is the data source.
  • The range variable: In the above example, c is 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

Query Syntax

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

Query syntax

Sorting the Results Using OrderBy

Method Syntax

We use OrderByThenBy 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.

Use OrderByDescending to sort in Descending order.

Use ThenBy or ThenByDescending clause to sort the results on multiple fields.

Query Syntax

We use the OrderBy clause to sort the results in Query Syntax.

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.

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.

Leave a Comment

Your email address will not be published.

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

Scroll to Top