EF Core 3.1.3执行存储过程并映射DTO的最优方案咨询

Hey there! Let's break down your question about executing stored procedures and mapping results to custom DTOs in EF Core 3.1.3—focusing on both best practices and performance concerns around reflection.

1. EF Core Native Solution: Keyless Entity Types (Recommended) #

Since you're already using EF Core, this is the most aligned and performant approach. EF Core supports keyless entity types (introduced in 2.1+) specifically for scenarios like mapping stored procedure results, views, or ad-hoc queries to DTOs without requiring a primary key or database table mapping.

Steps to implement:

• Configure your DTO as a keyless entity in your DbContext's OnModelCreating method:

  protected override void OnModelCreating(ModelBuilder modelBuilder)
  {
      modelBuilder.Entity<YourCustomDto>(eb =>
      {
          eb.HasNoKey(); // Mark as keyless
          // Map column names if they don't match your DTO property names
          eb.Property(dto => dto.OrderId).HasColumnName("Order_ID");
          eb.Property(dto => dto.CustomerName).HasColumnName("Customer_Name");
      });
  }
  

• Add a DbSet for your DTO in the DbContext:

  public DbSet<YourCustomDto> YourCustomDtos { get; set; }
  

• Execute the stored procedure using FromSqlRaw (or FromSqlInterpolated for safer parameter handling):

  var parameters = new[]
  {
      new SqlParameter("@StartDate", DateTime.UtcNow.AddMonths(-1)),
      new SqlParameter("@EndDate", DateTime.UtcNow)
  };
  
  var results = context.YourCustomDtos
      .FromSqlRaw("EXEC GetOrderSummary @StartDate, @EndDate", parameters)
      .ToList();
  

This approach uses EF Core's optimized internal mapping logic, so you avoid writing custom reflection code entirely. It also handles parameterization safely (preventing SQL injection) and integrates seamlessly with your existing EF Core setup.

2. Optimizing Custom Reflection-Based Mapping #

If you need a generic solution that doesn't rely on keyless entities, your current reflection approach can be drastically optimized by caching mapping logic instead of reflecting on every call. The main performance hit from reflection comes from repeatedly fetching PropertyInfo instances and constructing objects—caching these as compiled expression trees eliminates most of that overhead.

Here's an improved version of your mapper with caching:

public static class CachedDataReaderMapper
{
    private static readonly Dictionary<Type, Func<DbDataReader, object>> _mappingCache = new();

    public static List<T> MapToList<T>(DbDataReader reader) where T : new()
    {
        var results = new List<T>();
        if (!reader.HasRows) return results;

        var mapper = GetOrCreateMapper<T>();
        while (reader.Read())
        {
            results.Add((T)mapper(reader));
        }
        return results;
    }

    private static Func<DbDataReader, object> GetOrCreateMapper<T>() where T : new()
    {
        var type = typeof(T);
        if (_mappingCache.TryGetValue(type, out var existingMapper))
            return existingMapper;

        // Build a compiled expression tree to map DataReader to T
        var readerParam = Expression.Parameter(typeof(DbDataReader), "reader");
        var constructor = Expression.New(type);
        var memberBindings = new List<MemberBinding>();

        foreach (var prop in type.GetProperties(BindingFlags.Public | BindingFlags.Instance))
        {
            // Check if the column exists and value is not DBNull
            var hasColumnCall = Expression.Call(readerParam, typeof(DbDataReader).GetMethod("HasColumn", new[] { typeof(string) }), Expression.Constant(prop.Name));
            var valueExpr = Expression.Property(readerParam, "Item", Expression.Constant(prop.Name));
            var notDbNullExpr = Expression.NotEqual(valueExpr, Expression.Constant(DBNull.Value));
            var conditionExpr = Expression.AndAlso(hasColumnCall, notDbNullExpr);

            // Assign value to property (or default if condition fails)
            var valueAssign = Expression.Bind(prop, Expression.Convert(valueExpr, prop.PropertyType));
            var defaultAssign = Expression.Bind(prop, Expression.Default(prop.PropertyType));
            memberBindings.Add(Expression.Condition(conditionExpr, valueAssign, defaultAssign));
        }

        var initExpr = Expression.MemberInit(constructor, memberBindings);
        var mapperLambda = Expression.Lambda<Func<DbDataReader, object>>(initExpr, readerParam);
        var compiledMapper = mapperLambda.Compile();

        _mappingCache[type] = compiledMapper;
        return compiledMapper;
    }
}

Replace your DataReaderMapToList method with CachedDataReaderMapper.MapToList<T>(dataReader)—the first call for a DTO type will compile the mapping logic, and all subsequent calls will use the cached delegate, making performance nearly identical to manual mapping.

3. Performance Analysis #

• Keyless Entity Types: Best performance overall—EF Core precompiles query and mapping logic, so there's minimal overhead beyond executing the stored procedure itself.
• Cached Expression Tree Mapping: Very close to manual mapping performance. The only overhead is the initial compilation (once per DTO type), which is negligible in most web app scenarios.
• Original Reflection Approach: Will cause measurable performance degradation in high-concurrency or high-frequency scenarios, as it reflects on properties and creates object instances every time the method runs.

4. Third-Party Option: AutoMapper #

If you're already using AutoMapper in your project, it has built-in support for mapping IDataReader to DTOs. It automatically caches mapping configurations, so performance is on par with cached expression trees:

// Configure AutoMapper once (e.g., in startup)
var mapperConfig = new MapperConfiguration(cfg =>
{
    cfg.CreateMap<IDataReader, YourCustomDto>();
    // Add mappings for other DTOs as needed
});
var mapper = mapperConfig.CreateMapper();

// Use in your stored procedure execution method
using (var dataReader = command.ExecuteReader())
{
    var results = mapper.Map<List<YourCustomDto>>(dataReader);
    return results;
}

Final Recommendations #

• Prioritize keyless entity types if possible—it's the most "EF Core-native" approach, requires minimal custom code, and offers the best performance.
• If you need a generic solution, use the cached expression tree mapper or AutoMapper to avoid the performance hit of repeated reflection.

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