Результат анонимного типа из структуры объекта выполнения SQL-запроса
Я использую Entity Framework 5.0 с.net Framework 4.0 код первый подход. Теперь я знаю, что могу запустить raw sql в рамках сущности, выполнив
var students = Context.Database.SqlQuery<Student>("select * from student").ToList();
Это работает отлично, но я хочу вернуть анонимные результаты. Например, я хочу только определенные столбцы из таблицы ученика, такие как следующие
var students = Context.Database.SqlQuery<Student>("select FirstName from student").ToList();
Это не работает. это дает исключение
Считыватель данных несовместим с указанным "MyApp.DataContext.Student". Элемент типа "StudentId" не имеет соответствующего столбца в считывателе данных с тем же именем.
Итак, я попробовал dynamic тип
var students = Context.Database.SqlQuery<dynamic>("select FirstName from student").ToList();
он также не работает, он возвращает пустой объект. В нем нет данных.
Есть ли способ получить результат анонимного типа из динамического запроса SQL?
7 ответов
Решение
Вот окончательное решение, которое отлично сработало для меня.
public static System.Collections.IEnumerable DynamicSqlQuery(this 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, parameters);
}
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_";
// 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);
}
Вы должны использовать сырой SQL для этого, рамки прав SqlQuery<T> будет работать только для объектов с известными типами.
вот метод, который я использую:
public static IEnumerable<dynamic> DynamicListFromSql(this DbContext db, string Sql, Dictionary<string, object> Params)
{
using (var cmd = db.Database.Connection.CreateCommand())
{
cmd.CommandText = Sql;
if (cmd.Connection.State != ConnectionState.Open) { cmd.Connection.Open(); }
foreach (KeyValuePair<string, object> p in Params)
{
DbParameter dbParameter = cmd.CreateParameter();
dbParameter.ParameterName = p.Key;
dbParameter.Value = p.Value;
cmd.Parameters.Add(dbParameter);
}
using (var dataReader = cmd.ExecuteReader())
{
while (dataReader.Read())
{
var row = new ExpandoObject() as IDictionary<string, object>;
for (var fieldCount = 0; fieldCount < dataReader.FieldCount; fieldCount++)
{
row.Add(dataReader.GetName(fieldCount), dataReader[fieldCount]);
}
yield return row;
}
}
}
}
Вы можете назвать это так:
List<dynamic> results = DynamicListFromSql(myDb,"select * from table where a=@a and b=@b", new Dictionary<string, object> { { "a", true }, { "b", false } }).ToList();
Вы можете попробовать код отсюда, прокрутить вниз и найти орудие Станковского: http://www.codeproject.com/Articles/206416/Use-dynamic-type-in-Entity-Framework-SqlQuery
После копирования кода в статический класс вы можете вызвать эту функцию, чтобы получить то, что вы хотите:
var students = Context.Database.DynamicSqlQuery("select FirstName from student").ToList()
Если у вас есть сущность, и вы хотите вернуть только некоторые свойства, вы можете получить еще лучшее решение с помощью рефлексии.
Этот код построен на том же примере, что и в ответе выше.
В дополнение к этому вы можете указать тип и массив полей, которые вы хотите получить обратно.
Результат имеет тип 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_";
// 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);
}
}
Вы можете назвать это так:
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();
На самом деле он работает только с простыми типами и не обрабатывает ошибки.
Я использую это так
ORMClass:
public class ORMBase<T, TContext> : IORM<T>
where T : class
where TContext : DbContext, IDisposable, new()
Метод:
public IList<TResult> GetSqlQuery<TResult>(string sql, params object[] sqlParams)
{
using (TContext con = new TContext())
{
return Enumerable.ToList(con.Database.SqlQuery<TResult>(sql, sqlParams));
}
}
И, наконец, используя
public class ResimORM : ORMBase<Resim, mdlOgrenciKulup>, IDisposable
{
public void Dispose() { GC.SuppressFinalize(this); }
}
ResimORM RE_ORM = new ResimORM();
List<Resim> a = RE_ORM.GetSqlQuery<Resim>(sql,null).ToList();
int b = RE_ORM.GetSqlQuery<int>(sql,null).FirstOrDefault();
Я знаю, что это может быть немного широта, но вы можете использовать такой метод, если хотите выполнить скалярную функцию и всегда приводить ее к однородному типу.
Просто используйте этот параметр forceStringCast для принудительного преобразования результата скалярной функции sql в строку, а затем используйте эту строку по своему усмотрению.
public static Task<T> ScalarFunction<T>(DbContext db, string sql, bool forceStringCast = false, Dictionary<string, object> parameters = null)
{
string cmdText;
if (forceStringCast)
{
cmdText = $@"SELECT CAST({sql}({string.Join(",",
parameters.Keys.Select(p => "@" + p).ToList())} AS VARCHAR(MAX)));";
}
else
{
cmdText =
$@"SELECT {sql}({string.Join(",",
parameters.Keys.Select(p => "@" + p).ToList())});";
}
return db.Database.SqlQuery<T>(cmdText,parameters.Select(p => new SqlParameter(p.Key, p.Value)).ToArray()).FirstOrDefaultAsync();
}
Это мое решение:
DbContext.Database.Connection.Open();
var cmd = DbContext.Database.Connection.CreateCommand();
cmd.CommandText = @"Select * from Table where Param1 = @Param1 and Param2 = @Param2";
cmd.Parameters.Add(new SqlParameter("Param1", Mahdi));
cmd.Parameters.Add(new SqlParameter("Param2", 20));
List<Dictionary<string, object>> items = new List<Dictionary<string, object>>();
var reader = cmd.ExecuteReader();
while (reader.Read())
{
Dictionary<string, object> item = new Dictionary<string, object>();
for (int i = 0; i < reader.FieldCount; i++)
item[reader.GetName(i)] = (reader[i]);
items.Add(item);
}
return Request.CreateResponse(HttpStatusCode.OK, items);
Надеюсь помочь вам всем ;)