Entity Framework Lambda动态查询求助：跨表子级数据筛选

Hey Carlos, great question! Let's break this down step by step—your goal to avoid messy if-else branches and use dynamic queries with Entity Framework is totally achievable, and UNION is a solid approach here.

First: Is This Feasible? #

Absolutely! EF (especially EF Core) handles dynamic query building and UNION operations smoothly, as long as you structure your queries to return a consistent result type. This approach will keep your code clean, modular, and compliant with best practices.

Core Approach: Build Targeted Queries + Union Them #

The key idea is to create separate IQueryable instances for each parent-child pair (Table1/Children, Table2/Children, Table3/Children), apply your parent/child filters to each, then combine them with UNION (or Concat if you don't need deduplication).

Let's walk through a concrete example:

Step 1: Define a Shared DTO for Results

Since you're pulling children from three different tables, create a unified DTO to ensure all queries return the same structure:

public class ChildResultDto
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int ParentId { get; set; }
    public string ParentType { get; set; } // Optional: Track which parent table the child came from
    // Add other common child properties here
}

Step 2: Create a Filter Parameter Class

Bundle all your page filters into a single class for clarity:

public class ChildFilterParams
{
    // Parent-level filters (per table)
    public string? Parent1Category { get; set; }
    public int? Parent2MinValue { get; set; }
    public DateTime? Parent3StartDate { get; set; }
    
    // Child-level filters (applied to all children)
    public string? ChildNameContains { get; set; }
    public bool? IsChildActive { get; set; }
}

Step 3: Build + Union Queries

Use Lambda expressions to apply filters dynamically, then merge the results:

public IQueryable<ChildResultDto> GetFilteredChildren(ChildFilterParams filters)
{
    // Query for Table1's children
    var table1Query = _context.ParentTable1
        // Apply Table1 parent filters (only if the parameter is provided)
        .Where(p => filters.Parent1Category == null || p.Category == filters.Parent1Category)
        // Navigate to children
        .SelectMany(p => p.Children)
        // Apply shared child filters
        .Where(c => filters.ChildNameContains == null || c.Name.Contains(filters.ChildNameContains))
        .Where(c => filters.IsChildActive == null || c.IsActive == filters.IsChildActive.Value)
        // Map to shared DTO
        .Select(c => new ChildResultDto
        {
            Id = c.Id,
            Name = c.Name,
            ParentId = c.ParentTable1Id,
            ParentType = "Table1"
        });

    // Repeat for Table2's children
    var table2Query = _context.ParentTable2
        .Where(p => filters.Parent2MinValue == null || p.Value >= filters.Parent2MinValue.Value)
        .SelectMany(p => p.Children)
        .Where(c => filters.ChildNameContains == null || c.Name.Contains(filters.ChildNameContains))
        .Where(c => filters.IsChildActive == null || c.IsActive == filters.IsChildActive.Value)
        .Select(c => new ChildResultDto
        {
            Id = c.Id,
            Name = c.Name,
            ParentId = c.ParentTable2Id,
            ParentType = "Table2"
        });

    // Repeat for Table3's children
    var table3Query = _context.ParentTable3
        .Where(p => filters.Parent3StartDate == null || p.CreatedDate >= filters.Parent3StartDate.Value)
        .SelectMany(p => p.Children)
        .Where(c => filters.ChildNameContains == null || c.Name.Contains(filters.ChildNameContains))
        .Where(c => filters.IsChildActive == null || c.IsActive == filters.IsChildActive.Value)
        .Select(c => new ChildResultDto
        {
            Id = c.Id,
            Name = c.Name,
            ParentId = c.ParentTable3Id,
            ParentType = "Table3"
        });

    // Combine all queries with UNION (removes duplicates)
    return table1Query.Union(table2Query).Union(table3Query);
}

Pro Tips to Clean Up Further #

• Extract Filter Logic into Extension Methods: If you have repeated filter logic (like the child filters), turn them into reusable extensions:

  public static IQueryable<Child1> ApplyChildFilters(this IQueryable<Child1> query, ChildFilterParams filters)
  {
      if (filters.ChildNameContains != null)
          query = query.Where(c => c.Name.Contains(filters.ChildNameContains));
      if (filters.IsChildActive.HasValue)
          query = query.Where(c => c.IsActive == filters.IsChildActive.Value);
      return query;
  }
  // Create similar extensions for Child2 and Child3
  

  This cuts down on duplicate code across your three queries.

• Avoid Client-Side Evaluation: Ensure all filters are applied to IQueryable (not IEnumerable) so EF translates everything to SQL—this keeps performance high.

• Test Generated SQL: Use EF Core's logging to verify the generated SQL includes your filters and UNION clauses, confirming everything runs on the database server.

Final Notes #

This approach eliminates the need for sprawling if-else branches, keeps your code modular, and leverages EF's ability to translate complex queries to efficient SQL. It's a clean, maintainable solution for your dynamic filtering needs.

内容的提问来源于stack exchange，提问作者Carlos