NeuroAgent

Eliminate Duplicate Code with EF Core Include Methods

Learn how to eliminate duplicate code when using Entity Framework's Include method across multiple classes. Discover reusable extension methods, expression tree solutions, and best practices for maintaining clean EF queries. Start writing more maintainable code today!

#code-duplication#csharp-programming#ef-core#entity-framework#query-optimization
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 use the .Include method to load related entities. Each class has a similar pattern of includes, leading to code duplication. 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);

Another example from a different class:

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);

As you can see, some includes are repeated across different classes, such as:

csharp
.Include(x => x.Mandant).ThenInclude(x => x.Haupt_Adresse)

Is there a way to avoid this duplication? I’m looking for a solution that would allow me to create reusable methods like AddIncludeMandant() that I can use in any class that needs these includes.

NeuroAgent

Entity Framework’s Include method often leads to repetitive code when similar related entities need to be loaded across multiple queries. The most effective solution is to create reusable extension methods that encapsulate common include patterns. Here are several approaches to eliminate this duplication:

Creating Reusable Extension Methods

The recommended approach is to create extension methods on IQueryable<T> that can be composed together. This provides maximum flexibility and reusability across your application.

csharp
public static class EntityFrameworkExtensions
{
    // Generic method to add common includes for Mandant
    public static IQueryable<T> IncludeMandant<T>(this IQueryable<T> query) where T : class
    {
        // This would require runtime type checking or a base interface
        // Let's create more specific methods instead
        return query;
    }

    // Specific methods for common patterns
    public static IQueryable<FBBuchungen> IncludeFullBuchungData(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.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);
    }

    // Common includes that can be reused across different entities
    public static IQueryable<Mandant> IncludeMandantDetails(this IQueryable<Mandant> query)
    {
        return query.Include(x => x.Haupt_Adresse);
    }

    public static IQueryable<KKKonto> IncludeKKKontoDetails(this IQueryable<KKKonto> query)
    {
        return query
            .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);
    }
}

Now you can simplify your queries significantly:

csharp
// Before
query = (from n in currentDBContext.FBBuchungenCollection
              .Include(x => x.BelegHerkunft)
              .Include(x => x.Buchungsordner)
              .Include(x => x.Buchungsperiode).ThenInclude(x => x.Geschaeftsjahr)
              // ... many more includes
          select n);

// After
query = (from n in currentDBContext.FBBuchungenCollection
              .IncludeFullBuchungData()
          select n);

// For KKKonto
query = (from n in currentDBContext.KKKontoCollection
             .TagWith("KKKonto.BuildQuery")
             .IncludeKKKontoDetails()
         select n);

Specific Entity Extension Methods

Create extension methods for each entity type that encapsulates their specific include requirements. This approach is shown in the Stack Overflow example where they created PopulateWithTeacherAndLicense for IQueryable<Subject>.

csharp
public static class EntityFrameworkExtensions
{
    // Methods for FBBuchungen
    public static IQueryable<FBBuchungen> IncludeStandardBuchungData(this IQueryable<FBBuchungen> query)
    {
        return query
            .Include(x => x.BelegHerkunft)
            .Include(x => x.Buchungsordner)
            .Include(x => x.Buchungsperiode)
            .Include(x => x.BuchungsUser)
            .Include(x => x.Erfassungsart)
            .Include(x => x.ErstellUser)
            .Include(x => x.StornoUser);
    }

    public static IQueryable<FBBuchungen> IncludeTeilbuchungenWithDetails(this IQueryable<FBBuchungen> query)
    {
        return query
            .Include(x => x.Teilbuchungen)
            .ThenInclude(x => x.FremdWaehrung)
            .ThenInclude(x => x.KKArt)
            .ThenInclude(x => x.KKKonto)
            .ThenInclude(x => x.Konto)
            .ThenInclude(x => x.KoReVerteilung)
                .ThenInclude(x => x.Periodenverteilungen)
                .ThenInclude(x => x.Kontierungen)
                .ThenInclude(x => x.Kontierungsangaben)
                .ThenInclude(x => x.KontierungsangabenKTR);
    }

    // Methods for KKKonto
    public static IQueryable<KKKonto> IncludeStandardKKKontoData(this IQueryable<KKKonto> query)
    {
        return query
            .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);
    }

    // Common includes that can be shared
    public static IQueryable<T> IncludeMandantAddress<T>(this IQueryable<T> query) where T : class, IMandantEntity
    {
        // This assumes an interface IMandantEntity with a Mandant property
        return query.Include(x => x.Mandant).ThenInclude(x => x.Haupt_Adresse);
    }
}

To use the shared method, you would need to create an interface:

csharp
public interface IMandantEntity
{
    Mandant Mandant { get; set; }
}

// Your entities would implement this
public class FBBuchungen : IMandantEntity
{
    public Mandant Mandant { get; set; }
    // other properties...
}

public class KKKonto : IMandantEntity
{
    public Mandant Mandant { get; set; }
    // other properties...
}

Expression Tree Solutions

For more complex scenarios where you need dynamic includes, consider using expression trees. As mentioned in the Stack Overflow answer, expression trees provide a way to create reusable selectors that EF can translate to SQL.

csharp
public static class EntityFrameworkExtensions
{
    // Generic method to add includes based on property selectors
    public static IQueryable<T> Includes<T>(
        this IQueryable<T> query,
        params Expression<Func<T, object>>[] includes) where T : class
    {
        foreach (var include in includes)
        {
            query = query.Include(include);
        }
        return query;
    }

    // More complex example with nested includes
    public static IQueryable<FBBuchungen> IncludeBuchungenWithExpression(
        this IQueryable<FBBuchungen> query,
        Expression<Func<FBBuchungen, object>> includeSelector)
    {
        // This would need more complex expression tree manipulation
        // For now, we'll show a simpler approach
        return query;
    }
}

Usage:

csharp
query = currentDBContext.FBBuchungenCollection
    .Includes(
        x => x.BelegHerkunft,
        x => x.Buchungsordner,
        x => x.Mandant.Haupt_Adresse,
        x => x.Teilbuchungen
    );

Repository Pattern Integration

You can integrate these extension methods into a repository pattern for better organization. As mentioned in the Stack Overflow answer, you can create helper methods that manage includes.

csharp
public class GenericRepository<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 async Task<IEnumerable<T>> GetAllAsync(
        Expression<Func<T, bool>> filter = null,
        Func<IQueryable<T>, IOrderedQueryable<T>> orderBy = null,
        string includeProperties = null)
    {
        IQueryable<T> query = _dbSet;

        if (filter != null)
        {
            query = query.Where(filter);
        }

        if (includeProperties != null)
        {
            foreach (var includeProperty in includeProperties.Split(
                new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries))
            {
                query = query.Include(includeProperty);
            }
        }

        if (orderBy != null)
        {
            return await orderBy(query).ToListAsync();
        }
        else
        {
            return await query.ToListAsync();
        }
    }
}

// Specialized repositories can use the extension methods
public class BuchungenRepository : GenericRepository<FBBuchungen>
{
    public BuchungenRepository(DbContext context) : base(context) { }

    public async Task<IEnumerable<FBBuchungen>> GetFullBuchungenAsync()
    {
        return await _dbSet.IncludeFullBuchungData().ToListAsync();
    }
}

Best Practices and Considerations

Performance Considerations

Be mindful of the performance implications of extensive includes, as mentioned in the JetBrains blog. Consider using AsSplitQuery() when dealing with many-to-many relationships to avoid data duplication.

csharp
query = currentDBContext.FBBuchungenCollection
    .AsSplitQuery() // Reduces data duplication
    .IncludeFullBuchungData();

Composable Queries

As noted in the Reddit discussion, keeping extension methods on IQueryable rather than DbSet allows for better composition:

csharp
// Good: Extension method on IQueryable
public static IQueryable<T> IncludeBasicData<T>(this IQueryable<T> query) where T : class
{
    return query.Include(x => x.BasicProperty);
}

// This allows composition
var result = context.Entities
    .Where(x => x.IsActive)
    .IncludeBasicData()
    .IncludeAdvancedData();

Third-Party Libraries

For more advanced scenarios, consider libraries like NeinLinq.EntityFrameworkCore mentioned in the search results, which enable reuse of functions in EF queries.

csharp
// Using NeinLinq to enable method calls in queries
var users = await db.Users
    .Where(user => user.IsOver18()) // This would normally not work
    .ToListAsync();

Testing and Maintainability

When creating these extension methods, ensure they are:

  • Testable: Write unit tests for your extension methods
  • Documented: Use XML documentation to explain what each method includes
  • Versionable: Consider using semantic versioning if these are shared across projects
csharp
/// <summary>
/// Includes all standard Buchungen data with full navigation properties
/// </summary>
/// <param name="query">The source query</param>
/// <returns>Query with includes applied</returns>
public static IQueryable<FBBuchungen> IncludeFullBuchungData(this IQueryable<FBBuchungen> query)
{
    // Implementation...
}

By implementing these patterns, you can significantly reduce code duplication while maintaining clean, maintainable, and performant Entity Framework queries. The key is to identify common include patterns and encapsulate them in reusable extension methods that can be composed together as needed.

Sources

  1. Entity Framework Core - Call A Populate Extension Method Inside Include - Stack Overflow
  2. Entity Framework Extensions Overview
  3. Entity Framework Core 5 - Pitfalls To Avoid and Ideas to Try | The .NET Tools Blog
  4. How do you reuse queries in .NET Core with Entity Framework without a “clunky” repository? - Reddit
  5. Entity Framework Core - Use Extension Methods Inside Queryable - Stack Overflow
  6. EntityFrameworkQueryableExtensions.Include Method - Microsoft Learn
  7. How to write Repository method for .ThenInclude in EF Core 2 - Stack Overflow

Conclusion

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

  1. Create extension methods on IQueryable to encapsulate common include patterns, allowing for flexible composition across different queries.

  2. Build both entity-specific and shared extension methods - entity-specific methods for complex include chains and shared methods for common patterns like IncludeMandantAddress().

  3. Consider using expression trees for dynamic include scenarios that need runtime flexibility.

  4. Integrate with repository pattern by including these extension methods in specialized repositories for better organization.

  5. Follow performance best practices by using AsSplitQuery() for complex relationships and being mindful of over-fetching data.

The approach transforms verbose, repetitive code like your original examples into clean, reusable methods like .IncludeFullBuchungData() and .IncludeKKKontoDetails(), dramatically improving maintainability while preserving all the functionality of your original queries.

How to create reusable extension methods for Entity Framework Include chains?What are the performance implications of extensive Include methods in Entity Framework?How to implement repository pattern with Entity Framework to reduce code duplication?When should I use AsSplitQuery() with Entity Framework Include methods?How to test Entity Framework queries that use custom extension methods?
Ask NeuroAgent