ASP.NET核心实体框架SQL查询SELECT

I am one of the many struggling to "upgrade" from ASP.NET to ASP.NET Core.

我是努力从ASP.NET“升级”到ASP.NET Core的众多人之一。

In the ASP.NET project, I made database calls from my DAL like so:

在ASP.NET项目中，我从我的DAL进行数据库调用，如下所示：

var result = context.Database.SqlQuery<Object_VM>("EXEC [sp_Object_GetByKey] @Key",
      new SqlParameter("@Key", Key))
      .FirstOrDefault();

return result;

My viewmodel has additional fields that my object does not, such as aggregates of related tables. It seems unnecessary and counter intuitive to include such fields in a database / table structure. My stored procedure calculates all those things and returns the fields as should be displayed, but not stored.

我的viewmodel有我的对象没有的其他字段，例如相关表的聚合。在数据库/表结构中包含这些字段似乎是不必要的和反直觉的。我的存储过程计算所有这些内容并返回应显示的字段，但不存储。

I see that ASP.NET Core has removed this functionality. I am trying to continue to use stored procedures and load view models (and thus not have the entity in the database). I see options like the following, but as a result I get "2", the number of rows being returned (or another mysterious result?).

我看到ASP.NET Core已删除此功能。我试图继续使用存储过程和加载视图模型（因此没有数据库中的实体）。我看到如下选项，但结果我得到“2”，返回的行数（或另一个神秘的结果？）。

using(context)
{
    string cmd = "EXEC [sp_Object_getAll]";
    var result = context.Database.ExecuteSQLCommand(cmd);
}

But that won't work because context.Database.ExecuteSQLCommand is only for altering the database, not "selecting".

但这不起作用，因为context.Database.ExecuteSQLCommand仅用于更改数据库，而不是“选择”。

I've also seen the following as a solution, but the code will not compile for me, as "set" is really set<TEntity>, and there isn't a database entity for this viewmodel.

我也看到以下作为解决方案，但代码不会为我编译，因为“set”实际设置为，并且此viewmodel没有数据库实体。

var result = context.Set().FromSql("EXEC [sp_Object_getAll]");

Any assistance much appreciated.

任何协助非常感谢。

1 个解决方案

#1

Solution:

解：

(per Tseng's advice)

（按照Tseng的建议）

On the GitHub Entity Framework Issues page, there is a discussion about this problem. One user recommends creating your own class to handle this sort of requests, and another adds an additional method that makes it run smoother. I changed the methods slights to accept slightly different params.

在GitHub实体框架问题页面上，讨论了这个问题。一个用户建议创建自己的类来处理这种请求，另一个用户则添加一个额外的方法，使其运行更顺畅。我改变了方法轻蔑接受略有不同的参数。

Here is my adaptation (very little difference), for others that are also looking for a solution:

这是我的改编（非常小的差异），对于其他也在寻找解决方案的人：

Method in DAL

DAL中的方法

public JsonResult GetObjectByID(int ID)
{
    SqlParameter[] parms = new SqlParameter[] { new SqlParameter("@ID", ID) };
    var result = RDFacadeExtensions.GetModelFromQuery<Object_List_VM>(context, "EXEC [sp_Object_GetList] @ID", parms);
    return new JsonResult(result.ToList(), setting);
}

Additional Class

附加课程

public static class RDFacadeExtensions
{
    public static RelationalDataReader ExecuteSqlQuery(
        this DatabaseFacade databaseFacade, 
        string sql, 
        SqlParameter[] parameters)
    {
        var concurrencyDetector = databaseFacade.GetService<IConcurrencyDetector>();
        using (concurrencyDetector.EnterCriticalSection())
        {
            var rawSqlCommand = databaseFacade
                .GetService<IRawSqlCommandBuilder>()
                .Build(sql, parameters);

            return rawSqlCommand
                .RelationalCommand
                .ExecuteReader(
                    databaseFacade.GetService<IRelationalConnection>(),
                    parameterValues: rawSqlCommand.ParameterValues);
        }
    }

    public static IEnumerable<T> GetModelFromQuery<T>(
        DbContext context, 
        string sql, 
        SqlParameter[] parameters)
        where T : new()
    {
        DatabaseFacade databaseFacade = new DatabaseFacade(context);
        using (DbDataReader dr = databaseFacade.ExecuteSqlQuery(sql, parameters).DbDataReader)
        {
            List<T> lst = new List<T>();
            PropertyInfo[] props = typeof(T).GetProperties();
            while (dr.Read())
            {
                T t = new T();
                IEnumerable<string> actualNames = dr.GetColumnSchema().Select(o => o.ColumnName);
                for (int i = 0; i < props.Length; ++i)
                {
                    PropertyInfo pi = props[i];
                    if (!pi.CanWrite) continue;
                    System.ComponentModel.DataAnnotations.Schema.ColumnAttribute ca = pi.GetCustomAttribute(typeof(System.ComponentModel.DataAnnotations.Schema.ColumnAttribute)) as System.ComponentModel.DataAnnotations.Schema.ColumnAttribute;
                    string name = ca?.Name ?? pi.Name;
                    if (pi == null) continue;
                    if (!actualNames.Contains(name)) { continue; }
                    object value = dr[name];
                    Type pt = pi.DeclaringType;
                    bool nullable = pt.GetTypeInfo().IsGenericType && pt.GetGenericTypeDefinition() == typeof(Nullable<>);
                    if (value == DBNull.Value) { value = null; }
                    if (value == null && pt.GetTypeInfo().IsValueType && !nullable)
                    { value = Activator.CreateInstance(pt); }
                    pi.SetValue(t, value);
                }//for i
                lst.Add(t);
            }//while
            return lst;
        }//using dr
    }

#1