How can I get a DataSet as a result of a query in Entity Framework 5.0?

Question

I am not sure if this is possible. Maybe I'm trying to use the wrong tool for the work I'm trying to do.

Suppose I want to do the following:

I execute a function called "GetTableName" which returns the name of the SQL table from which I want to get the data.

I perform another function called GetFields, which returns the names of one or more fields in an SQL table.

I would like to be able to write another function that will return a DataSet (or some other common object) containing the rows from the table specified in "GetTableName", with the fields specified by "GetFields"

I know I can do this with the old ADO.net interface, but is there any way to do this using the Entity Framework?

To emphasize, the code will not know at compile time what the form of the data returned from SQL will be.

Answer 1

Since EF requires complex types, it is possible to write a class that inherits from ExpandoObject, which you could then (with a lot of coding) map to the DataTable class or just use it as it is. Then you basically need to say EF to somehow try to match these properties. However, I think it would be a long and winding journey of code, overloading and basic rewriting of the basic functions for displaying EF: s objects.

As you can see in this example, when a writer dynamically creates a model, it still remains difficult; the part where you automatically generate properties based on data: http://www.markzhou.com/blog/post/2011/06/02/Use-dynamic-type-in-Entity-Framework-41-SqlQuery()-method .aspx

I would recommend you go with ADO.NET if you need a DataTable.

Answer 2

As a rule, you should not use a DataSet inside an EF application. But if you really need to (for example, submit a report), this solution should work (this is EF 6 code):

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 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; }