Return Anonymous Type using SqlQuery RAW Query in Entity Framework

.net c# entity-framework mysql sql


How can I make Entity Framework SqlQuery to return an Anonymous type.

Right now I run a context.TheObject.SqlQuery() RAW query. the query joins two tables and I want to return the results of the joined tables.

If I use it with a type context.TheObject.SqlQuery() I only get to see the results of the table of that same type.

I tried db.Database.SqlQuery<DbResults>("the sql query here"); With a pre-defined class that matches the result's objects, but all the fields are null.

Using Entity Framework 6 with MySQL.

4/24/2015 4:37:01 PM

Accepted Answer

I'm going out on a limb here, and will try to address your underlying problem instead of directly answering your question.

Your scenario with the pre-defined class should work. A likely pitfall is that the column names and the properties of your class did not match up.

Sample code (LinqPad)

    var results = Database.SqlQuery<TestResult>("select r.Name, b.BankName from relation r inner join BankAccount b on b.RelationId = r.Id where r.Id = 2");

public class TestResult {
    public string Name { get; set; }
    public string BankName { get; set; }

I'd strongly advise you to revisit your problematic code using explicit types.

In direct response to your question: no, you can't return anonymous types from SqlQuery. The best you can do is build dynamic objects, but that unfortunately requires a fair bit of manual work using TypeBuilder. See for a sample.

11/10/2015 7:25:59 PM

Popular Answer

Here's what I did.

  1. Execute sp and get the results into a data reader
public static async Task<IEnumerable<object>> GetAnonymousResults(IUnitOfWork unitOfWork, string spName, SqlParameter[] outParameters, params SqlParameter[] parameters)

            //meh, you only need the context here. I happened to use UnitOfWork pattern and hence this.
            var context = unitOfWork as DbContext;

            DbCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = spName;
            command.Connection = context.Database.Connection;


            //Forget this if you don't have any out parameters

                var reader = await command.ExecuteReaderAsync();
                return reader.ToObjectList();//A custom method implemented below
  1. Read individual values from each row into a expando object and put the list of expando objects into an array
            public static List<object> ToObjectList(this IDataReader dataReader, bool ignoreUnmappedColumns = true)
                var list = new List<object>();
                while (dataReader.Read())
                    IEnumerable<string> columnsName = dataReader.GetColumnNames();//A custom method implemented below 
                    var obj = new ExpandoObject() as IDictionary<string, object>;

                    foreach (var columnName in columnsName)
                        obj.Add(columnName, dataReader[columnName]);
                    var expando = (ExpandoObject)obj;


                return list;
  1. Get the list of columns by using the reader.GetSchemaTable() method
           public static IEnumerable<string> GetColumnNames(this IDataReader reader)
                    var schemaTable = reader.GetSchemaTable();
                    return schemaTable == null
                        ? Enumerable.Empty<string>()
                        : schemaTable.Rows.OfType<DataRow>().Select(row => row["ColumnName"].ToString());


 var results =
                      StandaloneFunctions.GetAnonymousResults(_unitOfWork, "spFind",

In my case, I happened to use SPs but this should work with queries. All you have to do is replace the command with the following ( and remove all the parameter passing)

command.CommandType = CommandType.Text;
command.CommandText = "select * from SomeTable";

Related Questions


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow