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?

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