ASP.NET Core Entity Framework SQL Query SELECT

Question

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.

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 github.com/aspnet/EntityFramework/wiki/Roadmap to see EF Core's limitations and which features are to be added in future — Tseng
– Tseng, Commented Apr 18, 2017 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) github.com/aspnet/EntityFramework/issues/1862 — Tseng
– Tseng, Commented Apr 18, 2017 at 22:52
Possible duplicate of Raw SQL queries and Entity Framework Core — J. Doe
– J. Doe, Commented Apr 19, 2017 at 10:47
I apologize for mistyping. Tseng - thank you - this 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
– swallis1, Commented Apr 19, 2017 at 16:16

swallis1 · Accepted Answer · 2017-04-19 19:12:12Z

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
    }

Collectives™ on Stack Overflow

ASP.NET Core Entity Framework SQL Query SELECT

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related