Execute a query and map it to a list of dynamic objects

[mojtabakaviani]

please extend DbContext.Database.SqlQuery to support dynamic type for map query to a list of dynamic objects. for example:

var results = DbContext.Database.SqlQuery<dynamic>("SELECT * FROM Kids");
var id = result.First().Id;
var name = result.First().Name;
var age = result.First().Age ?? 0; // null support
foreach (var item in results)
{
     Console.WriteLine("{0}:{1}", item.Name, item.Age);
}

[prasannapattam]

(+ 1)
Can you also provide a Dictionary or List

[mojtabakaviani]

So extend this to FromSQL method

[ghost]

+1

[ChristineBoersen]

**NOTE this was written against pre RTM version of .Net core and may need to be modified to work with 1.0 RTM and newer versions of the framework

Attached is some code that provides this functionality with the existing framework. Let me know if this helps any.

    public static IEnumerable<dynamic> CollectionFromSql(this DbContext dbContext, string Sql, Dictionary<string, object> Parameters)
    {
        using (var cmd = dbContext.Database.GetDbConnection().CreateCommand())
        {
            cmd.CommandText = Sql;
            if (cmd.Connection.State != ConnectionState.Open)
                cmd.Connection.Open();

            foreach (KeyValuePair<string, object> param in Parameters)
            {
                DbParameter dbParameter = cmd.CreateParameter();
                dbParameter.ParameterName = param.Key;
                dbParameter.Value = param.Value;
                cmd.Parameters.Add(dbParameter);
            }

            //var retObject = new List<dynamic>();
            using (var dataReader = cmd.ExecuteReader())
            {

                while (dataReader.Read())
                {
                    var dataRow = GetDataRow(dataReader);
                    yield return dataRow ;

                }
            }


        }
    }

    private static dynamic GetDataRow(DbDataReader dataReader)
    {
        var dataRow = new ExpandoObject() as IDictionary<string, object>;
        for (var fieldCount = 0; fieldCount < dataReader.FieldCount; fieldCount++)
            dataRow.Add(dataReader.GetName(fieldCount), dataReader[fieldCount]);
        return dataRow;
    }

Usage:

List<dynamic> MyList = MyDbContext.CollectionFromSql("SELECT * FROM \"User\" WHERE UserID = @UserID",
            new Dictionary<string, object> { { "@UserID", 1 } }).ToList();

You will notice I used a Dictionay<string, object> since there are still some issues with passing a DbParameter currently - Support passing DbParameter instances to raw SQL APIs #3115

[d668]

@ChristineBoersen tried your code and got

'Database' does not contain a definition for 'GetDbConnection' and no extension method 'GetDbConnection' accepting a first argument of type 'Database' could be found (are you missing a using directive or an assembly reference?)

[ChristineBoersen]

Unfortunately, the code snippet above was written before the framework went to RTM, so I would expect it to break at this point. I've edited the post to indicate that as I've moved away from EF for my current projects.

[divega]

@intergleam looking for this? https://github.com/aspnet/EntityFramework/blob/dev/src/Microsoft.EntityFrameworkCore.Relational/RelationalDatabaseFacadeExtensions.cs#L154

[fixko]

In ChristineBoersen code Just replace dbContext.Database.GetDbConnection().CreateCommand() with
dbContext.Database.Connection.CreateCommand()

[babucr]

just change GetDbConnection
using (var cmd = dbContext.Database.Connection.CreateCommand())

[Displee]

CollectionFromSql doesnt exist on type DbContext.

[ImGonaRot]

@Displee You need to "extend" the DbContext class and copy the code provided by @ChristineBoersen

The method is an extension method.
public static IEnumerable<dynamic> CollectionFromSql(this DbContext dbContext, string Sql, Dictionary<string, object> Parameters)
Notice the "this DbContext dbContext" parameter on the "CollectionFromSql" method? This is called an "extension" method.
https://docs.microsoft.com/en-us/dotnet/csharp/programming-guide/classes-and-structs/extension-methods

[rahul230691]

Hi @ChristineBoersen, Thank you for the solution. I am using the same to execute my stored procedure that returns dynamic fields. but DataReader doesn't return any rows. However procedure returning 2 rows with 8 columns. Even DataReader returing 8 in fieldscount, which is same as procedure returning.

public static IEnumerable<dynamic> CollectionFromSql(this DbContext dbContext, string Sql, Dictionary<string, object> Parameters)
        {
            using (var cmd = dbContext.Database.GetDbConnection().CreateCommand())
            {
                cmd.CommandText = Sql;
                cmd.CommandType = CommandType.StoredProcedure;
                if (cmd.Connection.State != ConnectionState.Open)
                    cmd.Connection.Open();

                foreach (KeyValuePair<string, object> param in Parameters)
                {
                    DbParameter dbParameter = cmd.CreateParameter();
                    dbParameter.ParameterName = param.Key;
                    dbParameter.Value = param.Value;
                    cmd.Parameters.Add(dbParameter);
                }                                
                using (var dataReader = cmd.ExecuteReader())
                {
                    while (dataReader.Read())
                    {
                        var dataRow = GetDataRow(dataReader);
                        yield return dataRow;
                    }
                }
            }
        }

and here calling method -

Dictionary<string, object> parameters = new Dictionary<string, object>();
parameters.Add("@StartDate", (object)startDate ?? DBNull.Value);
parameters.Add("@EndDate", (object)endDate ?? DBNull.Value);
parameters.Add("@ClientID", (object)clientID ?? DBNull.Value);
parameters.Add("@ProgramID", (object)programID ?? DBNull.Value);

var results = _dbContext.CollectionFromSql("GET_DARReport", parameters).ToList();

Here is my procedure result.

Any help please??