//var x = DynamicSqlQuery.DynamicSqlQueryMethod(db.Database, sql, null).ToListAsync();
//TypeBuilder builder = DynamicSqlQuery.CreateTypeBuilder("MyDynamicAssembly", "MyModule", "MyType");
//DynamicSqlQuery.CreateAutoImplementedProperty(builder, "TodayPay", typeof(int));
//DynamicSqlQuery.CreateAutoImplementedProperty(builder, "MonthPay", typeof(int));
//DynamicSqlQuery.CreateAutoImplementedProperty(builder, "TodayNewAgent", typeof(int));
//DynamicSqlQuery.CreateAutoImplementedProperty(builder, "MonthNewAgent", typeof(int));
//DynamicSqlQuery.CreateAutoImplementedProperty(builder, "TodayNewUser", typeof(int));
//DynamicSqlQuery.CreateAutoImplementedProperty(builder, "MonthNewUser", typeof(int));
//DynamicSqlQuery.CreateAutoImplementedProperty(builder, "TotalUser", typeof(int));
//Type resultType = builder.CreateType();
//var queryResult = db.Database.SqlQuery(resultType, sql).ToListAsync();
//var n=queryResult.Result.FirstOrDefault();
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using XLShouYou.Biz.Model;
using XLShouYou.Biz.Service;
using XLShouYou.Biz.Utilities;
using System.Data.Entity;
using System.Collections;
using System.Reflection.Emit;
using System.Reflection;
namespace XLShouYou.Biz
{
public static class DynamicSqlQuery
{
public static System.Data.Entity.Infrastructure.DbRawSqlQuery DynamicSqlQueryMethod(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);
//return database.SqlQuery(resultType, sql, parameters);
}
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, 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);
}
}
}
参考文章:
http://stackoverflow.com/questions/26749429/anonymous-type-result-from-sql-query-execution-entity-framework
http://www.codeproject.com/Articles/206416/Use-dynamic-type-in-Entity-Framework-SqlQuery
http://www.cnblogs.com/xishuai/p/entityframework_async_await_SaveChangesAsync_ToListAsync.html
http://www.cnblogs.com/artech/archive/2007/07/15/818980.html
http://www.cnblogs.com/dudu/p/async_await_parallel.html
http://www.cnblogs.com/lori/p/4142201.html
http://www.cnblogs.com/NanaLich/archive/2013/03/06/implementing-inotifypropertychanged-via-typebuilder.html