无需新建类，如何从数据库返回关联表的通用对象而非指定类型？

需求：实现GetTransactionBySaleId方法，关联查询Transactions和TransactionItems表的全部数据，尽可能不新建类。

现有实现代码：

public async Task<IEnumerable<object>> GetTransactionBySaleId(int id)
{
    var query = ("SELECT t.*, ti.* " +
       "FROM Transactions t " +
       "INNER JOIN TransactionItems ti ON t.SaleId = ti.SaleId " +
       "WHERE t.SaleId = @Id");

    // Parameterisation to prevent injection attacks
    var idParam = new SqlParameter("Id", id);

    return await DatabaseConnection.Instance.ExecuteQueryAsync<object>(query, idParam);
}

ExecuteQueryAsync方法实现：

public async Task<IEnumerable<T>> ExecuteQueryAsync<T>(string query, params SqlParameter[] parameters) where T : new()
{
    using var connection = new SqlConnection(connectionString);

    await connection.OpenAsync();

    var resultList = new List<T>();

    using var command = new SqlCommand(query, connection);

    // Add parameters to the command if any
    if (parameters != null)
    {
        command.Parameters.AddRange(parameters);
    }

    using var reader = await command.ExecuteReaderAsync();
    
    while (await reader.ReadAsync())
    {
        var obj = new T();
        MapData(reader, obj);
        resultList.Add(obj);
    }
    
    return resultList;
}

遇到的问题：

• 返回类型设为object时，仅返回对应条目数量的空对象；
• 返回类型设为Transactions时，无法获取TransactionItems表的数据。

1. 核心问题分析 #

• 使用object作为泛型参数时，MapData方法无法给无属性的object实例赋值，因此得到空对象；
• 使用Transactions类时，该类仅包含Transactions表的属性，TransactionItems表的字段没有对应的属性，映射时会被忽略，所以拿不到关联表数据。

2. 无需新建类的最优方案：使用ExpandoObject #

ExpandoObject支持动态添加属性，完美适配多表联合查询的动态字段需求，且不需要新建实体类。

步骤1：修改方法返回类型

将GetTransactionBySaleId的返回类型改为Task<IEnumerable<ExpandoObject>>，并调整泛型调用：

public async Task<IEnumerable<ExpandoObject>> GetTransactionBySaleId(int id)
{
    var query = ("SELECT t.*, ti.* " +
       "FROM Transactions t " +
       "INNER JOIN TransactionItems ti ON t.SaleId = ti.SaleId " +
       "WHERE t.SaleId = @Id");

    var idParam = new SqlParameter("Id", id);

    return await DatabaseConnection.Instance.ExecuteQueryAsync<ExpandoObject>(query, idParam);
}

步骤2：修改MapData方法支持动态对象

调整MapData逻辑，让它能遍历DataReader的所有列，将列名和对应值添加到ExpandoObject中：

private void MapData(IDataReader reader, object obj)
{
    if (obj is ExpandoObject expando)
    {
        var expandoDict = expando as IDictionary<string, object>;
        for (int i = 0; i < reader.FieldCount; i++)
        {
            string columnName = reader.GetName(i);
            object value = reader.IsDBNull(i) ? null : reader.GetValue(i);
            expandoDict[columnName] = value;
        }
        return;
    }

    // 保留原有的实体类映射逻辑（兼容其他实体类场景）
    PropertyInfo[] properties = obj.GetType().GetProperties();
    foreach (PropertyInfo prop in properties)
    {
        if (!reader.IsDBNull(reader.GetOrdinal(prop.Name)))
        {
            object value = reader.GetValue(reader.GetOrdinal(prop.Name));
            prop.SetValue(obj, value);
        }
    }
}

步骤3：确认泛型约束兼容性

ExpandoObject具备默认构造函数，原ExecuteQueryAsync的where T : new()约束可以满足，无需修改该方法定义。

3. 临时替代方案：手动构造匿名类型 #

如果不想用ExpandoObject，可以直接读取DataReader并手动构造匿名类型，但需要硬编码所有列名，灵活性较差：

public async Task<IEnumerable<object>> GetTransactionBySaleId(int id)
{
    var query = ("SELECT t.*, ti.* " +
       "FROM Transactions t " +
       "INNER JOIN TransactionItems ti ON t.SaleId = ti.SaleId " +
       "WHERE t.SaleId = @Id");

    var idParam = new SqlParameter("Id", id);
    var result = new List<object>();

    using var connection = new SqlConnection(DatabaseConnection.Instance.connectionString);
    await connection.OpenAsync();
    using var command = new SqlCommand(query, connection);
    command.Parameters.Add(idParam);
    using var reader = await command.ExecuteReaderAsync();

    while (await reader.ReadAsync())
    {
        var item = new
        {
            // Transactions表字段
            TransactionId = reader.GetInt32(reader.GetOrdinal("TransactionId")),
            SaleId = reader.GetInt32(reader.GetOrdinal("SaleId")),
            TransactionDate = reader.GetDateTime(reader.GetOrdinal("TransactionDate")),
            // ...补充其他Transactions表字段
            // TransactionItems表字段
            TransactionItemId = reader.GetInt32(reader.GetOrdinal("TransactionItemId")),
            ProductId = reader.GetInt32(reader.GetOrdinal("ProductId")),
            Quantity = reader.GetInt32(reader.GetOrdinal("Quantity")),
            // ...补充其他TransactionItems表字段
        };
        result.Add(item);
    }

    return result;
}

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