How to Return Dynamic Object from SQL Query

Anonymous type result from sql query execution entity framework

Here is final solution that worked fine for me.

public static System.Collections.IEnumerable DynamicSqlQuery(this Database database, string sql, params object[] parameters)
        {
            TypeBuilder builder = createTypeBuilder(
                    "MyDynamicAssembly", "MyDynamicModule", "MyDynamicType");

            using (System.Data.IDbCommand command = database.Connection.CreateCommand())
            {
                try
                {
                    database.Connection.Open();
                    command.CommandText = sql;
                    command.CommandTimeout = command.Connection.ConnectionTimeout;
                    foreach (var param in parameters)
                    {
                        command.Parameters.Add(param);
                    }

                    using (System.Data.IDataReader reader = command.ExecuteReader())
                    {
                        var schema = reader.GetSchemaTable();

                        foreach (System.Data.DataRow row in schema.Rows)
                        {
                            string name = (string)row["ColumnName"];
                            //var a=row.ItemArray.Select(d=>d.)
                            Type type = (Type)row["DataType"];
                            if(type!=typeof(string) && (bool)row.ItemArray[schema.Columns.IndexOf("AllowDbNull")])
                            {
                                type = typeof(Nullable<>).MakeGenericType(type);
                            }
                            createAutoImplementedProperty(builder, name, type);
                        }
                    }
                }
                finally
                {
                    database.Connection.Close();
                    command.Parameters.Clear();
                }
            }

            Type resultType = builder.CreateType();

            return database.SqlQuery(resultType, sql, parameters);
        }

        private static TypeBuilder createTypeBuilder(
            string assemblyName, string moduleName, string typeName)
        {
            TypeBuilder typeBuilder = AppDomain
                .CurrentDomain
                .DefineDynamicAssembly(new AssemblyName(assemblyName),
                                       AssemblyBuilderAccess.Run)
                .DefineDynamicModule(moduleName)
                .DefineType(typeName, TypeAttributes.Public);
            typeBuilder.DefineDefaultConstructor(MethodAttributes.Public);
            return typeBuilder;
        }

        private static void createAutoImplementedProperty(
            TypeBuilder builder, string propertyName, Type propertyType)
        {
            const string PrivateFieldPrefix = "m_";
            const string GetterPrefix = "get_";
            const string SetterPrefix = "set_";

            // Generate the field.
            FieldBuilder fieldBuilder = builder.DefineField(
                string.Concat(PrivateFieldPrefix, propertyName),
                              propertyType, FieldAttributes.Private);

            // Generate the property
            PropertyBuilder propertyBuilder = builder.DefineProperty(
                propertyName, System.Reflection.PropertyAttributes.HasDefault, propertyType, null);

            // Property getter and setter attributes.
            MethodAttributes propertyMethodAttributes =
                MethodAttributes.Public | MethodAttributes.SpecialName |
                MethodAttributes.HideBySig;

            // Define the getter method.
            MethodBuilder getterMethod = builder.DefineMethod(
                string.Concat(GetterPrefix, propertyName),
                propertyMethodAttributes, propertyType, Type.EmptyTypes);

            // Emit the IL code.
            // ldarg.0
            // ldfld,_field
            // ret
            ILGenerator getterILCode = getterMethod.GetILGenerator();
            getterILCode.Emit(OpCodes.Ldarg_0);
            getterILCode.Emit(OpCodes.Ldfld, fieldBuilder);
            getterILCode.Emit(OpCodes.Ret);

            // Define the setter method.
            MethodBuilder setterMethod = builder.DefineMethod(
                string.Concat(SetterPrefix, propertyName),
                propertyMethodAttributes, null, new Type[] { propertyType });

            // Emit the IL code.
            // ldarg.0
            // ldarg.1
            // stfld,_field
            // ret
            ILGenerator setterILCode = setterMethod.GetILGenerator();
            setterILCode.Emit(OpCodes.Ldarg_0);
            setterILCode.Emit(OpCodes.Ldarg_1);
            setterILCode.Emit(OpCodes.Stfld, fieldBuilder);
            setterILCode.Emit(OpCodes.Ret);

            propertyBuilder.SetGetMethod(getterMethod);
            propertyBuilder.SetSetMethod(setterMethod);
        }    

Database context and Return Dynamic Result Set in ASP.NET MVC

Finally i made is using TypeBuilder option suggested by "Mortalus" and ExpandoObject object. It has little performance overhead right now.

Take Typebuilder code from "Mortalus" answer then i made code according to my requirement as below.

List<Dictionary<string, object>> expandolist = new List<Dictionary<string, object>>();

foreach (var item in returndata)
  {
  IDictionary<string, object> expando = new ExpandoObject();
  foreach (PropertyDescriptor propertyDescriptor in TypeDescriptor.GetProperties(item))
     {
      var obj = propertyDescriptor.GetValue(item);
      expando.Add(propertyDescriptor.Name, obj);
     }
     expandolist.Add(new Dictionary<string, object>(expando));
  }

  return expandolist;

so now, I have "Dictionary" object from dynamic object. and using it you can work easily at design time rather then wait until runtime using "dynamic" object.

How can I get Entity Framework dynamic query result for web api result

This I suppose wouldn't work, because EF is not returning properties in the dynamic object at all, for explained case.

It works other way around, First EF Takes all properties of provided Generic Type attribute, Then it tries to map properties to SQL request results.

So if you don't know what is the list of fields will be as result EF wouldn't generate it for you.

First solution will be to parse select list to define the fields and generate class dynamicaly. Check how to create dynamic class discussion. Than you can do something like

var dType = MyTypeBuilder.CompileResultType("sample", new Dictionary<string, Type>
{
    { "Id", typeof(int) }
});
var db = new Model1().Database;
var sql = db.GetType().GetMethods().Where(m => m.Name == "SqlQuery" && m.IsGenericMethod);
var gen = sql.First().MakeGenericMethod(dType);

var result = gen.Invoke(db, new object[] { "[Your SQL]", new object[0] });
var ie = (result as IEnumerable<object>);

that is really complex, but works

Second - you can do classic ADO SQLCommand using context.Database.Connection and parse results

Third way to solve the problem is to Return XML from SQL, if possible.

var query = db.SqlQuery<string>("[Your SQL] for XML PATH('item'), root('root')").First();

that will return all data in XML format Like:

<root>
    <item>
        <Id>5</Id>
        ...
    </item>
    <item>
        <Id>6</Id>
        ...
    </item>
</root>

Deserialize SQL query result into dynamic object c#

After executing query then create a query with existing output in SQL and set aliases for every column.

Is it possible to return dynamic objects or Dataset from a Sqlite Query?

In the end I actually managed to come up with a method that will run any query and return the rows as items in the list and the columns as objects in the array:

    public List<object[]> RunSql(string sqlString, bool includeColumnNamesAsFirstRow)
    {
        var lstRes = new List<object[]>();
        SQLitePCL.sqlite3_stmt stQuery = null;
        try
        {
            stQuery = SQLite3.Prepare2(fieldStrikeDatabase.Connection.Handle, sqlString);
            var colLenght = SQLite3.ColumnCount(stQuery);

            if (includeColumnNamesAsFirstRow)
            {
                var obj = new object[colLenght];
                lstRes.Add(obj);
                for (int i = 0; i < colLenght; i++)
                {
                    obj[i] = SQLite3.ColumnName(stQuery, i);
                }
            }

            while (SQLite3.Step(stQuery) == SQLite3.Result.Row)
            {
                var obj = new object[colLenght];
                lstRes.Add(obj);
                for (int i = 0; i < colLenght; i++)
                {
                     var columnType = SQLitePCL.raw.sqlite3_column_decltype(stQuery, i);

                     switch (columnType)
                     {
                         case "text":
                              obj[i] = SQLite3.ColumnString(stQuery, i);
                              break;
                         case "int":
                               obj[i] = SQLite3.ColumnInt(stQuery, i);
                               break;
                         case "real":
                               obj[i] = SQLite3.ColumnDouble(stQuery, i);
                               break;
                         case "blob":
                               obj[i] = SQLite3.ColumnBlob(stQuery, i);
                               break;
                         case "null":
                               obj[i] = null;
                               break;
                      }
                }
            }
            return lstRes;
        }
        catch (Exception)
        {
            return null;
        }
        finally
        {
            if (stQuery != null)
            {
                SQLite3.Finalize(stQuery); 
            }
        }
    }

Is it possible to generate dynamic sql to return rows with various columns in snowflake

Given this input:

create or replace table t as
select parse_json($1) my_json
from values
('{ "FruitShape":"Round", "FruitSize":55 } '),
('{ "FruitShape":"Square"  } '),
('{ "FruitShape":"Oblong", "FruitSize":22, "FruitColor":"Chartreuse" } ')
;

This query will generate the dynanmic SQL:

select 'select ' 
  || (select listagg(distinct 'my_json:'||key::text, ',') from t, lateral flatten(input=>t.my_json, mode=>'OBJECT')) 
  || ' from t;';

Generated SQL and it's output:

select my_json:FruitShape, my_json:FruitSize, my_json:FruitColor from t;

MY_JSON:FRUITSHAPE | MY_JSON:FRUITSIZE | MY_JSON:FRUITCOLOR
-------------------+-------------------+-------------------
"Round"            | 55                | NULL              
"Square"           | NULL              | NULL              
"Oblong"           | 22                | "Chartreuse"      

This stored procedure will execute dynamic SQL without cut-and-paste:

create or replace procedure p()
returns string
language javascript
strict
execute as caller
as
$$
  const statement1 =  `
    select 'select ' 
      || (select listagg(distinct 'my_json:'||key::text, ', ') from t, lateral flatten(input=>t.my_json, mode=>'OBJECT')) 
      || ' from t'
  `
  const rs1 = snowflake.execute ({sqlText: statement1})
  rs1.next()
  const statement2 = rs1.getColumnValue(1)
  const rs2 = snowflake.execute ({sqlText: statement2})
  return 'SUCCESS'
$$
;

Then you can call the stored procedure and collect the results:

call p();
select * from table(result_scan(-2))

You mentioned constraining the output depending on some metadata. You can do that in the dynamic SQL, for example by filtering the distinct list of fields.

Credits to davidgarrison for the result_scan() technique!

Hope that's helpful.

C# Getting from dynamic object to SQL Insert statement using SqlKata (or alternatively just ADO.NET)

Given that result is of type IEnumerable<KeyValuePair<string,object>> the linq to get to the lists of columns and values is a simple linq Select. You may have nulls in the Key of the KeyValuePair so remove those first:

result = result.Where(x => x.Key != null);      
IEnumerable<string> columns = result.Select(p => p.Key);
IEnumerable<object> values = result.Select(p => p.Value);

This is a case where using var is more a hinderance than a help. If the columns and values had been declared as the type they are expected to be, the compiler would show immediately whether that matches whatever is produced by the linq query.

SqlKata function Insert is for a multiple insert, so expects a set of insertion value lists:

var queryResult = db.Query(r.TableRef).Insert(columns, new[]{values});

---

Related Topics