EF Core查询存储过程映射到类型 [英] EF Core query stored procedure map to types

问题描述

我有一个需要查询数据库并将结果返回到Web api的项目.DB Admin即时创建了几个存储过程，它们具有一个UI，可以创建存储过程的定义及其名称，并且Web api服务仅调用该SP并应返回结果.根据以下代码，我无法将返回对象获取到Web api控制器，因为该对象包含项目但没有映射属性.

I have a project that needs to query a database and return results to web api. There are several stored procedures that are created on the fly by the DB Admin and they have a UI that they create the definition of the stored procedure and the name of it and the web api service just calls that SP and should return the result. Based on the below code I cannot get the return object to web api controller as object contains items but with no properties mapped.

var result = dbContext.Query<object>().AsNoTracking().FromSql(
            "dbo.MY_SP @DateFrom=@DateFrom, @DateTo=@DateTo",
            dateFromParam, dateToParam).ToList();

推荐答案

在您的情况下，您需要使用dynamic来表示存储过程的结果.

In your case, you need to use dynamic to represents the result of stored procedure.

在您的DbContext类中添加以下方法:

Add the following method in your DbContext class:

public IEnumerable<dynamic> GetDynamicResult(string commandText, params SqlParameter[] parameters)
{
    // Get the connection from DbContext
    var connection = DbContext.Database.GetDbConnection();

    // Open the connection if isn't open
    if (connection.State != System.Data.ConnectionState.Open)
        connection.Open();

    using (var command = connection.CreateCommand())
    {
        command.CommandText = commandText;
        command.Connection = connection;

        if (parameters?.Length > 0)
        {
            foreach (var parameter in parameters)
            {
                command.Parameters.Add(parameter);
            }
        }

        using (var dataReader = command.ExecuteReader())
        {
            // List for column names
            var names = new List<string>();

            if (dataReader.HasRows)
            {
                // Add column names to list
                for (var i = 0; i < dataReader.VisibleFieldCount; i++)
                {
                    names.Add(dataReader.GetName(i));
                }

                while (dataReader.Read())
                {
                    // Create the dynamic result for each row
                    var result = new ExpandoObject() as IDictionary<string, object>;

                    foreach (var name in names)
                    {
                        // Add key-value pair
                        // key = column name
                        // value = column value
                        result.Add(name, dataReader[name]);
                    }

                    yield return result;
                }
            }
        }
    }
}

现在，您可以通过以下方式调用存储过程:

Now you can invoke your stored procedure in this way:

var result = dbContext.GetDynamicResult(" exec [dbo].[MySp] @dateFrom, @dateTo ", new SqlParameter("@dateFrom", dateFrom), new SqlParameter("@dateTo", dateTo));

请让我知道这个答案是否有用.

Please let me know if this answer is useful.

致谢.

这篇关于EF Core查询存储过程映射到类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！