EF Core Query

Summary: in this tutorial, you will learn about EF Core query flow and how to query data from the database.

EF Core Query flow

EF Core uses LINQ to query data from the database. Here’s the query flow when you execute a query:

First, you execute a query:

context.Departments.ToList()Code language: CSS (css)

Second, EF Core reads model (Department class), works with the data provider (SQL Server in this example) to construct a corresponding SQL statement:

SELECT [d].[Id], [d].[Name]
FROM [Departments] AS [d]Code language: CSS (css)

Since we use the SQL Server data provider, EF Core will generate the T-SQL which is a SQL dialect of SQL Server.

Third, EF Core sends the generated SQL statement to the database for execution.

Fourth, EF Core receives the tabular results back from the database server.

Fifth, EF Core materializes the results as Department objects

Finally, EF Core adds tracking details to the DbContext instance.

EF Core uses both LINQ methods and operators to query data. For example, the following uses the LINQ method to get all departments:

var departments = context.Departments.ToList();Code language: JavaScript (javascript)

and the following example uses operators:

var departments = (from d
                   in context.Departments
                   select d)
                   .ToList();Code language: JavaScript (javascript)

Queries a composable

Queries are composable, allowing you to break a complex query into parts. For example, you can do this break the following query:

var departments = context.Departments.ToList();Code language: JavaScript (javascript)

into the following:

var query = context.Departments;
var departments = query.ToList();Code language: JavaScript (javascript)

Query enumeration

The following query returns all departments from the Departments table in one go:

var departments = context.Departments.ToList();Code language: JavaScript (javascript)

It means that EF Core retrieves the data and closes the database connection immediately.

However, the following only gets the data when for loop executes and closes the database connection until the last row is fetched:

using HR.Data;
using static System.Console;


var context = new HRContext();
var departments = context.Departments;

foreach (var d in departments)
{
    WriteLine($"{d.Id} {d.Name}");
}
Code language: PHP (php)

If the enumeration is fast, then you’ll be fine. However, if the enumeration is taking time, the database connection will stay open until the last row is fetched. It’ll create a performance problem.

Therefore, it is a good practice to get the results first using the ToList() method.

Filtering data (WHERE)

By default, EF Core protects you from SQL injection by parameterizing queries. The following example illustrates how to query departments whose name is Sales:

using HR.Data;
using static System.Console;


var context = new HRContext();

var departments = context.Departments
                        .Where(d => d.Name == "Sales")
                        .ToList();

foreach (var d in departments)
{
    WriteLine($"{d.Id} {d.Name}");
}
Code language: JavaScript (javascript)

Here is the generated SQL that EF Core sends to the database:

SELECT [d].[Id], [d].[Name]
      FROM [Departments] AS [d]
      WHERE [d].[Name] = N'Sales'Code language: PHP (php)

When you use hard-coded values like the literal string “Sales”, EF Core doesn’t parameterize the query, which is not secure and vulnerable to SQL injection.

However, when you use a variable and pass it to the query, EF Core will always create parameters for the value. For example:

using HR.Data;
using static System.Console;


var context = new HRContext();

var name = "Sales";
var departments = context.Departments
                        .Where(d => d.Name == name)
                        .ToList();

foreach (var d in departments)
{
    WriteLine($"{d.Id} {d.Name}");
}Code language: JavaScript (javascript)

In this example, we use the name variable in the query instead of using the literal values. If you run the program, you’ll see that EF Core parameterizes the query as follows:

Executed DbCommand (64ms) [Parameters=[@__name_0='Sales' (Size = 4000)], CommandType='Text', CommandTimeout='30']
      SELECT [d].[Id], [d].[Name]
      FROM [Departments] AS [d]
      WHERE [d].[Name] = @__name_0Code language: PHP (php)

The output shows that EFCore creates a parameter called @__name_0 and passes it to the query. Therefore, you should never pass the hard-coded values to the query to prevent SQL injections.

Filtering partial texts (LIKE)

SQL has the LIKE operator that allows you to match text using wildcard characters %. For example, “%abc%” will match any text that contains the string “abc”.

To form the LIKE operator in EF Core, you can use the Like() method:

 EF.Functions.Like(property, %keyword%)Code language: CSS (css)

For example, the following uses the Like() method to find departments whose names contain the letter i:

using System.Data;
using static System.Console;
using Microsoft.EntityFrameworkCore;
using HR.Data;


var context = new HRContext();

var keyword = "i";
var departments = context.Departments
                        .Where(d => EF.Functions.Like(d.Name, $"%{keyword}%"))
                        .ToList();

foreach (var d in departments)
{
    WriteLine($"{d.Id} {d.Name}");
}Code language: JavaScript (javascript)

Output:

2 Marketing
3 Logistics
5 IT

If you enable the log, you’ll see the following SQL statement:

Executed DbCommand(35ms) [Parameters=[@__Format_1='%i%' (Size = 4000)], CommandType = 'Text', CommandTimeout = '30']
SELECT[d].[Id], [d].[Name]
FROM[Departments] AS[d]
WHERE[d].[Name] LIKE @__Format_1Code language: PHP (php)

Finding an entity by its key value

To find an Entity by its key value, you use the DbSet.Find(key). Not that the Find() is DbSet() method that executes immediately once you call it.

The following example uses the Find() method to get the department by id:

using static System.Console;
using HR.Data;


var context = new HRContext();
var department = context.Departments.Find(1);

WriteLine($"{department?.Id} {department?.Name}");Code language: JavaScript (javascript)