Click here to Skip to main content
14,451,808 members

Use dynamic type in Entity Framework 4.1 SqlQuery() method

Rate this:
4.17 (4 votes)
Please Sign up or sign in to vote.
4.17 (4 votes)
4 Jun 2011Ms-PL
The DbContext.Database.SqlQuery() method offers a way to execute a SQL command then map the returning result set to a strongly typed object or a list of strongly typed objects.

With the release of Entity Framework 4.1, the DbContext.Database.SqlQuery() method offers a way to execute a SQL command then map the returning result set to a strongly typed object or a list of strongly 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 Entity Framework does the type mapping using Reflection. Additionally, Entity Framework searches each property on your returning type, and does 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.

To use the dynamic type on the 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 return types of the 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.

License

This article, along with any associated source code and files, is licensed under The Microsoft Public License (Ms-PL)

Share

About the Author

mazhou
Software Developer (Senior) Microsoft
Singapore Singapore
Mark is a Windows developer, he mainly works for building libraries and tools for the developers, and as well he designs and implements Windows based applications.

Comments and Discussions

 
QuestionScope of the TypeBuilder Pin
LiQuick24-Dec-15 2:43
MemberLiQuick24-Dec-15 2:43 
QuestionThanks for this... but I have a question. Pin
kiasta10-Apr-15 11:56
Memberkiasta10-Apr-15 11:56 
GeneralMy vote of 4 Pin
AntonioLopes30-May-13 0:42
MemberAntonioLopes30-May-13 0:42 
BugDateTime field are not fill Pin
Davoldaren12-Oct-12 6:17
MemberDavoldaren12-Oct-12 6:17 
QuestionJust Amazing Pin
Mehdy Khoshrou21-Apr-12 10:31
MemberMehdy Khoshrou21-Apr-12 10:31 
SuggestionImprovement to the idea Pin
stankovski28-Oct-11 7:34
Memberstankovski28-Oct-11 7:34 
GeneralRe: Improvement to the idea Pin
kingcomxu21-Aug-13 22:06
Memberkingcomxu21-Aug-13 22:06 
GeneralRe: Improvement to the idea Pin
Bhaskar P12-Oct-16 19:12
MemberBhaskar P12-Oct-16 19:12 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Technical Blog
Posted 4 Jun 2011

Stats

93.1K views
15 bookmarked