# 5 EF Core Features You Need To Know > EF Core is powerful, and knowing a few key features can save you lots of time and frustration. I've cherry-picked five essential features that you really need to know. Published: 2024-08-10. Author: Milan Jovanović. Canonical: https://milanjovanovic.tech/blog/5-ef-core-features-you-need-to-know The five EF Core features worth knowing are query splitting, bulk updates and deletes with `ExecuteUpdate` and `ExecuteDelete`, raw SQL queries for unmapped types, global query filters, and eager loading. Each solves a specific problem, from the cartesian explosion to updating many rows in one round trip. Here is how they work and when to reach for each one. Okay, let's be honest. We all have a million things on our plates, and diving deep into every nook and cranny of EF Core might not be at the top of your priority list. But here's the deal: EF Core is powerful, and knowing a few key features can save you lots of time and frustration. So, I won't bombard you with every single EF Core feature under the sun. Instead, I've cherry-picked five essential ones that you really need to know. We'll go through: - **Query Splitting** - your database's new best friend - **Bulk Updates and Deletes** - efficiency on steroids - **Raw SQL Queries** - when you need to go rogue - **Query Filters** - keeping things nice and tidy - **Eager Loading** - because lazy isn't so great Let's get started! ## Query Splitting [**Query splitting**](https://milanjovanovic.tech/blog/how-to-improve-performance-with-ef-core-query-splitting) is one of those EF Core features that you rarely need. Until one day, you do. Query splitting is helpful in scenarios where you're eager loading multiple collections. It helps us avoid the [cartesian explosion](https://learn.microsoft.com/en-us/ef/core/performance/efficient-querying#avoid-cartesian-explosion-when-loading-related-entities) problem. Let's say we want to retrieve a department with all its teams and employees. We might write a query like this: ```csharp Department department = context.Departments .Include(d => d.Teams) .Include(d => d.Employees) .Where(d => d.Id == departmentId) .First(); ``` This translates to a single SQL query with two JOINs. However, since these `JOIN` statements are on the same level, the database will return a _cross product_. Each row from `Teams` will be joined with each row `Employees`. In that case, the database returns many rows, significantly impacting performance. Here's how we can avoid these performance issues with query splitting: ```csharp Department department = context.Departments .Include(d => d.Teams) .Include(d => d.Employees) .Where(d => d.Id == departmentId) .AsSplitQuery() .First(); ``` With `AsSplitQuery`, EF Core will execute an additional SQL query for each collection navigation. However, be cautious not to overuse query splitting. I use split queries when I've _measured_ that they consistently perform better. Split queries have more round trips to the database, which might be slower if database latency is high. There is also no consistency guarantee across multiple SQL queries. ## Bulk Updates and Deletes EF Core 7 added two new APIs for performing [**bulk updates and deletes**](https://milanjovanovic.tech/blog/how-to-use-the-new-bulk-update-feature-in-ef-core-7), `ExecuteUpdate` and `ExecuteDelete`. They allow you to efficiently update a large number of rows in one round trip to the database. Here's a practical example. The company has decided to give a 5% raise to all employees in the "Sales" department. Without bulk updates, we might iterate through each employee and update their salary individually: ```csharp var salesEmployees = context.Employees .Where(e => e.Department == "Sales") .ToList(); foreach (var employee in salesEmployees) { employee.Salary *= 1.05m; } context.SaveChanges(); ``` This approach involves multiple database roundtrips, which can be inefficient, especially for large datasets. We can achieve the same in one roundtrip using `ExecuteUpdate`: ```csharp context.Employees .Where(e => e.Department == "Sales") .ExecuteUpdate(s => s.SetProperty(e => e.Salary, e => e.Salary * 1.05m)); ``` This executes a single SQL `UPDATE` statement, directly modifying the salaries in the database without loading entities into memory, giving us improved performance. Here's another example. Let's say an e-commerce platform wants to delete all shopping carts older than one year. Here's how we could do this with `ExecuteDelete`: ```csharp context.Carts .Where(o => o.CreatedOn < DateTime.Now.AddYears(-1)) .ExecuteDelete(); ``` This results in a single SQL `DELETE` statement, directly removing the old shopping carts from the database. However, bulk updates bypass the [**EF change tracker**](https://milanjovanovic.tech/blog/change-tracker-ef-core). This could be problematic, and I wrote about the [**caveats of bulk updates in this article**](https://milanjovanovic.tech/blog/what-you-need-to-know-about-ef-core-bulk-updates). ## Raw SQL Queries EF Core 8 added a new feature that allows us to query unmapped types with raw SQL. Suppose we want to retrieve data from a database view, stored procedure, or a table that doesn't directly correspond to any of our entity classes. For example, we want to retrieve a sales summary for each product. With EF Core 8, we can define a simple `ProductSummary` class representing the structure of the result set and query it directly: ```csharp public class ProductSummary { public int ProductId { get; set; } public string ProductName { get; set; } public decimal TotalSales { get; set; } } var productSummaries = await context.Database .SqlQuery( @$""" SELECT p.ProductId, p.ProductName, SUM(oi.Quantity * oi.UnitPrice) AS TotalSales FROM Products p JOIN OrderItems oi ON p.ProductId = oi.ProductId WHERE p.CategoryId = {categoryId} GROUP BY p.ProductId, p.ProductName """) .ToListAsync(); ``` The `SqlQuery` method returns an `IQueryable`, which allows you to compose raw SQL queries with LINQ. This combines the power of raw SQL with the expressiveness of LINQ. Remember to use parameterized queries to prevent **SQL injection** vulnerabilities. The `SqlQuery` method accepts a `FormattableString`, which means you can safely use an interpolated string. Each argument is converted to a SQL parameter. You can learn more about [**raw SQL queries in this article**](https://milanjovanovic.tech/blog/ef-core-raw-sql-queries). ## Query Filters [**Query filters**](https://milanjovanovic.tech/blog/how-to-use-global-query-filters-in-ef-core) are like reusable `WHERE` clauses you can apply to your entities. These filters are automatically added to LINQ queries whenever you retrieve entities of the corresponding type. This saves you from repeatedly writing the same filtering logic in multiple places within your application. Query Filters are commonly used for scenarios like: - [**Soft Deletes**](https://milanjovanovic.tech/blog/implementing-soft-delete-with-ef-core): Filter out records marked as deleted. - [**Multi-tenancy**](https://milanjovanovic.tech/blog/multi-tenant-applications-with-ef-core): Filter data based on the current tenant. - Row-level security: Restrict access to certain records based on user roles or permissions. In a multi-tenant application, you often need to filter data based on the current tenant. Query filters allow us to handle this requirement easily: ```csharp public class Product { public int Id { get; set; } public string Name { get; set; } // Associate products with tenants public int TenantId { get; set; } } protected override void OnModelCreating(ModelBuilder modelBuilder) { // The current TenantId is set based on the current request/context modelBuilder.Entity().HasQueryFilter(p => p.TenantId == _currentTenantId); } // Now, queries automatically filter based on the tenant: var productsForCurrentTenant = context.Products.ToList(); ``` Configuring multiple query filters on the same entity will only apply the last one. You can combine multiple query filters using `&&` (AND) and `||` (OR) operators. You can use `IgnoreQueryFilters` to bypass the filters in specific queries when needed. ## Eager Loading Eager Loading is a feature in EF Core that allows you to load related entities along with your main entity in a single database query. By fetching all necessary data in a single query, you can improve application performance. This is especially true when dealing with complex object graphs or when [**lazy loading**](https://milanjovanovic.tech/blog/lazy-eager-explicit-loading-ef-core) would result in many small, inefficient queries. Here's an example `VerifyEmail` use case. We want to load an `EmailVerificationToken` and eagerly load a `User` with the `Include` method because we want to modify both entities at the same time. ```csharp {6} internal sealed class VerifyEmail(AppDbContext context) { public async Task Handle(Guid tokenId) { EmailVerificationToken? token = await context.EmailVerificationTokens .Include(e => e.User) .FirstOrDefaultAsync(e => e.Id == tokenId); if (token is null || token.ExpiresOnUtc < DateTime.UtcNow || token.User.EmailVerified) { return false; } token.User.EmailVerified = true; context.EmailVerificationTokens.Remove(token); await context.SaveChangesAsync(); return true; } } ``` EF Core will generate a single SQL query that joins the `EmailVerificationToken` and `User` tables, retrieving all the necessary data in one go. Eager loading (and query splitting, which we mentioned earlier) isn't a silver bullet. Consider using projections if you only need specific properties from related entities to avoid fetching unnecessary data. ## Summary So, there you have it! Five EF Core features that, frankly, you can't afford _not_ to know. Remember, mastering EF Core takes time, but these features provide a solid foundation to build upon. Another piece of advice is to deeply understand how your database works. Mastering SQL also allows you to get the most value from EF Core. While we focused on five key features, there are many other EF Core features worth exploring: - [**Optimistic concurrency control**](https://milanjovanovic.tech/blog/solving-race-conditions-with-ef-core-optimistic-locking) - [**Database migrations**](https://milanjovanovic.tech/blog/efcore-migrations-a-detailed-guide) - [**Compiled queries**](https://milanjovanovic.tech/blog/unleash-ef-core-performance-with-compiled-queries) - [**Transactions**](https://milanjovanovic.tech/blog/working-with-transactions-in-ef-core) - [**Interceptors**](https://milanjovanovic.tech/blog/how-to-use-ef-core-interceptors) EF Core is continuously evolving, so keep an eye on the latest updates and releases to stay ahead. Good luck out there, and see you next week. --- ## Frequently asked questions ### What is query splitting in EF Core and when should you use it? Query splitting (AsSplitQuery) runs a separate SQL query for each collection navigation instead of one query with multiple JOINs, avoiding the cartesian explosion problem when eager loading multiple collections. Use it only when you have measured that it consistently performs better, since it adds round trips and loses consistency guarantees across queries. ### How do you perform bulk updates and deletes in EF Core? EF Core 7 added ExecuteUpdate and ExecuteDelete. They translate into a single SQL UPDATE or DELETE statement that modifies rows in one round trip, without loading entities into memory. Note that they bypass the EF Core change tracker. ### Can EF Core query types that are not mapped entities with raw SQL? EF Core 8 added the SqlQuery method for querying unmapped types, such as results from database views or stored procedures. It returns an IQueryable you can compose with LINQ, and it accepts a FormattableString so interpolated arguments become SQL parameters, preventing SQL injection. ### What are global query filters in EF Core? Query filters are reusable WHERE clauses configured on an entity that EF Core automatically applies to every LINQ query for that type. They are commonly used for soft deletes, multi-tenancy, and row-level security, and you can bypass them with IgnoreQueryFilters when needed. ### What is eager loading in EF Core? Eager loading uses the Include method to load related entities along with the main entity in a single database query, typically as a SQL JOIN. It avoids many small lazy-loading queries, but if you only need a few properties, a projection can fetch less data.