Use dynamic type in Entity Framework 4.1 SqlQuery() method

As the release of Entity Framework 4.1, the DbContext.Database.SqlQuery() method offers a way to execute a SQL command then maps the returning result set to a strong typed object or a list of strong typed objects. However, if you want to use the dynamic type or anonymous type as its return type, you will probably get your code compiled but receive exceptions during runtime.

The reason is that the Entity Framework does the type mapping using Reflection. Additionally, Entity Framework searches each property on your returning type, and do mapping by matching the property name and the SQL returning column name, if your type doesn’t have any public property (public fields don’t work) defined, there will be no mappings happen.

To use the dynamic type on SqlQuery method, you must find a way to dynamically construct a type, then add property definitions to this type. To do so, you will be required to use Reflection Emit technology.

Reflection Emit is a way to runtime inject IL code into a specified AppDomain. By using Emit, you can actually create dynamic assemblies, modules or types on any AppDomain in your application. The following Console application illustrates how to use Reflection Emit to use dynamic as returning types of SqlQuery method.

 using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Entity;
using System.Collections;
using System.Reflection.Emit;
using System.Reflection;
 
namespace Demo
{
    public class Program
    {
        public static void Main(string[] args)
        {
            string connectionString = "Server=(local); Integrated Security=true; Database=master";
            using (DbContext context = new DbContext(connectionString))
            {
                TypeBuilder builder = Program.CreateTypeBuilder(
                    "MyDynamicAssembly", "MyModule", "MyType");
                Program.CreateAutoImplementedProperty(builder, "name", typeof(string));
                Program.CreateAutoImplementedProperty(builder, "type", typeof(string));
                Program.CreateAutoImplementedProperty(builder, "id", typeof(int));
 
                Type resultType = builder.CreateType();
 
                dynamic queryResult = context.Database.SqlQuery(
                    resultType, "SELECT * FROM sys.sysobjects");
 
                Console.WriteLine("{0,20} {1,4} {2,10}", "Name", "Type", "ID");
                foreach (dynamic item in queryResult)
                {
                    Console.WriteLine("{0,10} {1,4} {2,10}", item.name, item.type, item.id);
                }
            }
 
            Console.ReadKey();
        }
 
        public 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;
        }
 
        public 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, 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);
        }
    }
}

The anonymous types may not work as well because there is no default constructor for anonymous types. Hope this helps.

Comments

• Anonymous
  June 14, 2016
  Excellent.....Thanks exactly what i want.