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

发布时间：2020-12-15 03:45:14 所属栏目：百科来源：网络整理

导读：我使用实体框架5.0与.net框架4.0代码第一种方法.现在我知道我可以通过以下方式在实体框架中运行raw sql var students = Context.Database.SqlQueryStudent("select * from student").ToList(); 它的工作完美,但我想要的是返回匿名结果.例如,我只想要学生表中

我使用实体框架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);
        }

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!