How to Retrieve Anonymous SQL Query Results Using Entity Framework?

Retrieving Anonymous SQL Query Results in Entity Framework

Entity Framework provides the SqlQuery method to execute raw SQL and map the results to specific entities. However, to retrieve anonymous results, a different approach is necessary.

One solution is to use the raw SqlConnection and ExecuteReader method to retrieve the data and manually create anonymous objects based on the result set. Here's a custom extension method that can be used:

public static IEnumerable<dynamic> DynamicListFromSql(this DbContext db, string Sql, Dictionary<string, object> Params)
{
    using (var cmd = db.Database.Connection.CreateCommand())
    {
        cmd.CommandText = Sql;
        if (cmd.Connection.State != ConnectionState.Open) { cmd.Connection.Open(); }

        foreach (KeyValuePair<string, object> p in Params)
        {
            DbParameter dbParameter = cmd.CreateParameter();
            dbParameter.ParameterName = p.Key;
            dbParameter.Value = p.Value;
            cmd.Parameters.Add(dbParameter);
        }

        using (var dataReader = cmd.ExecuteReader())
        {
            while (dataReader.Read())
            {
                var row = new ExpandoObject() as IDictionary<string, object>;
                for (var fieldCount = 0; fieldCount < dataReader.FieldCount; fieldCount++)
                {
                    row.Add(dataReader.GetName(fieldCount), dataReader[fieldCount]);
                }
                yield return row;
            }
        }
    }
}

With this method, you can retrieve anonymous results from SQL queries as follows:

List<dynamic> results = DynamicListFromSql(myDb, "select * from table where a=@a and b=@b", new Dictionary<string, object> { { "a", true }, { "b", false } }).ToList();

The above is the detailed content of How to Retrieve Anonymous SQL Query Results Using Entity Framework?. For more information, please follow other related articles on the PHP Chinese website!