CODEWITHSUNDEEP
c#entity-framework
PostureOfLearning
PostureOfLearning

Reputation: 3541

EF and stored procedure returning dynamic result

I have a stored procedure with an input that defines the columns that are returned.

How can I iterate through the result?

I have tried solutions similar to this:

var selectColsParam = new System.Data.SqlClient.SqlParameter()
    {
        ParameterName = "@SelectCols",
        Value = "Person.FirstName",
    };
string sql = string.Format("dbo.DynamicResultSP {0} ", selectColsParam.ParameterName);

var result = db.Database.SqlQuery<List<dynamic>>(sql, selectColsParam);

At best, 'result' contains the correct number of rows which I can iterate through, but the 'row' itself is simply an object I can't seem to do anything with.

I don't need to know the column names but need to be able to iterate through the fields.

I know having a stored procedure that returns different columns depending on input is not considered good design, however, this is what I have to work with and so changing the SP is not an option.

Any help is appreciated

Upvotes: 2

Views: 6053

Answers (2)

David K
David K

Reputation: 233

I ran into the same problem today, and had to resort back to SqlCommand rather than use the object context directly.

    // Retrieve the connection from the object context
    var entityConnection = this.ObjectContext.GetConnection() as EntityConnection;
    var dbConnection = entityConnection.StoreConnection as SqlConnection;

    // Create the command and associated parameters (dynamically passed in perhaps)
    var command = new SqlCommand("dbo.DynamicResultSP");
    command.Parameters.AddWithValue("@SelectCols", "Person.FirstName");
    ////command.Parameters.AddWithValue("@AnotherParameter", "Parameter.SecondValue");
    ////command.Parameters.AddWithValue("@AThirdParameter", "YetAnotherValue");

    dbConnection.Open();
    using (var reader = command.ExecuteReader())
    {
        // Get the column names
        columnNames = new string[reader.FieldCount];
        for (int i = 0; i < reader.FieldCount; i++)
        {
            columnNames[i] = reader.GetName(i);
        }

        // Get the actual results
        while (reader.Read())
        {
            var result = new string[reader.FieldCount];
            for (int i = 0; i < reader.FieldCount; i++)
            {
                result[i] = reader[i].ToString();
            }

            results.Add(result);
        }
    }
    dbConnection.Close();

Now you should have access to both the names of the fields and the results.

Whilst it's not the prettiest solution, it gets the job done. Suggestions welcome.

Upvotes: 2

tvanfosson
tvanfosson

Reputation: 532435

I ran into a similar issue when testing JsonResults. You might be interested in a little extension I wrote that allows you to convert an object to a dynamic which would allow you to hook into the runtime dynamic binding on each object.

It might look like:

var result = db.Database.SqlQuery<List<object>>(sql, selectColsParam);

var dynamicResults = result.Select(o => o.AsDynamic()).ToList();

foreach (dynamic item in dynamicResults)
{
   // treat as a particular type based on the position in the list
}

It's also possible that you might simply need to convert each element to the proper type based on whatever logic you have to determine that using Convert or by directly casting.

Upvotes: 0

Related Questions