Why your EF Core queries are slow and how to fix them

Entity Framework Core is powerful and flexible, but it's also easy to accidentally write slow, inefficient queries without realising it. If your ASP.NET Core application feels sluggish or your SQL Server performance is degrading under load, EF Core might be doing more work than you think behind the scenes.

We'll look at the five most common reasons EF Core queries become slow - and show exactly how to fix each one with examples, SQL output comparisons, and best practices you can apply immediately.

String columns default to nvarchar(max)

By default, EF Core treats all string properties as unlimited length. That means if you use EF Core migrations and run Add-Migration, your database ends up with nvarchar(max) for every string column:

Example entities:

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

	public int? CategoryId { get; set; }

	public string Name { get; set; } = string.Empty;

	public decimal WeightKg { get; set; }

	public string Description { get; set; } = string.Empty;

	public decimal Price { get; set; }

	public Category? Category { get; set; }
}

public class Category
{
	public int Id { get; set; }

	public string Name { get; set; } = string.Empty;
}

Resulting SQL queries to create tables

CREATE TABLE [dbo].[Category](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](max) NOT NULL,
CONSTRAINT [PK_Category] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Product](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[CategoryId] [int] NULL,
	[Name] [nvarchar](max) NOT NULL,
	[WeightKg] [decimal](18, 2) NOT NULL,
	[Description] [nvarchar](max) NOT NULL,
	[Price] [decimal](18, 2) NOT NULL,
 CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Product]  WITH CHECK ADD  CONSTRAINT [FK_Product_Category_CategoryId] FOREIGN KEY([CategoryId])
REFERENCES [dbo].[Category] ([Id])
GO

ALTER TABLE [dbo].[Product] CHECK CONSTRAINT [FK_Product_Category_CategoryId]
GO

Why this is bad

• nvarchar(max) columns cannot be indexed efficient
• Queries and sorting become significantly slower

Fix: Add maximum lengths

Add a configuration file for each entity and specify the HasMaxLength method:

public class ProductConfiguration : IEntityTypeConfiguration<Product>
{
	public void Configure(EntityTypeBuilder<Product> builder)
	{
		builder.HasKey(s => s.Id);

		builder.Property(x => x.Name)
			.HasMaxLength(100);

		builder.Property(x => x.Description)
			.HasMaxLength(1000);

		builder.ToTable("Product");
	}
}

public class CategoryConfiguration : IEntityTypeConfiguration<Category>
{
	public void Configure(EntityTypeBuilder<Category> builder)
	{
		builder.HasKey(s => s.Id);

		builder.Property(x => x.Name).HasMaxLength(100);

		builder.ToTable("Category");
	}
}

This produces leaner, index-friendly columns like nvarchar(100).

You can learn how to add configuration files to your DbContext by watching this video:

Querying full entities

Consider this common query:

await _context.Products.SingleOrDefaultAsync(x => x.Id == id);

The SQL generated for this query would be:

SELECT TOP(2) [p].[Id], [p].[CategoryId], [p].[Description], [p].[Name], [p].[Price], [p].[WeightKg]
FROM [Product] AS [p]
WHERE [p].[Id] = @id

It fetches every column in the Product table - even if you are only using a couple of them.

The fix is to create a separate DTO class and select the fields that you need:

public class ProductListingDto
{
	public int Id { get; set; }

	public string Name { get; set; } = string.Empty;
}

Then modify the query:

await _context.Products
	.Select(s => new ProductListingDto { Id = s.Id, Name = s.Name}) // <-- Select the columns you need.
	.SingleOrDefaultAsync(x => x.Id == id);

The SQL output then becomes:

SELECT TOP(2) [p].[Id], [p].[Name]
FROM [Product] AS [p]
WHERE [p].[Id] = @id

This results in:

• Less data
• Faster queries
• Smaller memory usage

Lazy loading creates n+1 queries

You can enable lazy loading in EF Core by:

• Installing the Microsoft.EntityFrameworkCore.Proxies package
• Add the .UseLazyLoadingProxies() extension method to your DbContext

// Program.cs
builder.Services.AddDbContext<MyDbContext>(options =>
{
	options
		.UseLazyLoadingProxies()
		.UseSqlServer(configuration.GetConnectionString("MyDbContext"));
});

• Making navigation properties virtual

public class Product
{
	...
	public virtual Category? Category { get; set; }
}

Lazy loading is good as you can load navigation properties only when you need them. But it comes with a catch...

The problem: n+1 queries

Consider this method:

public async Task<IList<ProductWithCategoryDto>> GetAllWithCategory()
{
	var productsWithCategory = new List<ProductWithCategoryDto>();
	var products = await _context.Products.ToListAsync(); // <-- Loads the products

	foreach (var p in products)
	{
		var productWithCategory = new ProductWithCategoryDto
		{
			Id = p.Id,
			Name = p.Name,
			CategoryName = p.Category?.Name // <-- Loads the category navigation property from the database.
		};
		productsWithCategory.Add(productWithCategory);
	}

	return productsWithCategory;
}

The await _context.Products.ToListAsync() call will load all the products from the database. But then it will loop through each of the products and try to get the category name by calling the Category navigation property:

foreach (var p in products)
{
	var productWithCategory = new ProductWithCategoryDto
	{
		Id = p.Id,
		Name = p.Name,
		CategoryName = p.Category?.Name // <-- Loads the Category navigation property from the database
	};
	productsWithCategory.Add(productWithCategory);
}

Just say we had products with three different categories, the resulting SQL would look like this:

SELECT [p].[Id], [p].[CategoryId], [p].[Description], [p].[Name], [p].[Price], [p].[WeightKg]
FROM [Product] AS [p]
	  
SELECT TOP(1) [c].[Id], [c].[Name] FROM [Category] WHERE [c].[Id] = @p	  
SELECT TOP(1) [c].[Id], [c].[Name] FROM [Category] WHERE [c].[Id] = @p
SELECT TOP(1) [c].[Id], [c].[Name] FROM [Category] WHERE [c].[Id] = @p

One query for the products, and one query for each of the different categories.

Fix: Use explicit loading with Include

Specifying the Category navigation property in the Include method will join the Category table before the query is executed.

var products = await _context.Products.Include(c => c.Category).ToListAsync();

public async Task<IList<ProductWithCategoryDto>> GetAllWithCategory()
{
	var productsWithCategory = new List<ProductWithCategoryDto>();
	var products = await _context.Products.Include(c => c.Category).ToListAsync();

	foreach (var p in products)
	{
		var productWithCategory = new ProductWithCategoryDto
		{
			Id = p.Id,
			Name = p.Name,
			CategoryName = p.Category?.Name
		};
		productsWithCategory.Add(productWithCategory);
	}

	return productsWithCategory;
}

This will result in a much better performing and faster SQL query:

SELECT [p].[Id], [p].[CategoryId], [p].[Description], [p].[Name], [p].[Price], [p].[WeightKg], [c].[Id], [c].[Name]
FROM [Product] AS [p]
LEFT JOIN [Category] AS [c] ON [p].[CategoryId] = [c].[Id]

Adding filters after the data is queried

Filters helps reduce the number of results returned in a query. Here, we are filtering the results based on page size. However, it's not uncommon to do filters after the query has been executed:

var products = await _context.Products
	.Select(s => new ProductListingDto { Id = s.Id, Name = s.Name })
	.ToListAsync(); // <-- Query executed
		
products = products.Skip(page * pageSize - pageSize) // <!-- Paging done here
			.Take(pageSize).ToList();

This means that EF Core will load all the products:

SELECT [p].[Id], [p].[Name]
FROM [Product] AS [p]

This is:

• Slow for large tables
• Uses unnecessary memory

Fix: Apply filters before ToListAsync()

var products = await _context.Products
	.Select(s => new ProductListingDto { Id = s.Id, Name = s.Name })
	.Skip(page * pageSize - pageSize).Take(pageSize) // <-- Applies paging before the query is executed
	.ToListAsync();

This means that SQL Server does the filtering:

SELECT [p].[Id], [p].[Name]
FROM [Product] AS [p]
ORDER BY (SELECT 1)
OFFSET @p ROWS FETCH NEXT @p0 ROWS ONLY

If you want to apply filters in a Where clause, also add these before the query is compiled. This is so SQL Server does the filtering and not the application:

var products = await _context.Products
	.Where(x => x.WeightKg > 5)
	.Select(s => new ProductListingDto { Id = s.Id, Name = s.Name })
	.Skip(page * pageSize - pageSize).Take(pageSize)
	.ToListAsync();

Resulting SQL query:

SELECT [p].[Id], [p].[Name]
FROM [Product] AS [p]
WHERE [p].[WeightKg] > 5.0
ORDER BY (SELECT 1)
OFFSET @p ROWS FETCH NEXT @p0 ROWS ONLY

Tracking entities that are read-only

EF Core tracks entities so that changes can be saved.

But read-only queries don't need tracking, and tracking adds overhead.

Fix: Add .AsNoTracking for read-only queries

var products = await _context.Products.AsNoTracking()
	.Select(s => new ProductListingDto { Id = s.Id, Name = s.Name })
	.Skip(page * pageSize - pageSize).Take(pageSize)
	.ToListAsync();

When you add the AsNoTracking method, it will not make any updates to your entity as the entites are not being tracked. Do not use it if you need to make changes.

var productsNoTracking = _context.Products.AsNoTracking(); // <-- No tracking enabled

var products = await productsNoTracking
	.Select(s => new ProductListingDto { Id = s.Id, Name = s.Name })
	.Skip(page * pageSize - pageSize).Take(pageSize)
	.ToListAsync();

(await productsNoTracking.FirstAsync()).WeightKg = 15.8m;
await _context.SaveChangesAsync(); // <-- This will not update the entity

Watch the video

Watch this video where we show you these examples in an application so you can see it in action:

In summary

Problem	Fix
Default nvarchar(max)	Use HasMaxLength()
Selecting full entities	Use DTOs and .Select()
Lazy loading n+1 problem	Use Include()
Paging after ToListAsync()	Apply Where, Skip and Take before executing the query
Unnecessary tracking on read-only queries	Use .AsNoTracking()

Applying these five practices will make your EF Core queries significantly faster, reduce SQL load, and give you better performance.