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

Anonymous Type Results from Entity Framework SQL Queries

When working with Entity Framework (EF), it may be necessary to execute raw SQL queries and retrieve anonymous type results. While EF's SqlQuery method allows for querying entities of known types, obtaining anonymous results requires a different approach.

Problem Description

Suppose you have the following SQL query that retrieves only the FirstName column from the Student table:

select FirstName from student

Using SqlQuery, the following code will not work as it expects a known type Student:

var students = Context.Database.SqlQuery<Student>($"select FirstName from student").ToList();

Anonymous Type Solution

To get anonymous type results from raw SQL queries, you can use the following method:

public static IEnumerable<dynamic> DynamicListFromSql(this DbContext db, string Sql, Dictionary<string, object> Params)
{
    using (var cmd = db.Database.Connection.CreateCommand())
    {
        ...
        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 creates an ExpandoObject and populates it with the data fetched from the data reader, effectively creating an anonymous type with dynamic properties.

Usage

To use this method, call it 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();

This will return a list of anonymous objects with dynamic properties corresponding to the columns in the result set.

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