Database context and Return Dynamic Result Set in

问题:

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.

回答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>

回答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;
}

回答3:

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:

Without knowing anything about the type that is returned, I think you might be out of luck.

If you know what patterns it might fall under, you could use some try { } catch () { }'s on interfaces that match those parameters on your otherwise dynamic query, but that seems like it might be a bit painful.

回答5:

Unfortunately, EF won't materialize objects unless it knows their Type.

If this is really necessary for you, I think your best bet would be to fall back to ADO.NET and DataTable.

回答6:

Similarly post by Darin Dimitrov, but it returns DataTable

public DataTable QueryToTable(Entities db, string queryText, SqlParameter[] parametes)
        {
            using ( DbDataAdapter adapter = new SqlDataAdapter())
            {
                adapter.SelectCommand = db.Database.Connection.CreateCommand();
                adapter.SelectCommand.CommandText = queryText;
                if (parametes != null)
                    adapter.SelectCommand.Parameters.AddRange(parametes);
                DataTable table = new DataTable();
                adapter.Fill(table);
                return table;
            }
        }

Use

SqlParameter[] parametes = new[]
                {
                    new SqlParameter("date_from", dateFrom)
                };

DataTable tab = QueryToTable(new Entities(), 
               "Select *  From SomeTable Where ADate >= @date_from", parametes);

Example for MS SQL Server

回答7:

I have recently stumbled upon this example:

http://www.markzhou.com/blog/post/2011/06/02/Use-dynamic-type-in-Entity-Framework-41-SqlQuery()-method.aspx

I haven't had the time to test it myself but it seems that it is possible with some additional work to construct the dynamic type.

In short you would want to do something like this:

  TypeBuilder builder = Program.CreateTypeBuilder(
                "MyDynamicAssembly", "MyModule", "MyType");
  Program.CreateAutoImplementedProperty(builder, "name", typeof(string));
  Program.CreateAutoImplementedProperty(builder, "type", typeof(string));
  Program.CreateAutoImplementedProperty(builder, "id", typeof(int));

  Type resultType = builder.CreateType();
  dynamic queryResult = context.Database.SqlQuery(
                    resultType, "SELECT * FROM sys.sysobjects");

Where TypeBuilder is described in details in the post I have attached.

回答8:

Adding to Petr Voborník's answer, dynamic query, I add dynamic insert of ResultSet, my application takes the dynamic query of all tables of the entire database, a chunk at a time and then inserts the dynamic results into a remote database, using Always Encrypted (omitted here). Passing a sb command and parameter object.

    public void StoreData(DbContext dbContext, Dictionary<string, string> columnInfo, List<Dictionary<string, object>> multiInsertObj, string tableName)
    {
        _ctx = dbContext;
        _columnInfo = columnInfo;
        var sb = new StringBuilder();
        sb.Append(BuildSqlCommand(tableName, columnInfo, multiInsertObj.Count));
        ExecuteSqlCommand(sb, GetParamsObject(columnInfo, multiInsertObj));
    }

    private static StringBuilder BuildSqlCommand(string tableName, Dictionary<string, string> variableInfo, int variableCount)
    {
        //Build sql command
        var sb = new StringBuilder();
        sb.Append("INSERT INTO dbo." + tableName + "(");
        foreach (var variable in variableInfo)
        {
            sb.Append(variable.Key);
            sb.Append(", ");
        }
        sb.Append("SystemNumber, ");
        sb.Remove(sb.Length - 2, 2).Append(") VALUES ");
        for (var i = 0; i < variableCount; i++)
        {
            sb.Append("(");
            foreach (var name in variableInfo.Keys)
            {
                sb.Append("@" + name + "_" + i + ",");
            }
            sb.Append("@SystemNumber" + "_" + i + ",");
            sb.Remove(sb.Length - 1, 1).Append("),");
        }
        sb.Remove(sb.Length - 1, 1);
        return sb;
    }

    private static object[] GetParamsObject(Dictionary<string, string> columnInfo, List<Dictionary<string, object>> multiInsertObj)
    {
        var variableCount = multiInsertObj.Count;
        var rowCount = multiInsertObj[0].Keys.Count;
        var objectLength = (rowCount + 1) * variableCount;
        var variableDataTypes = columnInfo.Values.ToList();
        var paramObj = new object[objectLength];
        var j = 0;
        var i = 0;
        foreach (var row in multiInsertObj)
        {
            var k = 0;
            foreach (var data in row)
            {
                var sb = new StringBuilder();
                sb.Append("@");
                sb.Append(data.Key);
                sb.Append("_" + i);
                paramObj[j] = new SqlParameter(sb.ToString(), SetSqlDataType(variableDataTypes[k])) { Direction = Input, Value = data.Value };
                j++;
                k++;
            }
            paramObj[j] = new SqlParameter(("@SystemNumber" + "_" + i), SetSqlDataType("int")) { Direction = Input, Value = _systemNumber };
            i++;
            j++;
        }
        return paramObj;
    }

    private static void ExecuteSqlCommand(StringBuilder sb, params object[] sqlParameters)
    {
        using (_ctx)
        {
            _ctx.Database.Connection.Open();
            using (var cmd = _ctx.Database.Connection.CreateCommand())
            {
                cmd.CommandText = sb.ToString();
                foreach (var param in sqlParameters)
                    cmd.Parameters.Add(param);
                try
                {
                    cmd.ExecuteNonQuery();
                }
                catch (Exception e)
                {
                    Console.WriteLine(e);
                    throw;
                }
            }
        }
    }