Reading Queries in Entity Framework

When working with databases, besides how we structure the data, the most important part is how we retrieve and manipulate information.

For this, we have CRUD operations, an acronym representing the four main operations we perform on databases:

In this article, we will focus on READ operations, which allow us to retrieve stored information from the database to display it in the application.

Methods for Reading Multiple Elements

Entity Framework offers several ways to perform READ queries, from basic to more advanced ones. These are the most common:

Query All Records with ToList

The simplest way to retrieve data is by using the ToList method, which returns all records from a table.

var students = context.Students.ToList();

foreach (var student in students)
{
	Console.WriteLine($"Id: {student.Id}, Name: {student.Name}, Age: {student.Age}");
}

In this example, EF Core executes a SQL query equivalent to:

SELECT * FROM Students;

Filter Data with Where

The Where method is one of the most used in EF, as it allows us to filter records based on a specific condition.

var adultStudents = context.Students.Where(e => e.Age > 18).ToList();
    
foreach (var student in adultStudents)
{
	Console.WriteLine($"Name: {student.Name}, Age: {student.Age}");
}

Generated SQL query:

SELECT * FROM Students WHERE Age > 18;

Methods for Reading a Single Element

Find a Record with FirstOrDefault

The FirstOrDefault method is similar to Where, but instead of returning a collection of records, it returns the first record that meets the specified condition. If no record meeting the condition is found, it returns null.

var student = context.Students.FirstOrDefault(e => e.Name == "Juan");
Console.WriteLine($"Id: {student.Id}, Name: {student.Name}");

Generated SQL query:

SELECT TOP 1 * FROM Students WHERE Name = 'Juan';

Search by Primary Key with Find

The Find method is one of the simplest and most efficient ways to find a record in the database using its primary key.

var entity = context.Entities.Find(primaryKey);