Returning anonymous type using SqlQuery RAW query in Entity Framework

How can I get Entity Framework SqlQuery to return anonymous type.

I am currently running a context.TheObject.SqlQuery()

RAW query . the query is joining 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 the results of a table of the same type.

I tried db.Database.SqlQuery<DbResults>("the sql query here")

; With a predefined class that matches the result objects, but all fields are null.

Using Entity Framework 6 with MySQL.

+3


source to share


2 answers


I'm going to a finiteness here and try to solve your main problem instead of directly answering your question.

Your script with a predefined class should work. The likely error is that the column names and properties of your class do not match.

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");
    results.Dump();
}

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

      



I highly recommend that you revisit your problematic code using explicit types.


As a direct answer to your question, no, you cannot return anonymous types from SqlQuery. The best you can do is build dynamic objects, but unfortunately this requires a little manual work using TypeBuilder. See http://www.codeproject.com/Articles/206416/Use-dynamic-type-in-Entity-Framework-SqlQuery for a sample.

+8


source


Here's what I did.

  • Execute sp and get results to 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;

            command.Parameters.AddRange(parameters);

            //Forget this if you don't have any out parameters
            command.Parameters.AddRange(outParameters);

            try
            {
                command.Connection.Open();
                var reader = await command.ExecuteReaderAsync();
                return reader.ToObjectList();//A custom method implemented below
            }
            finally
            {
                command.Connection.Close();
            }
        }

      

  1. Read individual values ​​from each line into an expando object and put a 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;

                    list.Add(expando);
                }

                return list;
            }

      

  1. Get list of columns using 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());
                }

      

Using

 var results =
                  await
                      StandaloneFunctions.GetAnonymousResults(_unitOfWork, "spFind",
                          outputParameters,parameters);

      

In my case, I had to use SP, but that should work with queries. All you have to do is replace the command with the following (and remove all parameters)

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

      

+3


source







All Articles