Use dynamic type in Entity Framework 4.1 SqlQuery() method
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.
发表评论
t8vAZS Wow, great post.Thanks Again. Really Cool.
Wu4Jv8 Thanks, However I am having difficulties with
lxqILT wow, awesome blog.Thanks Again.
h2Ta0K Muchos Gracias for your article post.Much thanks again. Fantastic.
LKeh66 Awesome blog post.Thanks Again. Awesome.
vbp2mR I am so grateful for your post.
9pFTzT Great, thanks for sharing this article post.Really looking forward to read more.
PSzYRh I loved your blog.Really looking forward to read more. Fantastic.
O5rzUx Fantastic blog.Really thank you! Much obliged.