不借助模型为EF Core LINQ查询结果添加计算字段（附查询代码）

Got it, let's tackle this problem. Since your Foo model has 300+ fields, creating a new DTO to include all those plus computed fields is obviously not feasible. Here are a couple of practical approaches to add computed fields without defining a new model:

1. In-Memory Processing with ExpandoObject (Most Practical for Your Case) #

This method first loads matching data into memory, then uses ExpandoObject to dynamically add computed fields while retaining all original model properties. It avoids the pain of manually listing 300+ fields.

Step 1: Add an Extension Method to Convert Entities to ExpandoObject #

using System.Dynamic;
using System.Reflection;

public static class ObjectExtensions
{
    public static ExpandoObject ToExpando(this object obj)
    {
        if (obj == null) throw new ArgumentNullException(nameof(obj));
        
        var expando = new ExpandoObject();
        var expandoDict = (IDictionary<string, object>)expando;
        
        // Copy all public properties from the original entity to the ExpandoObject
        foreach (var prop in obj.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance))
        {
            expandoDict[prop.Name] = prop.GetValue(obj);
        }
        
        return expando;
    }
}

Step 2: Modify Your Query to Add Computed Fields #

// First define your base union query (fill in the missing conditions from your code)
var baseQuery = _context.Foo 
    .Where(r => !StatusExceptionList.Contains(r.Status)) 
    .Where(r => (Convert.ToDateTime(r.date) - today).TotalDays < 31) 
    .Where(r => r.Pid == PId)
    .Union(_context.Foo 
        .Where(r => !DraftStatusExceptionList.Contains(r.Status)) 
        .Where(r => r.Pid == PId) 
        .Where(r => r.Csstatus != "NA" || !string.IsNullOrEmpty(r.Csstatus))
        // Add remaining conditions here
    );

// Load data into memory and inject computed fields
var result = baseQuery.AsEnumerable() // Pulls data from DB to local memory
    .Select(r => 
    {
        var expando = r.ToExpando();
        var expandoDict = (IDictionary<string, object>)expando;
        
        // Add your computed fields here
        expandoDict["DaysSinceDate"] = (DateTime.Today - Convert.ToDateTime(r.date)).TotalDays;
        expandoDict["IsRecent"] = (DateTime.Today - Convert.ToDateTime(r.date)).TotalDays < 7;
        // Add any other computed fields you need
        
        return expando;
    })
    .ToList();

Step 3: Access the Results #

Use dynamic to access both original and computed fields:

foreach (dynamic item in result)
{
    // Access original model fields
    Console.WriteLine($"Pid: {item.Pid}, Status: {item.Status}");
    // Access computed fields
    Console.WriteLine($"Days Since Date: {item.DaysSinceDate}, Is Recent: {item.IsRecent}");
}

Notes:

• AsEnumerable() loads all matching records into memory, so be cautious if your result set is extremely large.
• You lose compile-time type safety with dynamic, so double-check property names to avoid typos.

2. Database-Level Computation (For Large Datasets) #

If you need to compute fields at the database level (to avoid loading all data into memory), wrap the original entity plus computed fields in an anonymous type. This keeps computation in the DB without requiring a massive DTO.

var result = await baseQuery
    .Select(r => new 
    {
        // Wrap the entire original entity
        Foo = r,
        // Add computed fields (EF Core translates these to SQL)
        DaysSinceDate = EF.Functions.DateDiffDay(Convert.ToDateTime(r.date), DateTime.Today),
        IsRecent = EF.Functions.DateDiffDay(Convert.ToDateTime(r.date), DateTime.Today) < 7
    })
    .ToListAsync();

Access the Results: #

foreach (var item in result)
{
    // Original fields live under item.Foo
    Console.WriteLine($"Pid: {item.Foo.Pid}");
    // Computed fields are top-level
    Console.WriteLine($"Days Since Date: {item.DaysSinceDate}");
}

Notes:

• Use EF.Functions for date calculations to ensure proper SQL translation (instead of TotalDays, which may not be supported across all databases).
• This approach keeps data processing in the database, which is better for large datasets.

3. Dynamic Expression Tree Projection (Advanced) #

If you need a flat structure (no nested Foo property) and database-level computation, you can build a dynamic expression tree to project all 300+ fields plus computed fields. This is more complex but avoids manual field listing.

Example Implementation: #

using System.Linq.Expressions;
using System.Reflection.Emit;

public static IQueryable<dynamic> ProjectWithComputedFields<T>(this IQueryable<T> query, params (string Name, Expression<Func<T, object>> Expression)[] computedFields)
{
    var parameter = Expression.Parameter(typeof(T), "r");
    
    // Collect bindings for all original entity properties
    var propertyBindings = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance)
        .Select(prop => Expression.Bind(prop, Expression.Property(parameter, prop.Name)));
    
    // Collect bindings for computed fields
    var computedBindings = computedFields.Select(field =>
    {
        var expr = field.Expression.Body;
        // Unwrap convert expressions for value types
        if (expr.NodeType == ExpressionType.Convert)
        {
            expr = ((UnaryExpression)expr).Operand;
        }
        // Create a dummy property reference for the computed field
        var dummyProp = typeof(DummyComputedFields).GetProperty(field.Name) ?? throw new InvalidOperationException($"Property {field.Name} not defined in dummy class");
        return Expression.Bind(dummyProp, expr);
    });
    
    // Combine all bindings and build the projection expression
    var allBindings = propertyBindings.Concat(computedBindings);
    var anonymousType = CreateAnonymousType(typeof(T), computedFields.Select(f => f.Name));
    var initExpression = Expression.MemberInit(Expression.New(anonymousType.GetConstructor(Type.EmptyTypes)), allBindings);
    
    return query.Select(Expression.Lambda<Func<T, dynamic>>(initExpression, parameter));
}

// Helper to create a dynamic anonymous type with all original fields plus computed fields
private static Type CreateAnonymousType(Type baseType, IEnumerable<string> computedFieldNames)
{
    var properties = baseType.GetProperties(BindingFlags.Public | BindingFlags.Instance)
        .Select(p => (p.Name, p.PropertyType))
        .Concat(computedFieldNames.Select(name => (name, typeof(object))))
        .ToArray();
    
    var moduleBuilder = Assembly.GetExecutingAssembly().DefineDynamicModule("DynamicProjectionTypes");
    var typeBuilder = moduleBuilder.DefineType($"Anonymous_{Guid.NewGuid()}", TypeAttributes.Public | TypeAttributes.Class);
    
    foreach (var (name, type) in properties)
    {
        var field = typeBuilder.DefineField($"_{name}", type, FieldAttributes.Private);
        var propBuilder = typeBuilder.DefineProperty(name, PropertyAttributes.None, type, null);
        
        // Generate getter method
        var getter = typeBuilder.DefineMethod($"get_{name}", MethodAttributes.Public | MethodAttributes.SpecialName, type, Type.EmptyTypes);
        var getterIl = getter.GetILGenerator();
        getterIl.Emit(OpCodes.Ldarg_0);
        getterIl.Emit(OpCodes.Ldfld, field);
        getterIl.Emit(OpCodes.Ret);
        
        // Generate setter method
        var setter = typeBuilder.DefineMethod($"set_{name}", MethodAttributes.Public | MethodAttributes.SpecialName, null, new[] { type });
        var setterIl = setter.GetILGenerator();
        setterIl.Emit(OpCodes.Ldarg_0);
        setterIl.Emit(OpCodes.Ldarg_1);
        setterIl.Emit(OpCodes.Stfld, field);
        setterIl.Emit(OpCodes.Ret);
        
        propBuilder.SetGetMethod(getter);
        propBuilder.SetSetMethod(setter);
    }
    
    return typeBuilder.CreateType()!;
}

// Dummy class to define computed field names for expression binding
public class DummyComputedFields
{
    public object? DaysSinceDate { get; set; }
    public object? IsRecent { get; set; }
    // Add other computed field names here as properties
}

Usage: #

var result = await baseQuery
    .ProjectWithComputedFields(
        ("DaysSinceDate", r => EF.Functions.DateDiffDay(Convert.ToDateTime(r.date), DateTime.Today)),
        ("IsRecent", r => EF.Functions.DateDiffDay(Convert.ToDateTime(r.date), DateTime.Today) < 7)
    )
    .ToListAsync();

Notes:

• This is an advanced approach and requires thorough testing.
• The dynamic type will have a flat structure with all original fields and computed fields.

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