There are a lot of points about best practices when using Entity Framework and LINQ. Sometimes we need to cipher through a lot of information. The goal of this article is to summarize some of the best practices when it comes to using LINQ and Entity Framework in a way that is easy to digest. By going so hopefully this article can be a small reference about Entity Framework best practices.

LINQ and Entity Framework Best Practices

1. Use an appropriate database schema design for your application (tables, columns, relationships, and constraints and indexes)
2. Use the latest version of Entity Framework for the best performance and functionality.
3. Use appropriate data types for columns in your database schema to ensure that queries run as quickly as possible and storage space is not wasted
4. Use projections to retrieve only the data that is needed, which can significantly reduce the amount of data that is transferred over the network.
5. Use IQueryable instead of IEnumerable when querying data to allow the query to be translated into a SQL statement and executed on the database server.
6. Avoid using complex expressions in queries, as they can be difficult to read and can impact performance.
7. Use caching to reduce the number of database queries that are required, which can significantly improve performance.
8. Use stored procedures for complex queries or to execute bulk operations.
9. Use transactions to ensure data consistency and to protect against data corruption.
10. Use a level of abstraction over DbContext to abstract away data access details and to make your code more testable.
11. Do NOT Use lazy loading as much as possible because it can lead to performance issues, use eager loading instead of lazy loading
12. Use appropriate indexes on database columns to improve query performance.
13. Use migrations to manage database schema changes in a safe and repeatable way.
14. Use the appropriate level of isolation for transactions to avoid deadlocks and improve concurrency.
15. Use connection pooling to improve database connection reuse and reduce connection overhead.
16. Use optimistic concurrency to ensure data consistency in multi-user scenarios.
17. Use the right query strategy for your data access patterns, such as using batch queries or compiled queries.
18. Use asynchronous data access to improve scalability and responsiveness.
19. Don’t use RemoveRange, it is very inefficient (RemoveRange will generate a separate SQL DELETE statement for each entity, which can be slow and resource-intensive)
20. Never loop inside DbContext instance!
21. Use ExecuteSqlCommand/FromSql only when necessary, make sure the user input is sanitized before executing something against the database to reduce the chance of Sql Injection Attack
22. Use AsNoTracking() for read-only operations
23. Use connection resiliency for handling transient errors
24. Avoid using DbContext pooling in ASP.NET Core applications (using DbContext pooling can lead to issues such as stale data or concurrency problems. This is because DbContext instances are designed to be short-lived, and pooling them (reusing them) can result in unexpected behavior)

25. Avoid using unnecessary method calls:

    Superfluous Method call 

    var result = list.Where(e => e > 5).FirstOrDefault();  // Bad Code
    var result = list.FirstOrDefault(e => e > 5); // Clean Code

26. Do not use the Count() method to check if a collection is empty because it will iterate on all the elements to count them (use Count property if it was available)
27. Use FirstOrDefault() instead of First(), when you want to get the first element, to avoid an exception throwing when the collection is Empty. Only use first we lack of item in the collection is unnatural and we want to specifically throw an exception if it didn’t exist. The same applies to other methods of this type such as Single and SingleOrDefault.

Don’t use Lazy Loading As much as Possible

Lazy loading in EF Core loads related entities from the database as they are accessed, but this can cause extra roundtrips and slow the application, which is known as the N+1 problem. It is recommended to use eager or explicit loading instead to avoid this issue. Eager loading fetches all the related data in a single query, while explicit loading makes it clear in the source code when a database roundtrip occurs.

Consider the following code:

foreach (var blog in context.Blogs.ToList())
{
    foreach (var post in blog.Posts)
    {
        Console.WriteLine($"Blog {blog.Url}, Post: {post.Title}");
    }
}

This code iterates through all the Blogs and their associated Posts and prints them out. However, because of lazy loading, each iteration of the inner loop triggers a separate database query to retrieve the associated Posts, resulting in multiple queries and poor performance.

To avoid the N+1 problem, we can use eager loading. Eager loading loads all the related data in a single query, reducing the number of database roundtrips. We can use the Include operator to specify which related entities to load.

var blogsWithPosts = context.Blogs.Include(b => b.Posts).ToList();


foreach (var blog in blogsWithPosts)
{
    foreach (var post in blog.Posts)
    {
        Console.WriteLine($"Blog {blog.Url}, Post: {post.Title}");
    }
}

In this code, we use Include to load the Posts for each Blog entity in a single query. This code produces the same output as the previous example but executes only two queries instead of N+1.

Why should RemoveRange be avoided?

The RemoveRange method in Entity Framework is used to delete multiple entities at once from a database. While it is not necessarily discouraged to use RemoveRange, there are some potential issues to be aware of.

One issue is that RemoveRange can result in inefficient database operations, particularly when dealing with large numbers of entities. This is because RemoveRange will generate a separate SQL DELETE statement for each entity, which can be slow and resource-intensive.

Another potential issue is that RemoveRange does not provide the same level of control and flexibility as other methods for deleting entities, such as individual Remove calls or custom SQL statements. For example, if you need to delete entities based on complex business logic or relationships with other entities, RemoveRange may not be the best option.

If you need to delete large numbers of entities or have complex business logic, you may want to consider using a custom SQL statement or use entity framework extensions (db.Table.Where(a => a.Price <= 0).DeleteFromQuery())

Use connection resiliency for handling transient errors

In the context of Entity Framework, “connection resiliency” refers to the ability of the framework to handle transient errors that can occur during database operations, such as network failures, server timeouts, or resource constraints.

Transient errors are temporary issues that can occur when accessing a database, and are typically resolved automatically after a short period of time. Connection resiliency allows Entity Framework to automatically retry database operations that have failed due to transient errors, which can improve the reliability and availability of your application.

For example, suppose you have an ASP.NET Core web application that uses Entity Framework to access a SQL Server database. If a transient error occurs during a database operation, such as a network timeout, Entity Framework can automatically retry the operation a specified number of times before giving up and reporting an error to the application. This can help ensure that your application remains available and responsive, even in the face of intermittent network or server issues.

To enable connection resiliency in Entity Framework, you can use the UseSqlServer method in your application’s DbContext class, and pass a DbContextOptionsBuilder instance with the EnableRetryOnFailure method called, along with any additional configuration options that you need. For example:

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder.UseSqlServer(
        "Server=myserver;Database=mydatabase;Trusted_Connection=True;",
        options => options.EnableRetryOnFailure(
            maxRetryCount: 5,
            maxRetryDelay: TimeSpan.FromSeconds(30),
            errorNumbersToAdd: null));
}

In this example, the EnableRetryOnFailure method is called with a maximum retry count of 5, a maximum retry delay of 30 seconds, and a null errorNumbersToAdd parameter, which indicates that all transient errors should be retried. This will enable connection resiliency in Entity Framework for the specified database connection.

What are Change tracking proxies and why should we be careful using them

When you retrieve entities from the database using Entity Framework, it creates instances of entity classes that represent the data from the database. Entity Framework also creates Change Tracking Proxies, which are special classes that derive from your entity classes and track changes made to the entities.  When you make changes to an entity instance, the change tracking proxies automatically detect those changes and keep track of them. If you call the SaveChanges method on your DbContext, Entity Framework will persist those changes to the database.

 However, there are some situations where using change tracking proxies can cause unexpected behavior. For example, if you retrieve an entity from the database, modify a property, and then discard the entity without saving changes, the change tracking proxy will still mark the entity as modified. If you then retrieve the same entity again and save changes, the modified property will be saved to the database, even though you didn’t intend to change it.

To avoid such issues, you should be careful when working with change tracking proxies. For example, you can disable change tracking for specific entities or properties if you don’t need them to be tracked. You can also detach entities from the DbContext to prevent them from being tracked and save changes manually using the DbContext’s Entry method.

Use the appropriate level of isolation for transactions to avoid deadlocks and improve concurrency

Transactions are used to ensure that a set of database operations are completed either in their entirety or not at all. Transactions are used to maintain the consistency and integrity of data in a database. However, if transactions are not handled correctly, they can lead to deadlocks, where two or more transactions are waiting for each other to release resources, resulting in a situation where no progress can be made.

To avoid deadlocks and improve concurrency, it is essential to use the appropriate level of isolation for transactions. Isolation levels determine how much locking occurs between transactions and how much concurrency is allowed. Here are some of the isolation levels that can be used with Entity Framework:

1. Unspecified: The transaction level is determined by the underlying database provider.
2. Read Uncommitted: In this isolation level, transactions can read uncommitted data from other transactions. This level provides the highest concurrency but can lead to dirty reads.
3. Read Committed: In this isolation level, transactions can only read committed data from other transactions. This level provides a balance between concurrency and data consistency.
4. Repeatable Read: In this isolation level, transactions can read data from other transactions, but they cannot modify the data until the transaction is complete. This level provides a higher level of data consistency but may limit concurrency.
5. Serializable: In this isolation level, transactions cannot read data that has been modified by another transaction until that transaction is complete. This level provides the highest level of data consistency but may severely limit concurrency.

More info here.

LINQ to Entity best practices and Examples

• Avoid loading unnecessary data: Only retrieve the columns and rows that are needed. For example, instead of selecting all columns from a table, specify only the columns that are required.

var products = dbContext.Products.Select(p => new { p.Id, p.Name }).ToList();

• Use eager loading to avoid lazy loading: Lazy loading can lead to N+1 queries, which can have a significant impact on performance. Use eager loading to load related entities in a single query.

var orders = dbContext.Orders.Include(o => o.Customer).ToList();

• Use IQueryable<T> instead of IEnumerable<T>: When using LINQ to Entities, use IQueryable<T> instead of IEnumerable<T>. IQueryable<T> is a more efficient way to query data.

IQueryable<Product> productsQuery = dbContext.Products.Where(p => p.Price > 10);
var products = productsQuery.ToList();

• Use compiled queries to improve performance: Compiled queries are precompiled and can be reused, which can significantly improve performance.

private static Func<MyDbContext, int, IQueryable<Order>> _ordersByCustomerIdQuery =
    EF.CompileQuery((MyDbContext context, int customerId) =>
        context.Orders.Where(o => o.CustomerId == customerId));


var orders = _ordersByCustomerIdQuery(dbContext, 1).ToList();

• Use the AsNoTracking method to avoid tracking entities: When querying data that is not going to be updated, use the AsNoTracking method to avoid tracking entities. This can improve performance by reducing the amount of work done by the change tracker.

var products = dbContext.Products.AsNoTracking().ToList();

• Use stored procedures for complex queries: For complex queries that involve multiple tables or complex logic, consider using stored procedures. Stored procedures can improve performance by reducing the amount of data transferred between the database and the application.

var products = dbContext.Database.SqlQuery<Product>("EXEC GetProductsByCategory @categoryId", categoryId).ToList();

• Use appropriate data types: Use appropriate data types for columns. Using appropriate data types can improve query performance by reducing the amount of data that needs to be processed.

CREATE TABLE Products (
    Id int PRIMARY KEY,
    Name varchar(100),
    Price decimal(18, 2)
);

For instance if the primary key is a string instead of int, that can effect query performance and the type of indexes that we can create on that column.

LINQ immediate execution vs deferred execution

When working with LINQ in C#, it is important to keep in mind that LINQ operators are categorized into two types: immediate execution operators and deferred execution operators.

Immediate execution operators are those that execute the query immediately and load the results into memory. Examples of immediate execution operators in C# are:

• ToList(): Converts the elements in a sequence to a List<T>.
• ToArray(): Converts the elements in a sequence to an array.
• Max(): Returns the maximum value in a sequence of values.
• Min(): Returns the minimum value in a sequence of values.
• Any(): Determines whether any element of a sequence exists.
• First(): Returns the first element of a sequence.
• Single(): Returns the only element of a sequence, or throws an exception if there is not exactly one element in the sequence.

Here is an example of using an immediate execution operator in C#:

List<int> numbers = new List<int> { 1, 2, 3, 4, 5 };
int maxNumber = numbers.Max(); // The query is executed immediately and the result is loaded into memory.

Deferred execution operators, on the other hand, do not execute the query immediately. Instead, they create a query that can be executed later. Examples of deferred execution operators in C# are:

• Select(): Projects each element of a sequence into a new form.
• Where(): Filters a sequence of values based on a predicate.
• OrderBy(): Sorts the elements of a sequence in ascending order.
• Take(): Returns a specified number of elements from the beginning of a sequence.

Here is an example of using a deferred execution operator in C#:

List<int> numbers = new List<int> { 1, 2, 3, 4, 5 };
IEnumerable<int> query = numbers.Where(n => n % 2 == 0); // The query is not executed immediately.
foreach (int number in query) // The query is executed here, when we loop over the results.
{
    Console.WriteLine(number);
}

It is important to acquire the results late by calling immediate execution operators (such as ToList() or ToArray()) as late as possible in the code, because these operators execute the query and load the results into memory.

Summary

In this blog post, we’ve explored essential best practices for optimizing data access using LINQ and Entity Framework. These guidelines aim to enhance the efficiency and reliability of your database interactions, ultimately leading to a more robust and high-performing application.

First and foremost, it’s crucial to start with a well-designed database schema that includes appropriate tables, columns, relationships, constraints, and indexes. Staying up-to-date with the latest version of Entity Framework ensures you benefit from improved performance and functionality. Choosing suitable data types for your database columns minimizes query execution time and optimizes storage space. Utilizing projections to retrieve only the necessary data reduces network transfer overhead, while also enhancing query performance.

When writing queries, favor IQueryable over IEnumerable for efficient translation into SQL, and keep expressions simple for better readability and performance. Caching can significantly reduce database queries, enhancing overall application speed. Complex queries or bulk operations are best handled with stored procedures. Transactions should be used to maintain data consistency and safeguard against corruption. Employ an abstraction layer over DbContext to enhance testability and maintain clean code.

Minimize lazy loading to prevent performance issues, and instead, opt for eager loading when working with related entities. Effective indexing on database columns can drastically improve query performance. Migrations provide a safe and repeatable way to manage database schema changes. Choosing the right isolation level for transactions is essential to prevent deadlocks and enhance concurrency. Connection pooling improves connection reuse and reduces overhead.

Optimistic concurrency guarantees data consistency in multi-user scenarios. Select the appropriate query strategy for your data access patterns, such as batch or compiled queries. Asynchronous data access enhances scalability and responsiveness. Avoid using the RemoveRange method for deletion as it can be inefficient; consider alternative deletion methods. Lastly, never perform loops inside a DbContext instance to maintain separation of concerns. Enable connection resiliency to handle transient errors gracefully, ensuring application availability.

In summary, adhering to these LINQ and Entity Framework best practices empowers you to optimize your data access code, resulting in a more efficient, maintainable, and reliable application. By implementing these guidelines, you can minimize performance bottlenecks and potential pitfalls, ultimately delivering a superior user experience.

Further study and Resources:

10 Entity Framework Core Best Practices – CLIMB (climbtheladder.com)

Best Practices/Entity Framework Core Integration | Documentation Center | ABP.IO

Six ways to build better Entity Framework (Core and EF6) applications – The Reformed Programmer

Best practices with Entity Framework | by Prashant | CodeX | Medium

EF6 and EF Core Bulk Extensions | Bulk Insert, Update, Delete, Upsert

Efficient Querying – EF Core | Microsoft Learn