Anonymous type result from sql query execution entity framework

Question

I am using entity framework 5.0 with .net framework 4.0 code first approach. Now i know that i can run raw sql in entity framework by following

var students = Context.Database.SqlQuery<Student>("select * from student").ToList();

It's working perfectly but what I want is return anonymous results. For example I want only specific columns from student table like following

var students = Context.Database.SqlQuery<Student>("select FirstName from student").ToList();

It is not working. it gives exception

The data reader is incompatible with the specified 'MyApp.DataContext.Student'. A member of the type, 'StudentId', does not have a corresponding column in the data reader with the same name.

So I have tried dynamic type

var students = Context.Database.SqlQuery<dynamic>("select FirstName from student").ToList();

it is also not working, it returns an empty object. No data available in it.

Is there any way to get anonymous type result from a dynamic SQL query?

What is Student class and what is Student table? – abatishchev Nov 05 '14 at 05:10 — abatishchev, Nov 05 '14 at 05:10

score 49 · Answer 1 · answered Feb 23 '17 at 14:52

You have to use raw Sql for that, the entitity framework SqlQuery<T> will only work for objects with known types.

here is the method I use :

public static IEnumerable<dynamic> DynamicListFromSql(this DbContext db, string Sql, Dictionary<string, object> Params)
{
    using (var cmd = db.Database.Connection.CreateCommand())
    {
        cmd.CommandText = Sql;
        if (cmd.Connection.State != ConnectionState.Open) { cmd.Connection.Open(); }

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

        using (var dataReader = cmd.ExecuteReader())
        {
            while (dataReader.Read())
            {
                var row = new ExpandoObject() as IDictionary<string, object>;
                for (var fieldCount = 0; fieldCount < dataReader.FieldCount; fieldCount++)
                {
                    row.Add(dataReader.GetName(fieldCount), dataReader[fieldCount]);
                }
                yield return row;
            }
        }
    }
}

You can call it like this :

List<dynamic> results = DynamicListFromSql(myDb,"select * from table where a=@a and b=@b", new Dictionary<string, object> { { "a", true }, { "b", false } }).ToList();

Thanks for the response. One may also use`Newtonsoft.Json.Linq.JObject`instead of `ExpandoObject` — Ehsan88, Dec 03 '17 at 15:28
answer looks like https://github.com/aspnet/EntityFrameworkCore/issues/2344#issuecomment-172641417 — MattBH, Aug 21 '18 at 20:16
A possible update to add would be `dbParameter.Value = p.Value == null ? DBNull.Value : p.Value;` in case you are passing null values — LUKE, Mar 21 '20 at 15:34
Great solution! One small correction - since this is an extension method, I think the usage example needs to be "List results = myDb.DynamicListFromSql("select * from table..." — Oggy, Sep 14 '20 at 19:59

Manish Parakhiya · Accepted Answer · 2017-08-05T03:57:14.223

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);
        }

The solutions suits best, if the select query is to retrieve from a single table and single entity. Can we improvise this to implement for Select query with JOINS where we will have multiple tables and need to bind to different TypeBuilders. Thoughts? — gee'K'iran, Aug 25 '15 at 15:14
How do you use this? I am trying to use this in code but am unable to. Can you show sample ways to call? Thank you!!! — TheGeekYouNeed, Jan 20 '16 at 01:02
The OP was a question about `SqlQuery`. This does not use that function. — David Dombrowsky, Mar 03 '20 at 14:56

score 6 · Answer 3 · answered Nov 05 '14 at 04:38

6

You can try the code from here, scroll down and find the implement of stankovski: http://www.codeproject.com/Articles/206416/Use-dynamic-type-in-Entity-Framework-SqlQuery

After copying the code into a static class, you can call this function to get what you want:

var students = Context.Database.DynamicSqlQuery("select FirstName from student").ToList()

answered Nov 05 '14 at 04:38

Huy Hoang Pham

4,107
1
16
28

1

thanks, i'm struggling as to where to put the code exactly,controller helper class? With it being public static – John May 28 '15 at 04:58
2

Looks like there is another way that is much simpler. Go here and look at the post by ChristineBoersen -- https://github.com/aspnet/EntityFramework/issues/2344 – goroth Oct 15 '16 at 23:41
1

@goroth please note that you are referencing EF core. – JP Hellemons Sep 28 '17 at 13:46

ms007 · Answer 4 · 2016-09-06T09:00:25.620

If you have an entity and you only want some of the properties back you can get an even better solution with the help of reflection.

This code builds up on the same sample as in the answer above.

In addition to this you can specify a type and an array of fields you want to get back.

The result is of type IEnumerable.

public static class DatabaseExtension
{
    public static IEnumerable<T> DynamicSqlQuery<T>(this Database database, string[] fields, string sql, params object[] parameters) where T : new()
    {
        var type = typeof (T);

        var builder = CreateTypeBuilder("MyDynamicAssembly", "MyDynamicModule", "MyDynamicType");

        foreach (var field in fields)
        {
            var prop = type.GetProperty(field);
            var propertyType = prop.PropertyType;
            CreateAutoImplementedProperty(builder, field, propertyType);
        }

        var resultType = builder.CreateType();

        var items = database.SqlQuery(resultType, sql, parameters);
        foreach (object item in items)
        {
            var obj = new T();
            var itemType = item.GetType();
            foreach (var prop in itemType.GetProperties(BindingFlags.Instance | BindingFlags.Public))
            {
                var name = prop.Name;
                var value = prop.GetValue(item, null);
                type.GetProperty(name).SetValue(obj, value);
            }
            yield return obj;
        }
    }

    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, 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);
    }    
}

You can call it this way:

var fields = new[]{ "Id", "FirstName", "LastName" };
var sql = string.Format("SELECT {0} FROM People WHERE Id = @id", string.Join(", ", fields));

var person = db.Database.DynamicSqlQuery<People>(fields, sql, new SqlParameter("id", id))
    .FirstOrDefault();

Actually it works on simple types only and there is no error handling.

changed from dynamic to object in foreach (object item in items) — ms007, Sep 06 '16 at 09:01

Uğur Demirel · Answer 5 · 2018-11-21T13:52:21.463

I using it like that

ORMClass:

public class ORMBase<T, TContext> : IORM<T>
        where T : class
        where TContext : DbContext, IDisposable, new()

Method:

public IList<TResult> GetSqlQuery<TResult>(string sql, params object[] sqlParams)
{
                using (TContext con = new TContext())
                {
                    return Enumerable.ToList(con.Database.SqlQuery<TResult>(sql, sqlParams));
                }
}

And finally using

public class ResimORM : ORMBase<Resim, mdlOgrenciKulup>, IDisposable
{
    public void Dispose() { GC.SuppressFinalize(this); }
}





ResimORM RE_ORM = new ResimORM();
List<Resim> a = RE_ORM.GetSqlQuery<Resim>(sql,null).ToList();
int b = RE_ORM.GetSqlQuery<int>(sql,null).FirstOrDefault();

score -1 · Answer 6 · answered Aug 14 '20 at 12:33

I know it can be a little bit lat, but you can use method like this if you want to execute Scalar function and always cast it to uniform type.

Just use this forceStringCast parameter to force-cast sql scalar function result to string, then use this string as you want.

public static Task<T> ScalarFunction<T>(DbContext db, string sql, bool forceStringCast = false, Dictionary<string, object> parameters = null) 
    {
        string cmdText;

        if (forceStringCast)
        {
            cmdText = $@"SELECT CAST({sql}({string.Join(",", 
                parameters.Keys.Select(p => "@" + p).ToList())} AS VARCHAR(MAX)));";
        }
        else
        {
            cmdText =
                $@"SELECT {sql}({string.Join(",", 
                    parameters.Keys.Select(p => "@" + p).ToList())});";
        }

        return db.Database.SqlQuery<T>(cmdText,parameters.Select(p => new SqlParameter(p.Key, p.Value)).ToArray()).FirstOrDefaultAsync();
    }

score -2 · Answer 7 · answered Jan 16 '21 at 22:16

This is my solution:

DbContext.Database.Connection.Open();

            var cmd = DbContext.Database.Connection.CreateCommand();
            cmd.CommandText = @"Select * from Table where Param1 = @Param1 and Param2 = @Param2";
            cmd.Parameters.Add(new SqlParameter("Param1", Mahdi));
            cmd.Parameters.Add(new SqlParameter("Param2", 20));

            List<Dictionary<string, object>> items = new List<Dictionary<string, object>>();
            var reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                Dictionary<string, object> item = new Dictionary<string, object>();

                for (int i = 0; i < reader.FieldCount; i++)
                    item[reader.GetName(i)] = (reader[i]);
                items.Add(item);
            }

            return Request.CreateResponse(HttpStatusCode.OK, items);

Hope help you all ;)

This doesn't answer the question, and the one and only correct answer has already been given. (And please don't post identical answers to multiple questions). — Gert Arnold, Jan 16 '21 at 22:29
Dear Gert Arnold, This is correct, short and best solution for asked question. Check it again ;) — Mahdi, Jan 18 '21 at 04:52
The question is about `SqlQuery` and thehn, this answer has already been geven. — Gert Arnold, Jan 18 '21 at 07:55

Anonymous type result from sql query execution entity framework

7 Answers7

Linked