Improving Entity Framework performance

Microsoft's Entity Framework is an extended ORM (Object-Relational Mapper) that separates the object model of an application from its data model. It is an open-source framework for ADO.NET and is included as part of the .NET Framework.

Is Your Entity Data Model a Single Unit of Work?

When creating an Entity Data Model (EDM), it should represent a single unit of work rather than the entire database, especially if the database contains many unrelated or unnecessary objects. Including unnecessary objects in the EDM can degrade performance by consuming excess memory. To optimize performance, it's recommended to break a large EDM into smaller models, each representing a specific unit of work.

1. Disable Change tracking

If you only need to retrieve data and don’t need to track changes for updates, disable change tracking to improve performance. This can be done with AsNoTracking() in the query.

For example:

var customers = context.Customers.AsNoTracking().ToList();

2. Pre-generate Views

Creating an ObjectContext is resource-intensive because it involves loading and validating metadata. To reduce response time, it's recommended to use pre-generated views.

The Entity Framework runtime generates classes (views) when the object context is first created, but you can pre-generate these views using the EdmGen.exe tool or T4 templates. If the schema changes, the views must be regenerated using the /mode:ViewGeneration flag. Pre-generating views is also possible with a code-first approach to improve performance.

Run the following command to pre-generate views:

EdmGen.exe /mode:ViewGeneration /out:ViewsFile.cs /connection:yourConnectionString

3. Disable Auto Detection of Changes

Entity Framework tracks changes to entities by comparing their old and new values when methods like Find(), Remove(), Add(), Attach(), and SaveChanges() are called. This change detection process can be costly, especially with large sets of data, potentially degrading performance.

To optimize performance, you can disable change detection, ideally within a try/catch block, and re-enable it in the finally block.

For example:

context.ChangeTracker.AutoDetectChangesEnabled = false;
try
{
    // Perform operations
}
finally
{
    context.ChangeTracker.AutoDetectChangesEnabled = true;
}

4. Use Projections

Select only the fields you need to avoid retrieving unnecessary data. This reduces memory usage and query execution time.

For example:

var customers = context.Customers
                       .Where(c => c.City == "Seattle")
                       .Select(c => new { c.CustomerID, c.ContactName })
                       .ToList();

5. Paging and Select Only Required Data

Use paging to fetch only the data you need, avoiding the retrieval of large datasets.

For example:

int pageSize = 25, startingPageIndex = 1;
var customers = context.Customers
                       .Skip(startingPageIndex * pageSize)
                       .Take(pageSize)
                       .ToList();

6. Avoid Unnecessary LINQ Operations

Avoid using Contains() in LINQ queries for large datasets and retrieve only the required number of records.

For example:

var customers = context.Customers
                       .Where(c => c.City == "Seattle")
                       .Take(50)
                       .ToList();

7. Use Indexes

Create indexes on frequently queried entities to improve database query performance. You can create indexes using the CreateIndex() method.

For example, Using the Fluent API in Entity Framework to add an index to a property:

public class MyContext : DbContext
{
    public DbSet<Customer> Customers { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Customer>()
            .Property(c => c.LastName)
            .HasColumnAnnotation("Index", new IndexAnnotation(new IndexAttribute("IX_LastName") { IsUnique = false }));

        base.OnModelCreating(modelBuilder);
    }
}

In this example, an index is created on the LastName column of the Customer entity. Indexes help optimize queries filtering or sorting by this property.