如何根据列名数组动态创建IQueryable<T>（含关联实体）

To dynamically build an IQueryable<Country> that selects specific properties (including nested navigation properties like Cities.Name) based on a string array, you can use expression trees to construct the projection at runtime. Here's a complete working solution tailored to your needs:

Step 1: Dynamic Query Builder Method #

This helper method parses your column array, builds the necessary expression trees, and returns the dynamically projected query:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;

public static class DynamicQueryHelper
{
    public static IQueryable<Country> BuildDynamicCountryQuery(YourDbContextType context, string[] columns)
    {
        // Group columns by their root property (e.g., "Cities.Name" → root is "Cities")
        var columnGroups = columns
            .Select(col => col.Split('.'))
            .GroupBy(parts => parts[0])
            .ToDictionary(
                group => group.Key,
                group => group.Select(parts => string.Join('.', parts.Skip(1)))
                              .Where(nested => !string.IsNullOrEmpty(nested))
                              .ToList()
            );

        // Parameter expression for the Country entity in the query
        var countryParam = Expression.Parameter(typeof(Country), "co");
        var countryBindings = new List<MemberBinding>();

        foreach (var group in columnGroups)
        {
            var rootPropName = group.Key;
            var nestedProps = group.Value;
            var countryProp = typeof(Country).GetProperty(rootPropName);

            if (countryProp == null)
                continue; // Skip invalid properties

            // Handle navigation properties (like Cities, which is ICollection<City>)
            if (countryProp.PropertyType.IsGenericType && 
                countryProp.PropertyType.GetGenericTypeDefinition() == typeof(ICollection<>))
            {
                var elementType = countryProp.PropertyType.GetGenericArguments()[0];
                if (elementType != typeof(City))
                    continue; // Only handle City navigation for this example

                // Build projection for City entities
                var cityParam = Expression.Parameter(typeof(City), "c");
                var cityBindings = new List<MemberBinding>();

                foreach (var nestedProp in nestedProps)
                {
                    var cityProperty = typeof(City).GetProperty(nestedProp);
                    if (cityProperty != null)
                    {
                        var cityPropAccess = Expression.Property(cityParam, cityProperty);
                        cityBindings.Add(Expression.Bind(cityProperty, cityPropAccess));
                    }
                }

                // Fallback: If no nested properties are specified for Cities, include default fields (Id, Name)
                if (!cityBindings.Any())
                {
                    var cityIdProp = typeof(City).GetProperty("Id");
                    var cityNameProp = typeof(City).GetProperty("Name");
                    if (cityIdProp != null)
                        cityBindings.Add(Expression.Bind(cityIdProp, Expression.Property(cityParam, cityIdProp)));
                    if (cityNameProp != null)
                        cityBindings.Add(Expression.Bind(cityNameProp, Expression.Property(cityParam, cityNameProp)));
                }

                // Create the MemberInit expression for City
                var cityInit = Expression.MemberInit(Expression.New(typeof(City)), cityBindings);
                var citySelectLambda = Expression.Lambda<Func<City, City>>(cityInit, cityParam);

                // Apply Select to the Cities navigation property
                var citiesAccess = Expression.Property(countryParam, countryProp);
                var selectMethod = typeof(Enumerable).GetMethods()
                    .First(m => m.Name == "Select" && m.GetParameters().Length == 2)
                    .MakeGenericMethod(typeof(City), typeof(City));
                var citiesSelectCall = Expression.Call(selectMethod, citiesAccess, citySelectLambda);

                // Add the Cities projection to the Country bindings
                countryBindings.Add(Expression.Bind(countryProp, citiesSelectCall));
            }
            // Handle scalar properties (like Id, Name on Country)
            else
            {
                var propAccess = Expression.Property(countryParam, countryProp);
                countryBindings.Add(Expression.Bind(countryProp, propAccess));
            }
        }

        // Create the final projection for Country
        var countryInit = Expression.MemberInit(Expression.New(typeof(Country)), countryBindings);
        var selectLambda = Expression.Lambda<Func<Country, Country>>(countryInit, countryParam);

        return context.Countries.Select(selectLambda);
    }
}

Step 2: Usage Example #

Call the method with your column array to get the dynamic query that matches your hardcoded example:

string[] columns = new string[] { "Id", "Name", "Cities.Name" };
var query = DynamicQueryHelper.BuildDynamicCountryQuery(myContext, columns);

// Execute the query (e.g., to list results)
var result = query.ToList();

Key Considerations #

• Error Handling: The current code skips invalid properties, but you can add validation to throw exceptions if a column doesn't exist on the entity.
• Nested Depth: This solution handles one level of nesting (e.g., Cities.Name). For deeper nesting (like Cities.Region.Name), you'd need to extend the code to recursively parse multi-level property paths.
• Change Tracking: If you don't want EF Core to track the projected entities, add .AsNoTracking() to the query:

  return context.Countries.Select(selectLambda).AsNoTracking();
  

• Navigation Property Types: The code assumes your Cities property is of type ICollection<City>. Adjust the generic type check if you use IList<City> or another enumerable type.

This approach translates directly to efficient SQL, just like your hardcoded Select statement, and only loads the properties you specify.

内容的提问来源于stack exchange，提问作者Azia Stephane Lobognon-Naki