Entity Framework Core and LINQ Best Practices for C#

Entity Framework makes data access easy to write, and that’s also what makes it easy to write badly. A query that’s instant against a few test rows can bring the database to a crawl once it hits production data, and the C# often looks perfectly fine. This is my reference list of LINQ and Entity Framework Core best practices. It covers the habits that keep queries fast, along with the mistakes I’ve seen cost the most. Each section is short and has an example where one helps. Unless I say otherwise, everything here applies to EF Core. EF Core ships a new version with .NET every November, so stay reasonably current, because each release adds translations and performance work you get for free.

Load only what you need

Most EF performance problems come from fetching more data than the code uses. A projection with Select retrieves only the columns you ask for. Its results also aren’t tracked, so you avoid change-tracking overhead too:

List<ProductSummary> products = await db.Products
    .Where(p => p.IsActive)
    .OrderBy(p => p.Name)
    .Select(p => new ProductSummary(p.Id, p.Name, p.Price))
    .ToListAsync(ct);

When you need whole entities but won’t modify them, add AsNoTracking(). In a read-heavy app you can make no-tracking the default with UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking) and opt back in with AsTracking() where you update.
To check whether anything matches, use Any(). Against the database, AnyAsync(o => o.CustomerId == id) becomes an EXISTS query that stops at the first match, while Count() > 0 counts every matching row. With in-memory collections, Count() on a List<T> just reads the Count property. Only lazy sequences get enumerated in full.
Page large result sets with Skip and Take after an OrderBy. Without an order, the database is free to return a different page every time.

IQueryable vs IEnumerable

IQueryable<T> builds an expression that EF translates into SQL. IEnumerable<T> runs in memory. As soon as a query is typed as IEnumerable<T>, every LINQ operator after that point runs in C#, after the data has been loaded. This mistake is easy to miss in a code review:

// Loads every order into memory, then filters in C#
IEnumerable<Order> allOrders = db.Orders;
List<Order> recent = allOrders.Where(o => o.CreatedAt > cutoff).ToList();

// Filters in SQL
IQueryable<Order> orders = db.Orders;
List<Order> recentOrders = await orders.Where(o => o.CreatedAt > cutoff).ToListAsync(ct);

The same thing happens when a repository method returns IEnumerable<Order> and the caller adds a Where.

Related data and the N+1 problem

Lazy loading fetches a navigation property from the database the first time you access it. In a loop, that means one query for the parent rows plus one more for every row. This is the N+1 problem:

// With lazy loading on: one query for blogs, then one per blog for its posts
foreach (var blog in context.Blogs.ToList())
{
    foreach (var post in blog.Posts)
    {
        Console.WriteLine($"Blog {blog.Url}, Post: {post.Title}");
    }
}

Lazy loading is off by default in EF Core. You have to install the Proxies package or use ILazyLoader to turn it on. Without it, the code above doesn’t make extra queries. It prints nothing, because Posts was never loaded, which is a quieter bug of its own. Either way, the fix is to say up front what you need. Include loads the posts as part of the same query:

var blogsWithPosts = await context.Blogs
    .Include(b => b.Posts)
    .ToListAsync(ct);

Since EF Core 3.0 this is a single query with a JOIN. If you include two or more collections, that JOIN multiplies rows, which is known as cartesian explosion. Adding AsSplitQuery() makes EF send one query per collection instead. Often a projection is better still, because it fetches only the columns you print:

var blogs = await context.Blogs
    .Select(b => new { b.Url, PostTitles = b.Posts.Select(p => p.Title).ToList() })
    .ToListAsync(ct);

When you only sometimes need the related data, explicit loading (context.Entry(blog).Collection(b => b.Posts).LoadAsync()) makes the extra round trip visible in the code instead of hiding it behind a property getter.

Don’t query or save inside a loop

Every query and every SaveChanges call is a round trip to the database. Inside a loop, those round trips add up fast:

// One query per ID
foreach (int id in productIds)
{
    Product? product = await db.Products.FindAsync(id);
    // ...
}

// One query for all of them
List<Product> products = await db.Products
    .Where(p => productIds.Contains(p.Id))
    .ToListAsync(ct);

The same goes for writes. Make all your changes, then call SaveChangesAsync once. EF batches the resulting statements into as few round trips as it can, and wraps them in a transaction automatically.

Bulk updates and deletes

The real problem with RemoveRange isn’t the SQL it generates. EF Core batches the DELETE statements. The problem is that you have to load every entity into memory before you can remove it. Since EF Core 7, ExecuteDelete and ExecuteUpdate run a single set-based statement on the server, without loading anything:

int deleted = await db.Products
    .Where(p => p.Price <= 0)
    .ExecuteDeleteAsync(ct);

await db.Products
    .Where(p => p.CategoryId == categoryId)
    .ExecuteUpdateAsync(s => s.SetProperty(p => p.Price, p => p.Price * 1.1m), ct);

These methods bypass the change tracker. Entities you already have loaded won’t reflect the change, and SaveChanges interceptors and any cascade logic in your code won’t run. Only the database’s own cascades apply. For inserting very large volumes, SqlBulkCopy is still the fastest option on SQL Server. Before EF Core 7, the commercial Entity Framework Extensions library (DeleteFromQuery()) was the usual answer for bulk deletes. Today you only need it for its other bulk features.

Deferred vs immediate execution

LINQ operators like Where, Select, OrderBy and Take are deferred. They describe a query, and nothing runs until it’s enumerated. ToList, ToArray, First, Single, Any, Count, Max and Min run the query immediately. Three rules follow from this:

Compose all the filtering, ordering and projection you can before you materialize, so the database does that work.
Materialize once. Each time you enumerate a deferred query, it runs again, and the data may have changed in between:

IQueryable<Order> pending = db.Orders.Where(o => o.Status == OrderStatus.Pending);

int count = await pending.CountAsync(ct);          // first query
List<Order> list = await pending.ToListAsync(ct);  // second query, which may return different rows

Materialize before the DbContext is disposed. If a method returns a deferred query and the caller enumerates it after the context’s scope has ended, the caller gets an ObjectDisposedException.

Raw SQL and stored procedures

EF Core can translate far more LINQ than it used to. Since EF Core 3.0, if it can’t translate an expression, it throws instead of quietly running that part in memory. When you do need SQL, use the interpolated APIs. Each interpolated value is sent as a parameter, which protects you from SQL injection without any manual sanitizing:

// Stored procedure that returns entities
List<Product> products = await db.Products
    .FromSql($"EXEC GetProductsByCategory {categoryId}")
    .ToListAsync(ct);

// Unmapped result type (EF Core 8 and later)
List<CategoryTotal> totals = await db.Database
    .SqlQuery<CategoryTotal>($"SELECT CategoryId, SUM(Price) AS Total FROM Products GROUP BY CategoryId")
    .ToListAsync(ct);

// Commands
await db.Database.ExecuteSqlAsync($"UPDATE Products SET IsActive = 0 WHERE CategoryId = {categoryId}", ct);

Watch out for the Raw variants. FromSqlRaw($"... {value}") inserts the value directly into the SQL text, which makes it injectable. Use it only with SQL you’ve built yourself from trusted parts. Stored procedures are worth using for complex reporting queries, or for logic that has to stay in the database. Keep in mind that EF can’t add anything to a stored procedure call, so any Where you chain after it runs in memory. Plain SQL in FromSql can be extended with more LINQ.

DbContext lifetime, change tracking and pooling

A DbContext is meant to represent one unit of work. In ASP.NET Core, the default scoped registration gives you one context per request. Contexts that live longer than that cause trouble. Suppose you load a product, change its price, decide not to save, and query the same product again on the same context. EF returns the instance it’s already tracking, with your unsaved change still on it, and the next SaveChanges writes that change to the database. Keep contexts short-lived. If a long-running job has to reuse one, call ChangeTracker.Clear() between batches.

By default EF Core detects changes by comparing each entity with a snapshot taken when it was loaded. Change-tracking proxies (UseChangeTrackingProxies) are an opt-in alternative. They’re faster when you track many entities, but every navigation property must be virtual, and you have to create entities through CreateProxy. Most applications don’t need them.

DbContext pooling is a performance feature, not a risk. AddDbContextPool reuses context instances across requests and resets their state when each one is returned to the pool. That saves the setup cost on every request:

builder.Services.AddDbContextPool<StoreDbContext>(options =>
    options.UseSqlServer(
        builder.Configuration.GetConnectionString("Store"),
        sql => sql.EnableRetryOnFailure()));

Pooling has one real catch. EF resets its own state, but not fields you’ve added to your DbContext subclass, such as a tenant ID set in the constructor. That kind of state can leak from one request into the next. Two more rules apply whether you pool or not. A DbContext isn’t thread-safe, so never run two queries on the same context in parallel with Task.WhenAll. And don’t turn off ADO.NET connection pooling, which is on by default and handles connection reuse for you.

Transactions, isolation levels and concurrency

A single SaveChanges call already runs in a transaction, so you only need an explicit one (BeginTransactionAsync) when several SaveChanges calls or raw SQL commands must succeed or fail together. The isolation level decides how much transactions see of each other’s work. Higher levels hold more locks, and more locks mean more deadlocks. These descriptions follow SQL Server:

Read Uncommitted allows dirty reads, meaning you can see data other transactions haven’t committed. It’s the same as NOLOCK, and it’s rarely the right choice.
Read Committed is SQL Server’s default. You only see committed data, but a value can change between two reads in the same transaction.
Repeatable Read keeps the rows you’ve read from being changed until your transaction ends. New rows that match your query (phantoms) can still appear.
Serializable locks ranges, so transactions behave as if they ran one after another. It’s the safest level and the most likely to deadlock.
Snapshot and Read Committed Snapshot (RCSI) give readers a versioned copy of the data, so readers and writers don’t block each other. RCSI is on by default in Azure SQL Database, and turning it on in SQL Server fixes many reader/writer deadlocks in read-heavy apps.

Choose a level with db.Database.BeginTransactionAsync(IsolationLevel.RepeatableRead, ct), and keep transactions short. Never wait on an HTTP call or on user input while a transaction is open.

For users editing the same record at the same time, optimistic concurrency is usually better than locking. Add a row-version column, and EF includes it in the WHERE clause of every update:

public class Product
{
    public int Id { get; set; }
    public decimal Price { get; set; }

    [Timestamp]
    public byte[] RowVersion { get; set; } = [];
}

If someone else changed the row first, the update affects no rows and EF throws DbUpdateConcurrencyException. You then decide whether to reload the data, merge the changes or show the conflict to the user.

Connection resiliency

Cloud databases have transient failures, such as brief network drops, failovers and throttling. EnableRetryOnFailure retries these automatically. Configure it where you register the context, with the connection string from configuration, not hard-coded in OnConfiguring:

builder.Services.AddDbContext<StoreDbContext>(options =>
    options.UseSqlServer(
        builder.Configuration.GetConnectionString("Store"),
        sql => sql.EnableRetryOnFailure(
            maxRetryCount: 5,
            maxRetryDelay: TimeSpan.FromSeconds(30),
            errorNumbersToAdd: null)));

Here errorNumbersToAdd: null means you aren’t adding any error numbers to SQL Server’s built-in list of transient errors. It doesn’t mean “retry everything”. The trap is explicit transactions. With a retry strategy enabled, calling BeginTransaction directly throws an InvalidOperationException. EF can’t safely retry half a transaction, so you have to give it the whole unit of work to retry:

IExecutionStrategy strategy = db.Database.CreateExecutionStrategy();

await strategy.ExecuteAsync(async () =>
{
    await using IDbContextTransaction transaction = await db.Database.BeginTransactionAsync(ct);

    // All the work that must succeed or fail together
    await db.SaveChangesAsync(ct);

    await transaction.CommitAsync(ct);
});

The delegate can run more than once, so it has to be safe to repeat. Create the entities inside it, and don’t call external services from it.

Schema, data types and indexes

EF’s defaults are safe, but they aren’t always efficient. A string property maps to nvarchar(max), which can’t be an index key and costs more to process. Give strings a maximum length, give decimals a precision, and declare the indexes your queries need:

modelBuilder.Entity<Product>(product =>
{
    product.Property(p => p.Name).HasMaxLength(100).IsRequired();
    product.Property(p => p.Price).HasPrecision(18, 2);
    product.HasIndex(p => p.Sku).IsUnique();
});

Keys: an int or long identity column is compact and fast to index. Wide string keys make every index and foreign key bigger. Random GUIDs fragment a clustered index. EF Core’s SQL Server provider generates sequential GUIDs by default to avoid this, and .NET 9 added Guid.CreateVersion7() for time-ordered GUIDs.
Use varchar only for data you know is ASCII, like codes and SKUs. Names and free text need nvarchar.
Base indexes on the queries you actually run. Check the execution plans for your slowest queries, and index the columns they filter, join and sort on.
Manage schema changes with migrations. In production, don’t call Database.Migrate() at startup, because several instances may start at once and race to apply the same migration. Generate an idempotent script with dotnet ef migrations script --idempotent, or build a migration bundle with dotnet ef migrations bundle, and run it as a deployment step.

See the SQL, then optimize

You can’t judge an EF query without seeing the SQL it generates. ToQueryString() shows the SQL for a query without running it. LogTo(Console.WriteLine, LogLevel.Information) in development, or the Microsoft.EntityFrameworkCore.Database.Command log category, shows every command EF sends along with how long it took. Turn on EnableSensitiveDataLogging only in development, because it writes parameter values to the log.

Compiled queries: EF already caches the translation of each query, so EF.CompileAsyncQuery only pays off on very hot paths where you’ve measured the translation cost. Note that it returns an IAsyncEnumerable<T>, not an IQueryable:

private static readonly Func<StoreDbContext, int, IAsyncEnumerable<Order>> OrdersByCustomer =
    EF.CompileAsyncQuery((StoreDbContext db, int customerId) =>
        db.Orders.Where(o => o.CustomerId == customerId));

await foreach (Order order in OrdersByCustomer(db, customerId))
{
    // ...
}

Caching: EF has no built-in second-level cache. Cache reference data that rarely changes, such as countries or categories, with IMemoryCache or HybridCache (.NET 9 and later). Cache DTOs, not tracked entities, which belong to one context.
Async: Use ToListAsync, FirstOrDefaultAsync and SaveChangesAsync in web apps, so threads aren’t blocked waiting for the database.
Simple expressions: If a LINQ expression is hard to read, it usually produces SQL that’s hard to read too. Split it into steps, or move it into a well-named method.

Abstraction and testing

DbContext already implements the unit of work and repository patterns, so a generic IRepository<T> on top of it mostly hides features like Include, projections and AsNoTracking. I still put an abstraction over data access, but a thin, query-specific one. An IOrderQueries interface with a GetRecentOrdersAsync method keeps EF details out of the business logic and gives tests an easy seam. To test the queries themselves, run them against the same database engine you use in production, for example SQL Server in a Docker container through Testcontainers. The EF in-memory provider doesn’t behave like a relational database, and Microsoft recommends against using it for this.

Small LINQ habits

First() throws when nothing matches, and FirstOrDefault() returns null or default. Use First when an empty result means something is broken and should fail loudly. Use FirstOrDefault when “not found” is a normal outcome, and handle the null.
Single() and SingleOrDefault() also throw when more than one row matches. That’s useful for lookups that must be unique, but EF has to fetch two rows (TOP 2) to check.
Prefer FirstOrDefault(e => e > 5) to Where(e => e > 5).FirstOrDefault() because it reads better. Against EF the two produce identical SQL, and in memory the performance difference is negligible.

Hamid Mosalla

Programming Adventures