根据表模式生成一个类 [英] Generate a class based on table schema

问题描述

我想根据用户提交的查询结果动态生成一个类。例如，如果用户输入选择名称，tbl的年龄，则结果为名称列，即 string 和 age 是一个整数。结果类应为：

I want to dynamically generate a class based on the results from a query that user submits. For instance, if the user enters Select name, age from tbl, the result is a name column which is string and age which is an int. The resulting class should be:

public class Test
{
   public string Name { get; set; }
   public int Age { get; set; }
}

是否有一种有效的方法可以通过 EntityFramework 或C＃中的功能，或者我不得不使用反射来创建新类型并实例化它。

Is there an efficient way to do this via EntityFramework or features in C# or I have to use maybe reflection to create a new type and instantiate it.

PS：我的目的是运行该查询在数据库上进行，并在网格中向用户显示结果并运行一些filter / sort / etc。

PS: My purpose is to run this query on the database and show the results in a Grid to the user and run some filter/sort/etc. on it.

推荐答案

您可以使用TypeBuilder创建新类型，并使用EF的 SqlQuery（）如此处。

You could use TypeBuilder to create a new type and execute the query against database using EF's SqlQuery() as mentioned here.

OR

更清洁的方法将使用 dynamic 对象绑定网格。扩展EF以返回 所建议的 dynamic 对象的集合。 ChristineBoersen 此处。该代码是在EF转到RTM之前编写的。这是一个可行的版本：

A cleaner method would be to use dynamic objects to bind the grid. Extend EF to return a collection of dynamic objects as suggested by ChristineBoersen here. The code was written before EF went to RTM. Here's a version that works:

public static class EFExtensions
{
    public static IEnumerable<dynamic> CollectionFromSql(this DbContext dbContext, string Sql, Dictionary<string, object> Parameters)
    {
        using (var cmd = dbContext.Database.Connection.CreateCommand())
        {
            cmd.CommandText = Sql;
            if (cmd.Connection.State != ConnectionState.Open)
                cmd.Connection.Open();

            foreach (KeyValuePair<string, object> param in Parameters)
            {
                DbParameter dbParameter = cmd.CreateParameter();
                dbParameter.ParameterName = param.Key;
                dbParameter.Value = param.Value;
                cmd.Parameters.Add(dbParameter);
            }

            //var retObject = new List<dynamic>();
            using (var dataReader = cmd.ExecuteReader())
            {
                while (dataReader.Read())
                {
                    var dataRow = GetDataRow(dataReader);
                    yield return dataRow;
                }
            }
        }
    }

    private static dynamic GetDataRow(DbDataReader dataReader)
    {
        var dataRow = new ExpandoObject() as IDictionary<string, object>;
        for (var fieldCount = 0; fieldCount < dataReader.FieldCount; fieldCount++)
            dataRow.Add(dataReader.GetName(fieldCount), dataReader[fieldCount]);
        return dataRow;
    }
}

您可以按以下方式调用上述方法：

You could invoke the above method as follows:

var results = context.CollectionFromSql("Select Name, Age from tbl", new Dictionary<string, object>()).ToList();
// Bind results to grid

这篇关于根据表模式生成一个类的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！