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, which is an acronym that represents the four main operations we perform on databases:

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

Methods to read multiple items

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

Query all records 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 an equivalent SQL query:

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 to read a single item

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 is found that meets the condition, 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 search for a record in the database using its primary key.

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