How can I get a DataSet as the result of a query i

Question

I'm not sure if this is even possible. I might be trying to use the wrong tool for the job I'm trying to do.

Suppose that I want to do the following:

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

I execute another function called "GetFields" that returns a the names of one or more fields in the SQL table.

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

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

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

Answer 1

By the rule, you shouldn't use a DataSet inside a EF application. But, if you really need to (for instance, to feed a report), that solution should work (it's 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'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;
    }

Answer 2

Since EF requires complex types to map onto, it's probably possible to write a class inheriting from ExpandoObject which you would then be able (with alot of coding) to map onto a DataTable class or just use as is. Then you'd basically need to tell EF to somehow try map to those properties. However, I think that would be a long and winding journey of code, overloads and basicly rewriting the core functionality of EF:s object mapping.

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

I'd really recommend you go with ADO.NET if you need DataTable.