c# - 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?

question from:https://stackoverflow.com/questions/26749429/anonymous-type-result-from-sql-query-execution-entity-framework

Answer 1

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