Home > Database > Mysql Tutorial > How to Retrieve Anonymous Type Results from Raw SQL Queries in Entity Framework?

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

Linda Hamilton
Release: 2025-01-03 00:52:39
Original
550 people have browsed it

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
Copy after login

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();
Copy after login

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;
            }
        }
    }
}
Copy after login

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();
Copy after login

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!

source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Articles by Author
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template