Querying in Entity Framework

The Entity Framework Query are written using LINQ to Entities. They help us to send the queries to database and return results mapped to our entities. In this tutorial, we will learn how to use the Select clause to select all the rows. Learn to use the ToList method. Use the foreach loop to loop through the rows. How to filter using the Where clause. Sort the results on single or multiple fields using the OrderBy, orderByDescending, ThenBy & ThenByDescending clause. Also we will show you how to write EF Query using both the Method & Query Syntax

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

• Entity framework core database first
• Entity Framework code first with existing database

Download the source code from the Reverse engineering the Code First tutorial,  so that you can get started quickly.

Select All rows

Querying all the data or selecting all rows is the simplest form of the query, that you can write. The following example returns all the Products from the AdventureWorks database.

Query Syntax

We start if the Query by getting the reference to the Context AdventureWorks db = new AdventureWorks()

The Query Syntax starts from a from clause. The from clause must specify a data source & a range variable. In the following example db.Products is our data source. The e is the range variable. We use the range variable to refer to the data source further down the query.

The select e instructs the query to select everything from e i.e from the Products table, which is our data source. The Query syntax must end either with a select or a group clause

using (AdventureWorks db = new AdventureWorks())
{
    var products = from e in db.Products select e;

    foreach (Product p in products)
    {
        Console.WriteLine("{0} {1} ", p.ProductID, p.Name);
    }
}

The return type of the above query is stored in var products. We call this variable as Query Variable. It does not contain the result of the query, but a variable containing the query.

When we iterate through products, the entity framework sends the select query to the database to get the list of products. If you log the database query in Entity Framework to console you will see the query that EF generates. The query is as shown below.

SELECT
    [Extent1].[ProductID] AS [ProductID],
    [Extent1].[Name] AS [Name],
    [Extent1].[ProductNumber] AS [ProductNumber],
    [Extent1].[Color] AS [Color],
    [Extent1].[ProductSubcategoryID] AS [ProductSubcategoryID],
    [Extent1].[Price] AS [Price]
    FROM [dbo].[Product] AS [Extent1]

Method Syntax

The method syntax, in this case, is very simple. All you need to iterate over the contents of DbSet. The var products is nothing but Dbset of Products

using (AdventureWorks db = new AdventureWorks())
{
    var products = db.Products;
    foreach (Product p in products)
    {
        Console.WriteLine("{0} {1} ", p.ProductID, p.Name);
    }
}

The important thing to note here is that Entity Framework does not execute the query against the database until it needs the first result. It sends the query to the database when you actually start reading the data for the first time. In the above example, it happens during the first iteration of the foreach loop. We call this behavior as lazy loading.

If there is no data in the database, then the EF will return the empty collection.

ToList() method

You can also use the ToList() method to get the list of all products. Calling the ToList() method on the query variable forces the query to execute immediately. This behavior is known as eager loading.

Query Syntax

using (AdventureWorks db = new AdventureWorks())
{
    var products = (from e in db.Products
                   select e).ToList();
    foreach (Product p in products)
    {
        Console.WriteLine("{0} {1} ", p.ProductID, p.Name);
    }
}

Method Syntax

using (AdventureWorks db = new AdventureWorks())
{
    var products = db.Products.ToList();

    foreach (Product p in products)
    {
        Console.WriteLine("{0} {1} ", p.ProductID, p.Name);
    }
}

Filtering the Query results using Where

The Where method is used to get the only the required data from the database. The clause is similar to Where clause in SQL Queries.

Query Syntax

using (AdventureWorks db = new AdventureWorks())
{
    var products = from e in db.Products
                   where e.Name.StartsWith("A")
                   select e;

    foreach (Product p in products)
    {
        Console.WriteLine("{0} {1}", p.ProductID, p.Name);
    }
}

Method syntax

using (AdventureWorks db = new AdventureWorks())
{
    var products = db.Products.Where(e => e.Name.StartsWith("A"));

    foreach (Product p in products)
    {
        Console.WriteLine("{0} {1}", p.ProductID, p.Name);
    }
}

Sorting the Query Results Using OrderBy

Query Syntax

Ordering is done using OrderBy method in Query Syntax.

using (AdventureWorks db = new AdventureWorks())
{
    var products = from p in db.Products
            orderby p.Name 
    select p;

    foreach (Product p in products)
    {
        Console.WriteLine("{0} {1}", p.Name, p.ProductNumber);
    } 
}

You can specify the descending clause along with the orderby to specify how the sorting must take place.

using (AdventureWorks db = new AdventureWorks())
{
    var products = from p in db.Products
                   orderby p.Name descending
                   select p;

    foreach (Product p in products)
    {
        Console.WriteLine("{0} {1}", p.Name, p.ProductNumber);
    }
}

Ordering by multiple fields

orderby p.Name descending, p.ProductNumber, p.Color

Method syntax

Use OrderBy…ThenBy clause to sort the result. The default sort order is ascending. To sort in descending order you can use OrderByDescending & ThenByDescending methods. The Clause must always start with the Orderby or OrderByDescending. After that you can have any number of ThenBy / ThenByDescending clauses. These clause takes a lamda expression as shown in the example below.

using (AdventureWorks db = new AdventureWorks())
{
    var products = db.Products.OrderBy(c => c.Name);

    foreach (Product p in products)
    {
        Console.WriteLine("{0} {1}", p.Name, p.ProductNumber);
    }
}

using (AdventureWorks db = new AdventureWorks())
{
    var products = db.Products.OrderByDescending(c => c.Name).ThenBy(c => c.ProductNumber).ThenBy(c => c.Color);

    foreach (Product p in products)
    {
        Console.WriteLine("{0} {1}", p.Name, p.ProductNumber);
    }
}

Summary

We use LINQ to Entities to Query Data in Entity Framework. 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.