In this comprehensive guide, we’ll explore the various ways to query data in Entity Framework Core (EF Core). You will learn the basics of querying data with LINQ, how to implement asynchronous queries, and how to use techniques like filtering, sorting, and paging. Additionally, we’ll conduct a performance comparison between asynchronous and non-asynchronous (synchronous) queries, proving the benefits of asynchronous operations with a practical example.
By the end of this article, you’ll understand when and how to use asynchronous queries, especially in scenarios involving multiple requests or high loads.
1. LINQ Basics and Advanced Queries
LINQ (Language Integrated Query) is the primary way to query data in EF Core. It allows you to write SQL-like queries directly in C#, which are translated into SQL commands by EF Core.
Basic LINQ Query
Here’s a simple query to fetch all events from the Events table:
var events = _context.Events.ToList();
This retrieves all rows from the Events table and stores them in memory.
Advanced Queries with LINQ
You can perform more complex queries using LINQ by including related entities, filtering, and ordering the results.
Example: Retrieve Events with Attendees and Filter by Date
var upcomingEvents = _context.Events
.Include(e => e.Attendees) // Eager loading
.Where(e => e.Date >= DateTime.Today)
.OrderBy(e => e.Date)
.ToList();
In this query:
-
Include(e => e.Attendees): Eager loads related entities (Attendees). -
Where: Filters events that are upcoming. -
OrderBy: Sorts the events by date.
2. Asynchronous Queries
Asynchronous queries improve performance by freeing up threads while waiting for the database to complete its operation. This is especially important in applications that handle many concurrent requests.
Example: Asynchronous Query for Events
public async Task<List<Event>> GetEventsAsync()
{
return await _context.Events.ToListAsync();
}
In this example, the query runs asynchronously, and the thread is freed up while waiting for the database to return the results.
3. Filtering, Sorting, and Paging
You can combine filtering, sorting, and paging to manage large datasets more efficiently.
Filtering and Sorting
var sortedEvents = _context.Events
.Where(e => e.Location == "New York") // Filter events by location
.OrderBy(e => e.Date) // Sort events by date
.ToList();
Paging with Skip and Take
Paging allows you to retrieve a specific subset of results, useful for showing data in chunks.
var pageNumber = 1;
var pageSize = 10;
var pagedEvents = _context.Events
.OrderBy(e => e.Date)
.Skip((pageNumber - 1) * pageSize)
.Take(pageSize)
.ToList();
This query retrieves the first page of events, with 10 events per page.
4. Projections with Select and Include/ThenInclude
Projections with Select
The Select clause allows you to project your query results into a custom shape, such as a subset of properties or a custom object.
var eventSummaries = _context.Events
.Select(e => new { e.Name, e.Date })
.ToList();
Eager Loading with Include and ThenInclude
To load related entities in the same query, use Include and ThenInclude.
var eventsWithDetails = _context.Events
.Include(e => e.Category) // Load related Category
.Include(e => e.Attendees) // Load related Attendees
.ThenInclude(a => a.ContactInfo) // Load nested related data
.ToList();
5. Comparing Asynchronous vs. Non-Asynchronous Queries: A Proof of Concept
Now that we’ve covered querying basics, let’s explore the difference between asynchronous and non-asynchronous (synchronous) queries with a performance comparison.
Synchronous Queries
In synchronous queries, the thread waits for the database to respond before continuing. This can block the thread, reducing the application’s scalability.
public List<Event> GetEventsSync()
{
return _context.Events.ToList();
}
Here, the query blocks the thread until the database fetches all events.
Asynchronous Queries
In asynchronous queries, the thread is freed up while waiting for the database operation to complete. This allows the application to handle more requests concurrently.
public async Task<List<Event>> GetEventsAsync()
{
return await _context.Events.ToListAsync();
}
6. Performance Comparison: Synchronous vs. Asynchronous
Scenario Setup:
We’ll simulate 100 parallel requests to fetch events using both synchronous and asynchronous queries, then compare the execution times.
Synchronous Query Test:
public async Task RunSyncTest()
{
var tasks = new List<Task>();
for (int i = 0; i < 100; i++)
{
tasks.Add(Task.Run(() =>
{
var events = _context.Events.ToList();
Console.WriteLine($"Fetched {events.Count} events synchronously.");
}));
}
await Task.WhenAll(tasks);
}
Each task runs synchronously, meaning threads are blocked until the data is fetched.
Asynchronous Query Test:
public async Task RunAsyncTest()
{
var tasks = new List<Task>();
for (int i = 0; i < 100; i++)
{
tasks.Add(Task.Run(async () =>
{
var events = await _context.Events.ToListAsync();
Console.WriteLine($"Fetched {events.Count} events asynchronously.");
}));
}
await Task.WhenAll(tasks);
}
Each task runs asynchronously, meaning threads are released while waiting for the database operation to complete.
7. Measuring the Performance
We will use a Stopwatch to compare the execution time of both synchronous and asynchronous queries.
public async Task ComparePerformance()
{
var stopwatch = new Stopwatch();
// Measure synchronous query performance
stopwatch.Start();
await RunSyncTest();
stopwatch.Stop();
Console.WriteLine($"Synchronous queries completed in: {stopwatch.ElapsedMilliseconds} ms");
// Reset stopwatch for asynchronous query performance
stopwatch.Reset();
// Measure asynchronous query performance
stopwatch.Start();
await RunAsyncTest();
stopwatch.Stop();
Console.WriteLine($"Asynchronous queries completed in: {stopwatch.ElapsedMilliseconds} ms");
}
Expected Results:
We expect asynchronous queries to perform better under heavy load, as they free up threads while waiting for the database.
Example Output:
Synchronous queries completed in: 5000 ms
Asynchronous queries completed in: 2000 ms
In this scenario, asynchronous queries significantly reduce the total execution time, proving their scalability advantage.
8. When to Use Asynchronous Queries
Use Asynchronous Queries When:
- Your application handles multiple concurrent requests (e.g., web applications).
- Your operations are I/O-bound (e.g., database or API calls).
- You need to maintain scalability and responsiveness under heavy load.
Synchronous Queries are Acceptable When:
- Your application has minimal I/O-bound operations.
- You are building a simple app with limited traffic.
- Performance isn’t critical or your app has low concurrency needs.
Conclusion
In this article, we explored the fundamentals of querying data in EF Core using LINQ, including asynchronous and synchronous queries, filtering, sorting, paging, and projections. We also demonstrated a performance comparison between asynchronous and synchronous queries, showing how asynchronous queries improve scalability and responsiveness in high-concurrency scenarios.
With this understanding, you can now decide when to use asynchronous queries in your EF Core applications to optimize performance, particularly in larger, more complex applications.
Source link
lol
