Anonymous Type Result from SQL Query Execution Entity Framework

Return Anonymous Type using SqlQuery RAW Query in Entity Framework

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

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 http://www.codeproject.com/Articles/206416/Use-dynamic-type-in-Entity-Framework-SqlQuery for a sample.

Anonymous type result from sql query execution entity framework

Here is final solution that worked fine for me.

public static System.Collections.IEnumerable DynamicSqlQuery(this Database database, string sql, params object[] parameters)
        {
            TypeBuilder builder = createTypeBuilder(
                    "MyDynamicAssembly", "MyDynamicModule", "MyDynamicType");

            using (System.Data.IDbCommand command = database.Connection.CreateCommand())
            {
                try
                {
                    database.Connection.Open();
                    command.CommandText = sql;
                    command.CommandTimeout = command.Connection.ConnectionTimeout;
                    foreach (var param in parameters)
                    {
                        command.Parameters.Add(param);
                    }

                    using (System.Data.IDataReader reader = command.ExecuteReader())
                    {
                        var schema = reader.GetSchemaTable();

                        foreach (System.Data.DataRow row in schema.Rows)
                        {
                            string name = (string)row["ColumnName"];
                            //var a=row.ItemArray.Select(d=>d.)
                            Type type = (Type)row["DataType"];
                            if(type!=typeof(string) && (bool)row.ItemArray[schema.Columns.IndexOf("AllowDbNull")])
                            {
                                type = typeof(Nullable<>).MakeGenericType(type);
                            }
                            createAutoImplementedProperty(builder, name, type);
                        }
                    }
                }
                finally
                {
                    database.Connection.Close();
                    command.Parameters.Clear();
                }
            }

            Type resultType = builder.CreateType();

            return database.SqlQuery(resultType, sql, parameters);
        }

        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, System.Reflection.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);
        }    

How does Entity Framework manage mapping query result to anonymous type?

Here’s my solution all the way down of privates and internals. It travels with reflection into cached query plan which will exist after ToTraceString call or query execution to get what is called _columnMap. Column map contains ScalarColumnMap objects going in the order of anonymous object’s properties and pointing to the corresponding column position with ColumnPos property.

using System;
using System.Data.Objects;
using System.Reflection;

static class EFQueryUtils
{
    public static int[] GetPropertyPositions(ObjectQuery query)
    {
        // get private ObjectQueryState ObjectQuery._state;
        // of actual type internal class
        //      System.Data.Objects.ELinq.ELinqQueryState
        object queryState = GetProperty(query, "QueryState");
        AssertNonNullAndOfType(queryState, "System.Data.Objects.ELinq.ELinqQueryState");

        // get protected ObjectQueryExecutionPlan ObjectQueryState._cachedPlan;
        // of actual type internal sealed class
        //      System.Data.Objects.Internal.ObjectQueryExecutionPlan
        object plan = GetField(queryState, "_cachedPlan");
        AssertNonNullAndOfType(plan, "System.Data.Objects.Internal.ObjectQueryExecutionPlan");

        // get internal readonly DbCommandDefinition ObjectQueryExecutionPlan.CommandDefinition;
        // of actual type internal sealed class
        //      System.Data.EntityClient.EntityCommandDefinition
        object commandDefinition = GetField(plan, "CommandDefinition");
        AssertNonNullAndOfType(commandDefinition, "System.Data.EntityClient.EntityCommandDefinition");

        // get private readonly IColumnMapGenerator EntityCommandDefinition._columnMapGenerator;
        // of actual type private sealed class
        //      System.Data.EntityClient.EntityCommandDefinition.ConstantColumnMapGenerator
        object columnMapGenerator = GetField(commandDefinition, "_columnMapGenerator");
        AssertNonNullAndOfType(columnMapGenerator, "System.Data.EntityClient.EntityCommandDefinition+ConstantColumnMapGenerator");

        // get private readonly ColumnMap ConstantColumnMapGenerator._columnMap;
        // of actual type internal class
        //      System.Data.Query.InternalTrees.SimpleCollectionColumnMap
        object columnMap = GetField(columnMapGenerator, "_columnMap");
        AssertNonNullAndOfType(columnMap, "System.Data.Query.InternalTrees.SimpleCollectionColumnMap");

        // get internal ColumnMap CollectionColumnMap.Element;
        // of actual type internal class
        //      System.Data.Query.InternalTrees.RecordColumnMap
        object columnMapElement = GetProperty(columnMap, "Element");
        AssertNonNullAndOfType(columnMapElement, "System.Data.Query.InternalTrees.RecordColumnMap");

        // get internal ColumnMap[] StructuredColumnMap.Properties;
        // array of internal abstract class
        //      System.Data.Query.InternalTrees.ColumnMap
        Array columnMapProperties = GetProperty(columnMapElement, "Properties") as Array;
        AssertNonNullAndOfType(columnMapProperties, "System.Data.Query.InternalTrees.ColumnMap[]");

        int n = columnMapProperties.Length;
        int[] propertyPositions = new int[n];
        for (int i = 0; i < n; ++i)
        {
            // get value at index i in array
            // of actual type internal class
            //      System.Data.Query.InternalTrees.ScalarColumnMap
            object column = columnMapProperties.GetValue(i);
            AssertNonNullAndOfType(column, "System.Data.Query.InternalTrees.ScalarColumnMap");

            //string colName = (string)GetProp(column, "Name");
            // can be used for more advanced bingings

            // get internal int ScalarColumnMap.ColumnPos;
            object columnPositionOfAProperty = GetProperty(column, "ColumnPos");
            AssertNonNullAndOfType(columnPositionOfAProperty, "System.Int32");

            propertyPositions[i] = (int)columnPositionOfAProperty;
        }
        return propertyPositions;
    }

    static object GetProperty(object obj, string propName)
    {
        PropertyInfo prop = obj.GetType().GetProperty(propName, BindingFlags.NonPublic | BindingFlags.Instance);
        if (prop == null) throw EFChangedException();
        return prop.GetValue(obj, new object[0]);
    }

    static object GetField(object obj, string fieldName)
    {
        FieldInfo field = obj.GetType().GetField(fieldName, BindingFlags.NonPublic | BindingFlags.Instance);
        if (field == null) throw EFChangedException();
        return field.GetValue(obj);
    }

    static void AssertNonNullAndOfType(object obj, string fullName)
    {
        if (obj == null) throw EFChangedException();
        string typeFullName = obj.GetType().FullName;
        if (typeFullName != fullName) throw EFChangedException();
    }

    static InvalidOperationException EFChangedException()
    {
        return new InvalidOperationException("Entity Framework internals has changed, please review and fix reflection code");
    }
}

I think some assertions can be relaxed to check not the exact type but base type containing necessary property.

Is there a solution without reflection?

How to return anonymous type from EF sql query

It appears that EF doesn't support returning any types other than ones that have EF mappings when using the ObjectContext.ExecuteStoreQuery() method.

I ended up using a SqlCommand object and datareader with an anonymous type.

Compilied queries with return anonymous type

Try this one: http://social.msdn.microsoft.com/Forums/en/adodotnetentityframework/thread/3782e05c-8a21-462c-950d-3410072c185e

EF Core cannot convert from query select of anonymous type to DTO object class

When a compiler encounters a anonymous type it internally creates a class with a name which is auto generated. As a result even though your anonymous type and UserPoolDto dto have same properties they are two different types or classes altogether. So when we try to assign one with another and try to build it eventually results in a cast exception.

There are certain ways to solve the problem in hand by doing a cast as discussed here. However the best approach would be to directly create a dto instance inside of your select.

var pools = await _context.Pools
       .Include(p => p.Type)
       .Include(p => p.Type.Category)
       .Include(p => p.Style)
       .Select(p => new UserPoolDto
       {
           Id = p.Id,
           Name = p.Name,
           Key = p.Key,
           TypeName = p.Type.Name,
           CategoryName = p.Type.Category.Name,
           StyleName = p.Style.Name,
           RegCost = p.RegCost,
           RegStartTimeUTC = p.RegStartTimeUTC,
           RegEndTimeUTC = p.RegEndTimeUTC,
           Icon = p.Icon,
           Description = p.Description,
           Instructions = p.Instructions,
           CreatedBy = p.CreatedBy,
       })
       .Where(x => x.CreatedBy == request.UserId)
       .ToListAsync(cancellationToken);

Get Values from anonymous type

To use your properties as an anonymous type you can cast it to a dynamic.

Eg:

var Selected = dgvShow.CurrentRow.DataBoundItem;
var objDynamic = (dynamic)Selected;
Console.WriteLine(objDynamic.idStudent);

I would recommend using a model instead of an anonymous type and unbox your DataBoundItem into your model, it will benefit accessibility of your property names via IntelliSense and make it more manageable for other people to maintain.

---

Related Topics