如何用ASP.NET Web API OData提供程序返回动态列存储过程结果？

针对你遇到的动态列返回问题，结合VS2013的环境（对应Web API 2.2 + OData v4），可以通过动态生成Edm模型+ExpandoObject序列化的方案解决，以下是具体实现步骤：

1. 准备必要的NuGet包 #

确保项目安装对应版本的OData包：

Install-Package Microsoft.AspNet.WebApi.OData -Version 5.2.3

2. 动态生成Edm模型 #

OData依赖元数据描述数据结构，需根据存储过程返回的DataTable动态构建EdmEntityType和EdmModel：

private IEdmModel CreateDynamicEdmModel(DataTable dataTable, string entitySetName = "DynamicData")
{
    var edmModel = new EdmModel();
    var entityContainer = new EdmEntityContainer("DynamicNamespace", "DefaultContainer");
    
    // 构建动态实体类型
    var entityType = new EdmEntityType("DynamicNamespace", "DynamicEntity");
    foreach (DataColumn col in dataTable.Columns)
    {
        // 映射CLR类型到Edm原生类型
        var edmType = MapClrTypeToEdm(col.DataType);
        entityType.AddStructuralProperty(col.ColumnName, edmType);
    }
    
    // OData要求实体必须有主键，这里取第一列作为主键（无唯一列可自行添加虚拟主键）
    if (entityType.StructuralProperties.Any())
    {
        entityType.AddKeys(entityType.StructuralProperties.First());
    }
    
    // 添加实体集到模型
    var entitySet = entityContainer.AddEntitySet(entitySetName, entityType);
    edmModel.AddElement(entityContainer);
    edmModel.AddElement(entityType);
    
    return edmModel;
}

private EdmPrimitiveTypeKind MapClrTypeToEdm(Type clrType)
{
    return clrType switch
    {
        Type t when t == typeof(int) => EdmPrimitiveTypeKind.Int32,
        Type t when t == typeof(long) => EdmPrimitiveTypeKind.Int64,
        Type t when t == typeof(string) => EdmPrimitiveTypeKind.String,
        Type t when t == typeof(DateTime) => EdmPrimitiveTypeKind.DateTimeOffset,
        Type t when t == typeof(decimal) => EdmPrimitiveTypeKind.Decimal,
        Type t when t == typeof(bool) => EdmPrimitiveTypeKind.Boolean,
        _ => EdmPrimitiveTypeKind.String // 默认映射为字符串
    };
}

3. 将DataTable转换为动态对象集合 #

使用ExpandoObject存储动态行数据，让OData序列化器识别动态属性：

private IEnumerable<dynamic> ConvertDataTableToDynamic(DataTable dt)
{
    foreach (DataRow row in dt.Rows)
    {
        var expando = new ExpandoObject();
        var expandoDict = (IDictionary<string, object>)expando;
        
        foreach (DataColumn col in dt.Columns)
        {
            expandoDict[col.ColumnName] = row[col] == DBNull.Value ? null : row[col];
        }
        
        yield return expando;
    }
}

4. 实现动态OData控制器 #

继承ODataController，手动处理OData响应序列化，确保返回符合OData规范的格式：

public class DynamicDataController : ODataController
{
    [HttpGet]
    public IHttpActionResult Get(string spName, params string[] spParams)
    {
        // 1. 执行存储过程获取DataTable（替换为你的ADO.NET逻辑）
        var dataTable = ExecuteStoredProcedure(spName, spParams);
        
        if (dataTable?.Rows.Count == 0)
        {
            return NotFound();
        }
        
        // 2. 生成动态Edm模型
        var edmModel = CreateDynamicEdmModel(dataTable);
        var entitySet = edmModel.EntityContainer.FindEntitySet("DynamicData");
        
        // 3. 转换为动态对象集合
        var dynamicData = ConvertDataTableToDynamic(dataTable);
        
        // 4. 构建OData响应
        var response = new HttpResponseMessage(HttpStatusCode.OK);
        var odataUri = new ODataUri { ServiceRoot = Request.RequestUri.GetLeftPart(UriPartial.Authority) };
        var writerSettings = new ODataMessageWriterSettings
        {
            ODataUri = odataUri,
            Version = ODataVersion.V4,
            IncludeAnnotationFilter = _ => true
        };
        
        using (var stream = new MemoryStream())
        {
            var messageWrapper = new ODataMessageWrapper(stream, response.Headers);
            using (var writer = new ODataMessageWriter(messageWrapper, writerSettings, edmModel))
            {
                var feedWriter = writer.CreateODataFeedWriter(entitySet.EntityType(), entitySet);
                feedWriter.WriteStart();
                
                foreach (var item in dynamicData)
                {
                    feedWriter.WriteStart(item);
                    feedWriter.WriteEnd();
                }
                
                feedWriter.WriteEnd();
            }
            
            stream.Position = 0;
            response.Content = new StreamContent(stream);
            response.Content.Headers.ContentType = MediaTypeHeaderValue.Parse("application/json;odata.metadata=full");
        }
        
        return ResponseMessage(response);
    }
    
    // 替换为你的存储过程执行逻辑
    private DataTable ExecuteStoredProcedure(string spName, string[] parameters)
    {
        var dt = new DataTable();
        var connString = ConfigurationManager.ConnectionStrings["YourDbConn"].ConnectionString;
        
        using (var conn = new SqlConnection(connString))
        using (var cmd = new SqlCommand(spName, conn))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            
            if (parameters != null)
            {
                for (int i = 0; i < parameters.Length; i++)
                {
                    cmd.Parameters.AddWithValue($"@Param{i}", parameters[i]);
                }
            }
            
            conn.Open();
            using (var reader = cmd.ExecuteReader())
            {
                dt.Load(reader);
            }
        }
        
        return dt;
    }
}

5. 配置Web API路由 #

在WebApiConfig.cs中注册OData路由，允许动态模型的访问：

public static void Register(HttpConfiguration config)
{
    // 启用属性路由
    config.MapHttpAttributeRoutes();
    
    // 注册通用OData路由（初始模型为空，实际模型动态生成）
    config.MapODataServiceRoute(
        routeName: "ODataRoute",
        routePrefix: "odata",
        model: new EdmModel()
    );
    
    // 配置JSON序列化
    config.Formatters.JsonFormatter.SerializerSettings.ReferenceLoopHandling = Newtonsoft.Json.ReferenceLoopHandling.Ignore;
}

6. Excel导入适配 #

Excel导入OData时需要完整的元数据，响应中指定了odata.metadata=full，Excel会自动从响应中解析数据结构。测试时直接在Excel中输入OData地址即可，例如：

http://yourdomain/odata/DynamicData?spName=YourStoredProcedure&spParams=Param1&spParams=Param2

关于VS2013版本的说明 #

VS2013搭配Web API 2.2 + OData v4是完全可行的，无需升级版本，只要确保NuGet包版本对应即可。

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