Database context and Return Dynamic Result Set in ASP.NET MVC

Question

In MVC 4 and EF 5 i want to run dynamic query.

var returndata = Context.Database.SqlQuery(Type, strsql, null);

i don't know, how many fields it will return and name. Out of this result i want to make table structure that will display on view.

Question : What should i passed as Type?

my query return below result:

Field 1, Field 2, Field 3, Field 4, Field 5

Row1...

Row2..

Appreciate any suggestion.

Answer 1

You could use a raw SQL query because EF doesn't support that:

private static IEnumerable<object[]> Read(DbDataReader reader)
{
    while (reader.Read())
    {
        var values = new List<object>();
        for (int i = 0; i < reader.FieldCount; i++)
        {
            values.Add(reader.GetValue(i));
        }
        yield return values.ToArray();
    }
}

and then:

public ActionResult Index()
{
    using (var ctx = new UsersContext())
    using (var cmd = ctx.Database.Connection.CreateCommand())
    {
        ctx.Database.Connection.Open();
        cmd.CommandText = "SELECT * FROM UserProfile";
        using (var reader = cmd.ExecuteReader())
        {
            var model = Read(reader).ToList();
            return View(model);
        }
    }
}

and finally in your view:

@model IEnumerable<object[]>
<table>
    <tbody>
        @foreach (var row in Model)
        {
            <tr>
                @foreach (var column in row)
                {
                    <td>@column</td>
                }
            </tr>
        }
    </tbody>
</table>

Answer 2

This method loads data from SQL select (with parameters) to the list of rows, where each row is the dictionary of columns (the key is the column name).

private static List<Dictionary<string, object>> LoadData(string sqlSelect, params object[] sqlParameters)
{
    var table = new List<Dictionary<string, object>>();
    using (var ctx = new DbEntities())
    {
        ctx.Database.Connection.Open();
        using (var cmd = ctx.Database.Connection.CreateCommand())
        {
            cmd.CommandText = sqlSelect;
            foreach (var param in sqlParameters)
                cmd.Parameters.Add(param);
            using (var reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    var row = new Dictionary<string, object>();
                    for (int i = 0; i < reader.FieldCount; i++)
                        row[reader.GetName(i)] = reader[i];
                    table.Add(row);
                }
            }
        }
    }
    return table;
}