Click here to Skip to main content
13,740,709 members
Click here to Skip to main content
Add your own
alternative version

Stats

84.4K views
15 bookmarked
Posted 4 Jun 2011
Licenced Ms-PL

Use dynamic type in Entity Framework 4.1 SqlQuery() method

, 4 Jun 2011
Rate this:
Please Sign up or sign in to vote.
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.

You may also be interested in...

Pro
Pro

Comments and Discussions

 
QuestionScope of the TypeBuilder Pin
LiQuick24-Dec-15 1:43
memberLiQuick24-Dec-15 1:43 
QuestionThanks for this... but I have a question. Pin
kiasta10-Apr-15 10:56
memberkiasta10-Apr-15 10:56 
GeneralMy vote of 4 Pin
AntonioLopes29-May-13 23:42
memberAntonioLopes29-May-13 23:42 
BugDateTime field are not fill Pin
Davoldaren12-Oct-12 5:17
memberDavoldaren12-Oct-12 5:17 
QuestionJust Amazing Pin
Mehdy Khoshrou21-Apr-12 9:31
memberMehdy Khoshrou21-Apr-12 9:31 
SuggestionImprovement to the idea Pin
stankovski28-Oct-11 6:34
memberstankovski28-Oct-11 6:34 
GeneralRe: Improvement to the idea Pin
kingcomxu21-Aug-13 21:06
memberkingcomxu21-Aug-13 21:06 
GeneralRe: Improvement to the idea Pin
Bhaskar P12-Oct-16 18:12
memberBhaskar P12-Oct-16 18: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.

Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web05-2016 | 2.8.180920.1 | Last Updated 4 Jun 2011
Article Copyright 2011 by mazhou
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid