如果您有一个实体,只想获取其中一些属性,那么可以借助反射来获得更好的解决方案。
这段代码基于上面答案中的示例。
除此之外,您还可以指定要获取回来的类型和字段数组。
结果的类型为IEnumerable。
public static class DatabaseExtension
{
public static IEnumerable<T> DynamicSqlQuery<T>(this Database database, string[] fields, string sql, params object[] parameters) where T : new()
{
var type = typeof (T);
var builder = CreateTypeBuilder("MyDynamicAssembly", "MyDynamicModule", "MyDynamicType");
foreach (var field in fields)
{
var prop = type.GetProperty(field);
var propertyType = prop.PropertyType;
CreateAutoImplementedProperty(builder, field, propertyType);
}
var resultType = builder.CreateType();
var items = database.SqlQuery(resultType, sql, parameters);
foreach (object item in items)
{
var obj = new T();
var itemType = item.GetType();
foreach (var prop in itemType.GetProperties(BindingFlags.Instance | BindingFlags.Public))
{
var name = prop.Name;
var value = prop.GetValue(item, null);
type.GetProperty(name).SetValue(obj, value);
}
yield return obj;
}
}
private 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;
}
private static void CreateAutoImplementedProperty(TypeBuilder builder, string propertyName, Type propertyType)
{
const string privateFieldPrefix = "m_";
const string getterPrefix = "get_";
const string setterPrefix = "set_";
FieldBuilder fieldBuilder = builder.DefineField(
string.Concat(privateFieldPrefix, propertyName),
propertyType, FieldAttributes.Private);
PropertyBuilder propertyBuilder = builder.DefineProperty(
propertyName, PropertyAttributes.HasDefault, propertyType, null);
MethodAttributes propertyMethodAttributes =
MethodAttributes.Public | MethodAttributes.SpecialName |
MethodAttributes.HideBySig;
MethodBuilder getterMethod = builder.DefineMethod(
string.Concat(getterPrefix, propertyName),
propertyMethodAttributes, propertyType, Type.EmptyTypes);
ILGenerator getterILCode = getterMethod.GetILGenerator();
getterILCode.Emit(OpCodes.Ldarg_0);
getterILCode.Emit(OpCodes.Ldfld, fieldBuilder);
getterILCode.Emit(OpCodes.Ret);
MethodBuilder setterMethod = builder.DefineMethod(
string.Concat(setterPrefix, propertyName),
propertyMethodAttributes, null, new Type[] { propertyType });
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);
}
}
您可以这样调用:
var fields = new[]{ "Id", "FirstName", "LastName" };
var sql = string.Format("SELECT {0} FROM People WHERE Id = @id", string.Join(", ", fields));
var person = db.Database.DynamicSqlQuery<People>(fields, sql, new SqlParameter("id", id))
.FirstOrDefault();
实际上,它仅适用于简单类型,并且没有错误处理。