How to Retrieve Anonymous Type Results from Entity Framework SQL Queries?

Anonymous Type Results from Entity Framework SQL Queries

Entity Framework allows for executing raw SQL queries, but a common challenge arises when attempting to retrieve anonymous results. The native SqlQuery method expects specific types, making it incompatible with dynamic or anonymous results.

To overcome this, a custom approach using the underlying connection objects is necessary. The following C# code provides a solution:

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

This method takes a SQL query string, as well as a dictionary of parameters. It creates and executes the command using the underlying connection, reads the results, and dynamically populates an ExpandoObject with the column names and values.

To use this method, simply call it like so:

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

This will return a list of dynamic anonymous objects that can be easily iterated over and accessed by property name.

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