c# - 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>