Database context and Return Dynamic Result Set in ASP.NET MVC c# entity-framework


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



Appreciate any suggestion.

3/31/2013 5:32:24 PM

Accepted Answer

Finally i made is using TypeBuilder option suggested by "Mortalus" and ExpandoObject object. It has little performance overhead right now.

Take Typebuilder code from "Mortalus" answer then i made code according to my requirement as below.

List<Dictionary<string, object>> expandolist = new List<Dictionary<string, object>>();

foreach (var item in returndata)
  IDictionary<string, object> expando = new ExpandoObject();
  foreach (PropertyDescriptor propertyDescriptor in TypeDescriptor.GetProperties(item))
      var obj = propertyDescriptor.GetValue(item);
      expando.Add(propertyDescriptor.Name, obj);
     expandolist.Add(new Dictionary<string, object>(expando));

  return expandolist;

so now, I have "Dictionary" object from dynamic object. and using it you can work easily at design time rather then wait until runtime using "dynamic" object.

4/5/2013 2:29:39 PM

Popular Answer

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++)
        yield return values.ToArray();

and then:

public ActionResult Index()
    using (var ctx = new UsersContext())
    using (var cmd = ctx.Database.Connection.CreateCommand())
        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[]>
        @foreach (var row in Model)
                @foreach (var column in row)

Related Questions


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow