0

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

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

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.

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?).

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".

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.

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

Any assistance much appreciated.

swallis1
  • 99
  • 1
  • 13
  • 1. you don't upgrade from ASP.NET to ASP.NET Core you migrate and you should treat it as such. Upgrade generally means backward compatibility, migration means there is no such thing. Since ASP.NET Core is a complete rewrite and has significant differences under the hood. 2.) ASP.NET Core didn't removed anything, since ASP.NET never had an ORM. That's Entity Framework (and it's rewrite: EF Core). 3. read https://github.com/aspnet/EntityFramework/wiki/Roadmap to see EF Core's limitations and which features are to be added in future – Tseng Apr 18 '17 at 22:48
  • One of the features that's not implemented yet is Ad-Hoc binding (binding SQL to models which are not registered as entities) https://github.com/aspnet/EntityFramework/issues/1862 – Tseng Apr 18 '17 at 22:52
  • Possible duplicate of [Raw SQL queries and Entity Framework Core](http://stackoverflow.com/questions/35305825/raw-sql-queries-and-entity-framework-core) – J. Doe Apr 19 '17 at 10:47
  • I apologize for mistyping. Tseng - thank you - [this](https://github.com/aspnet/EntityFramework/issues/1862#issuecomment-263392435) helped. Not a duplicate, as the discussion in the other topic is around using a model that is in the database, while I am trying to avoid that. – swallis1 Apr 19 '17 at 16:16

1 Answers1

1

Solution:

(per Tseng's advice)

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.

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

Method in 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
    }
swallis1
  • 99
  • 1
  • 13