Essential Entity Framework Core Tips: How to Optimize Performance, Streamline Queries, and More

Learn how to boost query performance, operations, and manage large datasets effectively. From basic to advanced tips for developers looking to enhance their EF Core skills. 1. Use AsNoTracking for Read-Only Scenarios AsNoTracking fetches data that is not intended to be changed in the current context. It makes queries faster and more efficient because EF Core does not need to capture tracking information. var users = context.Users.AsNoTracking().ToList(); The aforementioned query retrieves all users from the database without any tracking overhead, which provides performance gain in data-heavy application 2. Opt for Explicit Loading Explicit loading is a process where related data is loaded from the database at a later time and not immediately with the initial query. var book = context.Books.Single(b => b.BookId == 1); context.Entry(book).Collection(b => b.Authors).Load(); Here, the book entity is first loaded without its related Authors. Only when explicitly called, the Authors collection is loaded into memory. 3. Use Configuration Over Conventions EF Core uses conventions based on your class definitions to configure the schema of the database. public class MyContext : DbContext { protected override void OnModelCreating(ModelBuilder modelBuilder) { modelBuilder.Entity () .Property(u => u.Username) .IsRequired() .HasMaxLength(100); } } 4. Leverage Query Filters for Multi-Tenancy Query filters are LINQ query predicates applied to models globally in the DbContext. protected override void OnModelCreating(ModelBuilder modelBuilder) { modelBuilder.Entity ().HasQueryFilter(p => p.TenantId == _tenantId); } It filters every query to include only data relevant to a specific tenant identified by _tenantId. 5. Index Attributes to Optimize Queries Adding index attributes to properties that are frequently used in WHERE clauses or join keys can significantly improve query performance by reducing the data scan scope. public class User { [Key] public int UserId { get; set; } [Required] [MaxLength(256)] [Index] // Index attribute to improve search performance public string Username { get; set; } } By marking Username with an [Index], searches against this column are more efficient. 6. Use Batch Saves to Reduce Database Roundtrips When updating or inserting multiple entities, use batch operations to minimize the number of round trips to the database. var users = new List { new User { Username = "user1" }, new User { Username = "user2" } }; context.Users.AddRange(users); context.SaveChanges(); It adds multiple users in one operation, optimizing connection usage. 7. Avoid N+1 Queries in EF Core The N+1 problem occurs in an application when it makes one query to retrieve the primary data and then multiple secondary queries for each piece of primary data to retrieve metadata. It can be prevented by the eager loading of data. var departments = context.Departments.Include(d => d.Employees).ToList(); It fetches departments along with their employees in a single query, preventing multiple queries for each department. 8. Handle Concurrency With Optimistic Concurrency It is used to handle conflicts so that a record hasn’t been modified by another transaction before updating it in the database. [ConcurrencyCheck] public string Email { get; set; } The attribute ensures that the Email value hasn't been modified by the different users before the current transaction has completed execution, preventing data inconsistency. 9. Use Lazy Loading Judiciously Lazy loading automatically loads data from the database the first time a navigation property is accessed. public virtual ICollection Orders { get; set; } // Lazy loading of orders Here, Orders are loaded only when accessed, which helps reduce initial load time. 10. Implement Efficient Pagination Efficient pagination reduces the load on the database by retiring only a subset of the total records that are currently required. int pageNumber = 2; int pageSize = 10; var users = context.Users.OrderBy(u => u.UserId) .Skip((pageNumber - 1) * pageSize) .Take(pageSize) .ToList(); It filters out only the second page of users, limiting the result to 10 entries, which helps in handling large datasets efficiently. 11. Use Select to Shape Data Instead of fetching the complete models from the database, use Select to transform data into simpler models to reduce memory usage and improve performance. var userProfiles = context.Users .Select(u => new UserProfileDto { UserId = u.Id, Username = u.Username, Email = u.Email }) .ToList(); It fetches only the required fields, minimizing the size of data transferred over the network. 12. Minimize Database Hits with FirstOrDefaultAsync Use asynchronous methods like FirstOrDefaultAsync instead of FirstOrDefault for querying single objects. var user = await context.Users .Where(u => u.Username == "john.doe") .FirstOrDefaultAsync(); The non-blocking call improves the performance of your application under heavy load. 13. Pre-compiled Queries for Repeated UsePre-compile var compiledQuery = EF.CompileQuery( (MyDbContext context, int userId) => context.Users.FirstOrDefault(u => u.UserId == userId) ); // Usage var user = compiledQuery.Invoke(context, 5); Useful in high-load scenarios where the same query is executed frequently. 14. Utilize Transaction Scopes for Multiple Operations Use a transaction scope to ensure multiple operations either all succeed or fail. using (var transaction = context.Database.BeginTransaction()) { try { context.Users.Add(newUser); context.SaveChanges(); context.Purchases.Add(newPurchase); context.SaveChanges(); transaction.Commit(); } catch { transaction.Rollback(); } } It ensures data consistency across operations. 15. Optimize Model Building with OnModelCreating Use the OnModelCreating method in your DbContext to optimize model configuration, indexes, and relationships. protected override void OnModelCreating(ModelBuilder modelBuilder) { modelBuilder.Entity () .HasIndex(u => u.Email) .IsUnique(); } It creates a unique index for the Email column, which is beneficial for performance when querying by email. 16. Implement Caching to Reduce Database Load Implement caching for data that is static to decrease database load and improve UI responsiveness. var cacheKey = "Top10Users"; var topUsers = memoryCache.GetOrCreate(cacheKey, entry => { entry.SlidingExpiration = TimeSpan.FromMinutes(10); return context.Users.OrderBy(u => u.SignupDate).Take(10).ToList(); }); The above approach caches the top 10 users and refreshes the cache every 10 minutes. 17. Handle Large Data Sets with AsSplitQuery Split queries are generally used for querying huge data sets with complex includes which can be more efficient than loading all data in a single query. var orders = context.Orders .Include(o => o.Customer) .Include(o => o.OrderDetails) .AsSplitQuery() .ToList(); The method breaks down the query into multiple SQL queries which performs better than a single complex query. 18. Avoid Memory Leaks With Detach Separate entities from the DbContext when they’re not required anymore to prevent memory leaks. context.Entry(user).State = EntityState.Detached; It removes the entity from the context, reduces space, and prevents potential performance issues. 19. Use Include and ThenInclude for Deep Loading For loading complex object graphs, utilize Include and ThenInclude to specify related data to be loaded from the database. var books = context.Books .Include(b => b.Author) .ThenInclude(a => a.ContactDetails) .ToList(); The query mentioned above loads books, authors, and the authors’ contact details in a single operation. 20. Stream Large Data Sets With AsAsyncEnumerable To handle large data sets, use AsAsyncEnumerable to stream data asynchronously, which in turn reduces memory consumption. await foreach (var user in context.Users.AsAsyncEnumerable()) { Console.WriteLine(user.Username); } The approach mentioned above streams user data asynchronously; ideal for processing large data without worrying about server memory. C# Programming🚀 Thank you for being a part of the C# community! Before you leave: Follow us: Youtube | X | LinkedIn | Dev.to Visit our other platforms: GitHub Learn how to boost query performance, operations, and manage large datasets effectively. From basic to advanced tips for developers looking to enhance their EF Core skills. 1. Use AsNoTracking for Read-Only Scenarios AsNoTracking fetches data that is not intended to be changed in the current context. It makes queries faster and more efficient because EF Core does not need to capture tracking information. var users = context.Users.AsNoTracking().ToList(); var users = context.Users.AsNoTracking().ToList(); The aforementioned query retrieves all users from the database without any tracking overhead, which provides performance gain in data-heavy application 2. Opt for Explicit Loading Explicit loading is a process where related data is loaded from the database at a later time and not immediately with the initial query. var book = context.Books.Single(b => b.BookId == 1); context.Entry(book).Collection(b => b.Authors).Load(); var book = context.Books.Single(b => b.BookId == 1); context.Entry(book).Collection(b => b.Authors).Load(); Here, the book entity is first loaded without its related Authors. Only when explicitly called, the Authors collection is loaded into memory. 3. Use Configuration Over Conventions EF Core uses conventions based on your class definitions to configure the schema of the database. public class MyContext : DbContext { protected override void OnModelCreating(ModelBuilder modelBuilder) { modelBuilder.Entity () .Property(u => u.Username) .IsRequired() .HasMaxLength(100); } } public class MyContext : DbContext { protected override void OnModelCreating(ModelBuilder modelBuilder) { modelBuilder.Entity () .Property(u => u.Username) .IsRequired() .HasMaxLength(100); } } 4. Leverage Query Filters for Multi-Tenancy Query filters are LINQ query predicates applied to models globally in the DbContext. protected override void OnModelCreating(ModelBuilder modelBuilder) { modelBuilder.Entity ().HasQueryFilter(p => p.TenantId == _tenantId); } protected override void OnModelCreating(ModelBuilder modelBuilder) { modelBuilder.Entity ().HasQueryFilter(p => p.TenantId == _tenantId); } It filters every query to include only data relevant to a specific tenant identified by _tenantId. 5. Index Attributes to Optimize Queries Adding index attributes to properties that are frequently used in WHERE clauses or join keys can significantly improve query performance by reducing the data scan scope. public class User { [Key] public int UserId { get; set; } [Required] [MaxLength(256)] [Index] // Index attribute to improve search performance public string Username { get; set; } } public class User { [Key] public int UserId { get; set; } [Required] [MaxLength(256)] [Index] // Index attribute to improve search performance public string Username { get; set; } } By marking Username with an [Index], searches against this column are more efficient. 6. Use Batch Saves to Reduce Database Roundtrips When updating or inserting multiple entities, use batch operations to minimize the number of round trips to the database. var users = new List { new User { Username = "user1" }, new User { Username = "user2" } }; context.Users.AddRange(users); context.SaveChanges(); var users = new List { new User { Username = "user1" }, new User { Username = "user2" } }; context.Users.AddRange(users); context.SaveChanges(); It adds multiple users in one operation, optimizing connection usage. 7. Avoid N+1 Queries in EF Core The N+1 problem occurs in an application when it makes one query to retrieve the primary data and then multiple secondary queries for each piece of primary data to retrieve metadata. It can be prevented by the eager loading of data. var departments = context.Departments.Include(d => d.Employees).ToList(); var departments = context.Departments.Include(d => d.Employees).ToList(); It fetches departments along with their employees in a single query, preventing multiple queries for each department. 8. Handle Concurrency With Optimistic Concurrency It is used to handle conflicts so that a record hasn’t been modified by another transaction before updating it in the database. [ConcurrencyCheck] public string Email { get; set; } [ConcurrencyCheck] public string Email { get; set; } The attribute ensures that the Email value hasn't been modified by the different users before the current transaction has completed execution, preventing data inconsistency. 9. Use Lazy Loading Judiciously Lazy loading automatically loads data from the database the first time a navigation property is accessed. public virtual ICollection Orders { get; set; } // Lazy loading of orders public virtual ICollection Orders { get; set; } // Lazy loading of orders Here, Orders are loaded only when accessed, which helps reduce initial load time. 10. Implement Efficient Pagination Efficient pagination reduces the load on the database by retiring only a subset of the total records that are currently required. int pageNumber = 2; int pageSize = 10; var users = context.Users.OrderBy(u => u.UserId) .Skip((pageNumber - 1) * pageSize) .Take(pageSize) .ToList(); int pageNumber = 2; int pageSize = 10; var users = context.Users.OrderBy(u => u.UserId) .Skip((pageNumber - 1) * pageSize) .Take(pageSize) .ToList(); It filters out only the second page of users, limiting the result to 10 entries, which helps in handling large datasets efficiently. 11. Use Select to Shape Data Instead of fetching the complete models from the database, use Select to transform data into simpler models to reduce memory usage and improve performance. var userProfiles = context.Users .Select(u => new UserProfileDto { UserId = u.Id, Username = u.Username, Email = u.Email }) .ToList(); var userProfiles = context.Users .Select(u => new UserProfileDto { UserId = u.Id, Username = u.Username, Email = u.Email }) .ToList(); It fetches only the required fields, minimizing the size of data transferred over the network. 12. Minimize Database Hits with FirstOrDefaultAsync Use asynchronous methods like FirstOrDefaultAsync instead of FirstOrDefault for querying single objects. var user = await context.Users .Where(u => u.Username == "john.doe") .FirstOrDefaultAsync(); var user = await context.Users .Where(u => u.Username == "john.doe") .FirstOrDefaultAsync(); The non-blocking call improves the performance of your application under heavy load. 13. Pre-compiled Queries for Repeated UsePre-compile var compiledQuery = EF.CompileQuery( (MyDbContext context, int userId) => context.Users.FirstOrDefault(u => u.UserId == userId) ); // Usage var user = compiledQuery.Invoke(context, 5); var compiledQuery = EF.CompileQuery( (MyDbContext context, int userId) => context.Users.FirstOrDefault(u => u.UserId == userId) ); // Usage var user = compiledQuery.Invoke(context, 5); Useful in high-load scenarios where the same query is executed frequently. 14. Utilize Transaction Scopes for Multiple Operations Use a transaction scope to ensure multiple operations either all succeed or fail. using (var transaction = context.Database.BeginTransaction()) { try { context.Users.Add(newUser); context.SaveChanges(); context.Purchases.Add(newPurchase); context.SaveChanges(); transaction.Commit(); } catch { transaction.Rollback(); } } using (var transaction = context.Database.BeginTransaction()) { try { context.Users.Add(newUser); context.SaveChanges(); context.Purchases.Add(newPurchase); context.SaveChanges(); transaction.Commit(); } catch { transaction.Rollback(); } } It ensures data consistency across operations. 15. Optimize Model Building with OnModelCreating Use the OnModelCreating method in your DbContext to optimize model configuration, indexes, and relationships. protected override void OnModelCreating(ModelBuilder modelBuilder) { modelBuilder.Entity () .HasIndex(u => u.Email) .IsUnique(); } protected override void OnModelCreating(ModelBuilder modelBuilder) { modelBuilder.Entity () .HasIndex(u => u.Email) .IsUnique(); } It creates a unique index for the Email column, which is beneficial for performance when querying by email. 16. Implement Caching to Reduce Database Load Implement caching for data that is static to decrease database load and improve UI responsiveness. var cacheKey = "Top10Users"; var topUsers = memoryCache.GetOrCreate(cacheKey, entry => { entry.SlidingExpiration = TimeSpan.FromMinutes(10); return context.Users.OrderBy(u => u.SignupDate).Take(10).ToList(); }); var cacheKey = "Top10Users"; var topUsers = memoryCache.GetOrCreate(cacheKey, entry => { entry.SlidingExpiration = TimeSpan.FromMinutes(10); return context.Users.OrderBy(u => u.SignupDate).Take(10).ToList(); }); The above approach caches the top 10 users and refreshes the cache every 10 minutes. 17. Handle Large Data Sets with AsSplitQuery Split queries are generally used for querying huge data sets with complex includes which can be more efficient than loading all data in a single query. var orders = context.Orders .Include(o => o.Customer) .Include(o => o.OrderDetails) .AsSplitQuery() .ToList(); var orders = context.Orders .Include(o => o.Customer) .Include(o => o.OrderDetails) .AsSplitQuery() .ToList(); The method breaks down the query into multiple SQL queries which performs better than a single complex query. 18. Avoid Memory Leaks With Detach Separate entities from the DbContext when they’re not required anymore to prevent memory leaks. context.Entry(user).State = EntityState.Detached; context.Entry(user).State = EntityState.Detached; It removes the entity from the context, reduces space, and prevents potential performance issues. 19. Use Include and ThenInclude for Deep Loading For loading complex object graphs, utilize Include and ThenInclude to specify related data to be loaded from the database. var books = context.Books .Include(b => b.Author) .ThenInclude(a => a.ContactDetails) .ToList(); var books = context.Books .Include(b => b.Author) .ThenInclude(a => a.ContactDetails) .ToList(); The query mentioned above loads books, authors, and the authors’ contact details in a single operation. 20. Stream Large Data Sets With AsAsyncEnumerable To handle large data sets, use AsAsyncEnumerable to stream data asynchronously, which in turn reduces memory consumption. await foreach (var user in context.Users.AsAsyncEnumerable()) { Console.WriteLine(user.Username); } await foreach (var user in context.Users.AsAsyncEnumerable()) { Console.WriteLine(user.Username); } The approach mentioned above streams user data asynchronously; ideal for processing large data without worrying about server memory. C# Programming🚀 Thank you for being a part of the C# community! Before you leave: Follow us: Youtube | X | LinkedIn | Dev.to Visit our other platforms: GitHub Youtube X LinkedIn Dev.to GitHub