c# – sql查询执行实体框架的匿名类型结果

我使用实体框架5.0与.net框架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();

它不工作它给出例外

The data reader is incompatible with the specified ‘MyApp.DataContext.Student’. A member of the type,‘StudentId’,does not have a corresponding column in the data reader with the same name.

所以我尝试了动态类型

var students = Context.Database.sqlQuery<dynamic>("select FirstName from student").ToList();

它也不工作,它返回一个空对象.没有可用的数据.

有没有办法从动态SQL查询获取匿名类型的结果？

解决方法

以上解决方案对我来说很好,但是当我尝试获取Nullable类型列时,它有一些问题.

这是我的最终解决方案.

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,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,propertyMethodAttributes,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,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);
        }

c# – sql查询执行实体框架的匿名类型结果

解决方法

猜你在找的C#相关文章