使用实体框架返回数据表

using (dbContext.Database.Connection)
{
dbContext.Database.Connection.Open();
DbCommand cmdItems= dbContext.Database.Connection.CreateCommand();
cmdItems.CommandText = "GetAvailableItems";
cmdItems.CommandType = CommandType.StoredProcedure;
cmdItems.Parameters.Add(new SqlParameter("jobCardId", 100525));
//Need to write code below to populate a DataTable.
}

谢谢大家。我解出来了。解决方案如下:

using (var context = new DataBaseContext())
{
    var dt = new DataTable();
    var conn = context.Database.Connection;
    var connectionState = conn.State;
    try
    {
        if (connectionState != ConnectionState.Open) conn.Open();
        using (var cmd = conn.CreateCommand())
        {
            cmd.CommandText = "GetAvailableItems";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add(new SqlParameter("jobCardId", 100525));
            using (var reader = cmd.ExecuteReader())
            {
                dt.Load(reader);
            }
        }
    }
    catch (Exception ex)
    {
        // error handling
        throw;
    }
    finally
    {
        if (connectionState != ConnectionState.Closed) conn.Close();
    }
    return dt;
}

这个例子将返回一个从EntityFramework中选择数据的datatable对象。

我相信这是实现目标的最佳解决方案。然而，这种解决方案的问题是每个记录都是枚举的。您可能希望首先过滤列表，然后从列表中运行这个以避免这种情况。

DataTable dt = new DataTable();
(from rec in database.Table.AsEnumerable()
                     select new
                     {
                         id = rec.id,
                         name = rec.Name
                         //etc
                     }).Aggregate(table, (dt, r) =>
                     {
                         dt.Rows.Add(r.id, r.Name);
                         return dt;
                     });

此解决方案简单，非常快速且易于使用。

创建DbContext扩展:

using System.Data;
using System.Data.Common;
using System.Data.Entity;
..
..
public static class DbContextExtensions
{
    public static DataTable DataTable(this DbContext context, string sqlQuery)
    {
        DbProviderFactory dbFactory = DbProviderFactories.GetFactory(context.Database.Connection);
        using (var cmd = dbFactory.CreateCommand())
        {
            cmd.Connection = context.Database.Connection;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = sqlQuery;
            using (DbDataAdapter adapter = dbFactory.CreateDataAdapter())
            {
                adapter.SelectCommand = cmd;
                DataTable dt = new DataTable();
                adapter.Fill(dt);
                return dt;
            }
        }
    }
}

例子:

using (MyDbContext db = new MyDbContext())
{
    string query = db.Students.Where(o => o.Age > 20).ToString();
    DataTable dataTable = db.DataTable(query);
    ..
    DataTable dt = db.DataTable(
                         (  from o in db.Studets
                            where o.Age > 20
                            select o
                         ).ToString()
                    );
}

只是改进了以前的解决方案，现在包括通用参数(不是SQL Server特定的)和多个结果集支持:

    DataSet GetDataSet(string sql, CommandType commandType, Dictionary<string, Object> parameters)
    {
        // creates resulting dataset
        var result = new DataSet();
        // creates a data access context (DbContext descendant)
        using (var context = new MyDbContext())
        {
            // creates a Command 
            var cmd = context.Database.Connection.CreateCommand();
            cmd.CommandType = commandType;
            cmd.CommandText = sql;
            // adds all parameters
            foreach (var pr in parameters)
            {
                var p = cmd.CreateParameter();
                p.ParameterName = pr.Key;
                p.Value = pr.Value;
                cmd.Parameters.Add(p);
            }
            try
            {
                // executes
                context.Database.Connection.Open();
                var reader = cmd.ExecuteReader();
                // loop through all resultsets (considering that it's possible to have more than one)
                do
                {
                    // loads the DataTable (schema will be fetch automatically)
                    var tb = new DataTable();
                    tb.Load(reader);
                    result.Tables.Add(tb);
                } while (!reader.IsClosed);
            }
            finally
            {
                // closes the connection
                context.Database.Connection.Close();
            }
        }
        // returns the DataSet
        return result;
    }

我在DataContext类中添加了以下方法:

public async Task<DataTable> ExecReturnQuery(string query)
    {
        using (var command = this.Database.GetDbConnection().CreateCommand())
        {
            command.CommandText = query;
            this.Database.OpenConnection();
            using (var result = await command.ExecuteReaderAsync())
            {
                var table = new DataTable();
                table.Load(result);
                // returning DataTable (instead of DbDataReader), cause can't use DbDataReader after CloseConnection().
                this.Database.CloseConnection();
                return table;
            }
        }
    }

然后我从任何类(我注入DataContext类)调用它，像这样:

DataTable myTableRecords = await _dataContext.ExecReturnQuery("SELECT * FROM MyTable");

我只是把答案混在一起。这段代码运行一个动态查询并将结果转换为字典列表。

public List < Dictionary < string,object >> DataTableToDictionaryList(DataTable table) {
    List < Dictionary < string,
    object >> parentRow = new List < Dictionary < string,
    object >> ();
    Dictionary < string,
    object > childRow;
    foreach(DataRow row in table.Rows) {
        childRow = new Dictionary < string,
        object > ();
        foreach(DataColumn col in table.Columns) {
            childRow.Add(col.ColumnName, row[col]);
        }
        parentRow.Add(childRow);
    }
    return (parentRow);
}
List < Dictionary < string,object >> RunDynamicQuery(string sql, Dictionary < string, Object > parameters = null, int resultSet = 0, CommandType commandType = CommandType.Text) {
    // creates resulting dataset
    var resultDataSet = new DataSet();
    // creates a data access context (DbContext descendant)
    using(var context = new DataDbContext()) {
        // creates a Command 
        var conn = context.Database.Connection;
        var cmd = conn.CreateCommand();
        cmd.CommandType = commandType;
        cmd.CommandText = sql;
        if (parameters != null) {
            // adds all parameters
            foreach(var pr in parameters) {
                var p = cmd.CreateParameter();
                p.ParameterName = pr.Key;
                p.Value = pr.Value;
                cmd.Parameters.Add(p);
            }
        }
        try {
            // executes
            if (conn.State != ConnectionState.Open) {
                conn.Open();
            }
            var reader = cmd.ExecuteReader();
            // loop through all resultsets (considering that it's possible to have more than one)
            int currentResultSet = -1;
            do {
                currentResultSet++;
                //skip lower resultsets
                if (resultSet > currentResultSet) {
                    continue;
                }
                // loads the DataTable (schema will be fetch automatically)
                var tb = new DataTable();
                tb.Load(reader);
                resultDataSet.Tables.Add(tb);
                //make sure to get only one result set
                break;
            } while (! reader . IsClosed );
        }
        finally {
            // closes the connection
            context.Database.Connection.Close();
        }
    }
    return DataTableToDictionaryList(resultDataSet.Tables[0]);
}