Anonymous type result from sql query execution entity framework

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

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()

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.

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

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

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