NeuroAgent

How to Avoid Duplicate Code in Entity Framework Include Methods

Learn proven techniques to eliminate duplicate code when using Entity Framework's .Include method across multiple classes. Discover extension methods, repository patterns, and best practices for cleaner, maintainable queries.

#code-reuse#csharp-programming#database-optimization#dotnet-development#entity-framework#query-optimization#software-architecture
Question

How can I avoid duplicate code when using Entity Framework’s .Include method across multiple classes?

I’m working with Entity Framework and have multiple classes that require similar .Include and .ThenInclude chains. For example:

csharp
query = (from n in currentDBContext.FBBuchungenCollection
              .Include(x => x.BelegHerkunft)
              .Include(x => x.Buchungsordner)
              .Include(x => x.Buchungsperiode).ThenInclude(x => x.Geschaeftsjahr)
              .Include(x => x.BuchungsUser)
              .Include(x => x.Erfassungsart)
              .Include(x => x.ErstellUser)
              .Include(x => x.Mandant).ThenInclude(x => x.Haupt Adresse)
              .Include(x => x.StornoUser)
              .Include(x => x.Teilbuchungen).ThenInclude(x => x.FremdWaehrung)
              .Include(x => x.Teilbuchungen).ThenInclude(x => x.KKArt)
              .Include(x => x.Teilbuchungen).ThenInclude(x => x.KKKonto)                         
              .Include(x => x.Teilbuchungen).ThenInclude(x => x.Konto)
              .Include(x => x.Teilbuchungen).ThenInclude(x => x.KoReVerteilung).ThenInclude(x => x.Periodenverteilungen).ThenInclude(x => x.Kontierungen).ThenInclude(x => x.Kontierungsangaben).ThenInclude(x => x.KontierungsangabenKTR).ThenInclude(x => x.Kostentraeger)
              .Include(x => x.Teilbuchungen).ThenInclude(x => x.KoReVerteilung).ThenInclude(x => x.Periodenverteilungen).ThenInclude(x => x.Kontierungen).ThenInclude(x => x.Kontierungsangaben).ThenInclude(x => x.KontierungsangabenKTR).ThenInclude(x => x.KostentraegerArt)                         
              .Include(x => x.Teilbuchungen).ThenInclude(x => x.OffenerPosten).ThenInclude(x => x.Kostenstelle)
              .Include(x => x.Teilbuchungen).ThenInclude(x => x.OffenerPosten).ThenInclude(x => x.Kostentraeger)
              .Include(x => x.Teilbuchungen).ThenInclude(x => x.OffenerPosten).ThenInclude(x => x.Mandant)
              .Include(x => x.Teilbuchungen).ThenInclude(x => x.OffenerPosten).ThenInclude(x => x.Sachkonto)
              .Include(x => x.Teilbuchungen).ThenInclude(x => x.Steuerschluessel)
              .Include(x => x.Teilbuchungen).ThenInclude(x => x.VerweisTeilbuchung)
          select n);

And another example:

csharp
query = (from n in currentDBContext.KKKontoCollection
             .TagWith("KKKonto.BuildQuery")
             .Include(x => x.Mandant).ThenInclude(x => x.Haupt Adresse)
             .Include(x => x.KKArt)
             .Include(x => x.Land)
             .Include(x => x.Mahnkategorie)
             .Include(x => x.SammelKonto)
             .Include(x => x.Zahlungsbedingung)
         select n);

Notice that both examples include the same line:

csharp
.Include(x => x.Mandant).ThenInclude(x => x.Haupt Adresse)

Is there a way to create reusable methods for common include patterns? For example, a method like AddIncludeMandant() that I can use across different classes to avoid this duplication?

What are the best practices for organizing Entity Framework queries to minimize code duplication while maintaining readability and performance?

NeuroAgent

You can avoid duplicate code when using Entity Framework’s .Include method by creating reusable extension methods and organizing your query logic effectively. The most common approach involves creating extension methods that encapsulate common include patterns, allowing you to reuse the same navigation chains across multiple queries without duplicating the code.

Contents

Extension Methods for Common Include Patterns

The most direct solution to your problem is to create extension methods that encapsulate your common include chains. This approach allows you to reuse the same navigation patterns across different DbSet queries.

Basic Extension Method Approach

csharp
public static class EntityFrameworkExtensions
{
    public static IQueryable<FBBuchungen> IncludeStandardBuchungIncludes(this IQueryable<FBBuchungen> query)
    {
        return query
            .Include(x => x.BelegHerkunft)
            .Include(x => x.Buchungsordner)
            .Include(x => x.Buchungsperiode).ThenInclude(x => x.Geschaeftsjahr)
            .Include(x => x.BuchungsUser)
            .Include(x => x.Erfassungsart)
            .Include(x => x.ErstellUser)
            .Include(x => x.Mandant).ThenInclude(x => x.HauptAdresse)
            .Include(x => x.StornoUser)
            .Include(x => x.Teilbuchungen).ThenInclude(x => x.FremdWaehrung)
            .Include(x => x.Teilbuchungen).ThenInclude(x => x.KKArt)
            .Include(x => x.Teilbuchungen).ThenInclude(x => x.KKKonto)
            .Include(x => x.Teilbuchungen).ThenInclude(x => x.Konto)
            .Include(x => x.Teilbuchungen).ThenInclude(x => x.KoReVerteilung).ThenInclude(x => x.Periodenverteilungen)
            .Include(x => x.Teilbuchungen).ThenInclude(x => x.OffenerPosten).ThenInclude(x => x.Kostenstelle)
            .Include(x => x.Teilbuchungen).ThenInclude(x => x.OffenerPosten).ThenInclude(x => x.Kostentraeger)
            .Include(x => x.Teilbuchungen).ThenInclude(x => x.OffenerPosten).ThenInclude(x => x.Mandant)
            .Include(x => x.Teilbuchungen).ThenInclude(x => x.OffenerPosten).ThenInclude(x => x.Sachkonto)
            .Include(x => x.Teilbuchungen).ThenInclude(x => x.Steuerschluessel)
            .Include(x => x.Teilbuchungen).ThenInclude(x => x.VerweisTeilbuchung);
    }

    public static IQueryable<KKKonto> IncludeStandardKontoIncludes(this IQueryable<KKKonto> query)
    {
        return query
            .Include(x => x.Mandant).ThenInclude(x => x.HauptAdresse)
            .Include(x => x.KKArt)
            .Include(x => x.Land)
            .Include(x => x.Mahnkategorie)
            .Include(x => x.SammelKonto)
            .Include(x => x.Zahlungsbedingung);
    }
}

Usage Example

csharp
// Instead of the long chain, use your extension method
var query = currentDBContext.FBBuchungenCollection
    .IncludeStandardBuchungIncludes()
    .Where(x => x.SomeCondition);

// For KKKonto
var kontoQuery = currentDBContext.KKKontoCollection
    .IncludeStandardKontoIncludes()
    .Where(x => x.SomeCondition);

Modular Approach with Composable Extensions

For even better organization, you can break down your includes into smaller, composable methods:

csharp
public static class EntityFrameworkExtensions
{
    // Common includes used across multiple entity types
    public static IQueryable<T> IncludeCommonNavigation<T>(this IQueryable<T> query) where T : class
    {
        var type = typeof(T);
        if (type == typeof(FBBuchungen))
        {
            return (query as IQueryable<FBBuchungen>)
                .IncludeStandardBuchungIncludes() as IQueryable<T>;
        }
        else if (type == typeof(KKKonto))
        {
            return (query as IQueryable<KKKonto>)
                .IncludeStandardKontoIncludes() as IQueryable<T>;
        }
        return query;
    }

    // Add specific includes for common navigation patterns
    public static IQueryable<T> IncludeMandantWithDetails<T>(this IQueryable<T> query) where T : class
    {
        return query.Include("Mandant.HauptAdresse") as IQueryable<T>;
    }
}

According to the Reddit discussion on .NET Core query reuse, “It provides more flexibility if the extension methods are on an IQueryable rather than the DbSet, since this allows multiple reusable queries to be composed together.”

Repository Pattern with Reusable Includes

Another approach is to implement a repository pattern that encapsulates common include logic:

csharp
public interface IRepository<T> where T : class
{
    IQueryable<T> GetAllIncluding(params Expression<Func<T, object>>[] includeProperties);
    IQueryable<T> GetAll();
}

public class GenericRepository<T> : IRepository<T> where T : class
{
    protected readonly DbContext _context;
    protected readonly DbSet<T> _dbSet;

    public GenericRepository(DbContext context)
    {
        _context = context;
        _dbSet = context.Set<T>();
    }

    public virtual IQueryable<T> GetAllIncluding(params Expression<Func<T, object>>[] includeProperties)
    {
        IQueryable<T> query = _dbSet;

        foreach (var includeProperty in includeProperties)
        {
            query = query.Include(includeProperty);
        }

        return query;
    }

    public virtual IQueryable<T> GetAll()
    {
        return _dbSet;
    }
}

// Specific repository with predefined includes
public class BuchungRepository : GenericRepository<FBBuchungen>
{
    public BuchungRepository(DbContext context) : base(context) { }

    public IQueryable<FBBuchungen> GetAllWithStandardIncludes()
    {
        return _dbSet
            .Include(x => x.BelegHerkunft)
            .Include(x => x.Mandant).ThenInclude(x => x.HauptAdresse)
            // ... other includes
            .Include(x => x.Teilbuchungen).ThenInclude(x => x.KoReVerteilung);
    }
}

As mentioned in the StackOverflow discussion about repository patterns, having centralized include logic helps avoid duplication while maintaining clean separation of concerns.

DbContext Helper Classes

You can create helper classes that generate include expressions dynamically:

csharp
public static class DbContextHelper
{
    public static Func<IQueryable<T>, IQueryable<T>> GetNavigations<T>() where T : class
    {
        var type = typeof(T);
        
        if (type == typeof(FBBuchungen))
        {
            return query => query
                .Include(x => x.Mandant).ThenInclude(x => x.HauptAdresse)
                .Include(x => x.Teilbuchungen).ThenInclude(x => x.OffenerPosten)
                // ... other includes
                ;
        }
        else if (type == typeof(KKKonto))
        {
            return query => query
                .Include(x => x.Mandant).ThenInclude(x => x.HauptAdresse)
                .Include(x => x.KKArt)
                // ... other includes
                ;
        }
        
        return query => query;
    }
}

// Usage
var includes = DbContextHelper.GetNavigations<FBBuchungen>();
var query = includes(currentDBContext.FBBuchungenCollection);

Third-Party Solutions

Several third-party libraries can help with query reuse:

NeinLinq.EntityFrameworkCore

As mentioned in the StackOverflow answer about extension methods, NeinLinq “extends LINQ providers such as Entity Framework to enable reusing functions, rewriting queries, and building dynamic queries using translatable predicates and selectors.”

Expressionify

The Expressionify GitHub repository provides a way to use extension methods in Entity Framework Core queries, though as noted in the documentation, “this forces Entity Framework to run the query in memory, rather than in the database.”

Entity Framework Extensions

While primarily focused on performance, Entity Framework Extensions also provides utilities for query management.

Best Practices for Organization

1. Hierarchical Organization

Organize your includes hierarchically:

csharp
public static class EntityFrameworkExtensions
{
    // Level 1: Basic includes
    public static IQueryable<T> IncludeBasicNavigation<T>(this IQueryable<T> query) where T : class
    {
        return query
            .Include(x => x.Mandant)
            .Include(x => x.ErstellUser);
    }

    // Level 2: Detailed includes
    public static IQueryable<T> IncludeDetailedNavigation<T>(this IQueryable<T> query) where T : class
    {
        return query
            .IncludeBasicNavigation()
            .Include(x => x.Mandant).ThenInclude(x => x.HauptAdresse)
            .Include(x => x.Teilbuchungen);
    }

    // Level 3: Complex includes
    public static IQueryable<T> IncludeComplexNavigation<T>(this IQueryable<T> query) where T : class
    {
        return query
            .IncludeDetailedNavigation()
            .Include(x => x.Teilbuchungen).ThenInclude(x => x.KoReVerteilung).ThenInclude(x => x.Periodenverteilungen);
    }
}

2. Fluent Interface Pattern

Create a fluent interface for building complex queries:

csharp
public static class QueryBuilder
{
    public static EntityQuery<T> StartWith<T>(IQueryable<T> query) where T : class
    {
        return new EntityQuery<T>(query);
    }
}

public class EntityQuery<T> where T : class
{
    private readonly IQueryable<T> _query;

    public EntityQuery(IQueryable<T> query)
    {
        _query = query;
    }

    public EntityQuery<T> IncludeMandant()
    {
        _query = _query.Include(x => x.Mandant).ThenInclude(x => x.HauptAdresse);
        return this;
    }

    public EntityQuery<T> IncludeTeilbuchungenWithOffenePosten()
    {
        _query = _query
            .Include(x => x.Teilbuchungen)
            .Include(x => x.Teilbuchungen).ThenInclude(x => x.OffenerPosten);
        return this;
    }

    public IQueryable<T> Build()
    {
        return _query;
    }
}

// Usage
var query = QueryBuilder.StartWith(currentDBContext.FBBuchungenCollection)
    .IncludeMandant()
    .IncludeTeilbuchungenWithOffenePosten()
    .Build();

3. Tagged Queries for Debugging

As shown in your example, use TagWith to identify queries:

csharp
public static class EntityFrameworkExtensions
{
    public static IQueryable<T> TagWithStandardIncludes<T>(this IQueryable<T> query) where T : class
    {
        return query.TagWith("StandardIncludes_" + typeof(T).Name);
    }
}

// Usage
var query = currentDBContext.FBBuchungenCollection
    .TagWithStandardIncludes()
    .IncludeStandardBuchungIncludes();

Performance Considerations

1. Avoid Over-Including

Be careful not to include more data than necessary. As noted in the Microsoft documentation, “Specifies related entities to include in the query results. The navigation property to be included is specified starting with the type of entity being queried (TEntity).”

2. Use Selective Includes

Create multiple include methods for different scenarios:

csharp
public static class EntityFrameworkExtensions
{
    public static IQueryable<FBBuchungen> IncludeSummaryOnly(this IQueryable<FBBuchungen> query)
    {
        return query
            .Include(x => x.Mandant)
            .Include(x => x.Buchungsperiode);
    }

    public static IQueryable<FBBuchungen> IncludeFullDetails(this IQueryable<FBBuchungen> query)
    {
        return query.IncludeStandardBuchungIncludes();
    }
}

3. Monitor Query Performance

Use logging to monitor your queries:

csharp
// In your DbContext
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder.LogTo(Console.WriteLine, LogLevel.Information)
        .EnableSensitiveDataLogging();
}

Sources

  1. r/dotnet on Reddit: How do you reuse queries in .NET Core with Entity Framework without a “clunky” repository?
  2. StackOverflow: How to write Repository method for .ThenInclude in EF Core 2
  3. StackOverflow: Entity Framework Core - Use Extension Methods Inside Queryable
  4. Microsoft Learn: QueryableExtensions.Include Method (EF6)
  5. Microsoft Learn: EntityFrameworkQueryableExtensions.Include Method (EF Core)
  6. GitHub - ClaveConsulting/Expressionify: Use extension methods in Entity Framework Core queries
  7. Entity Framework Extensions Overview

Conclusion

To avoid duplicate code when using Entity Framework’s .Include method, consider these key strategies:

  1. Create extension methods for common include patterns, allowing you to reuse the same navigation chains across multiple queries
  2. Use a repository pattern to centralize include logic and maintain separation of concerns
  3. Implement hierarchical organization of includes with basic, detailed, and complex levels
  4. Consider third-party libraries like NeinLinq for advanced query building capabilities
  5. Use tagged queries for better debugging and monitoring
  6. Be selective about includes to avoid performance issues

The extension method approach is generally the most straightforward and maintains readability while eliminating duplication. Start with simple reusable include methods and gradually build up to more complex composable patterns as your application grows. Remember to monitor query performance and adjust your include strategies based on actual usage patterns.

How do I create reusable extension methods for Entity Framework complex navigation properties?What are the performance implications of using multiple .ThenInclude statements in Entity Framework queries?How can I implement a repository pattern with Entity Framework to centralize include logic?What are the best practices for organizing Entity Framework queries in large applications?How do I debug and optimize Entity Framework queries with multiple includes?When should I avoid using .Include in Entity Framework and use projection instead?
Ask NeuroAgent