Entity Framework 7 - How to Retrieve Dynamic Data

Problem

Given that I am using Entity Framework 7 and that I want to return data from a query, but not be bound to a DbSet object to run a report. Since CoreCLR doesn't have DataTables which were also a popular way to achieve this functionality previously, another method needed to be developed.

Background

Entity Framework 6 had some helper methods to work with this. Unfortunately, Entity Framework 7 hasn't provided this functionality yet. The closest thing in Entity Framework 7 is FromSql(). Unfortunately, FromSql must be called to a DbSet object. There are times, such as when generating a report, where you just want to return some data, but not have it bound to a DbSet object.

Assumptions are that you are using a DNX of 1.0.0-beta8 or later. This code most likely will work with previous runtime versions, but has not been tested on them.

Using the Code

One way to overcome this, is to create an extension method on DbContext. Below is the extension method I used. It returns a IEnumerable<Dynamic> collection that may be used for things such as reporting. It uses the power of dynamic (DLR) and the ExpandoObject to make all the magic happen.

using Microsoft.Data.Entity;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Dynamic;

namespace Extensions
{
    public static class DbContextExtensions
    {

        public static IEnumerable<dynamic> CollectionFromSql(this DbContext dbContext, 
                                                             string sql, 
                                                             Dictionary<string, object> Parameters)
        {
            using (var cmd = dbContext.Database.GetDbConnection().CreateCommand())
            {
                cmd.CommandText = sql;
                if (cmd.Connection.State != ConnectionState.Open)
                    cmd.Connection.Open();
         
                foreach (KeyValuePair<string, object> param in Parameters)
                {
                    DbParameter dbParameter = cmd.CreateParameter();
                    dbParameter.ParameterName = param.Key;
                    dbParameter.Value = param.Value;
                    cmd.Parameters.Add(dbParameter);
                }
         
                var retObject = new List<dynamic>();
                using (var dataReader = cmd.ExecuteReader())
                {
                    while (dataReader.Read())
                    {
                        var dataRow = new ExpandoObject() as IDictionary<string, object>;
                        for (var fieldCount = 0; fieldCount < dataReader.FieldCount; fieldCount++)
                            dataRow.Add(dataReader.GetName(fieldCount), dataReader[fieldCount]);
         
                        retObject.Add((ExpandoObject) dataRow);
                    }
                }
         
                return retObject;
            }
        }
    }
}

Now to call this method is fairly simple. The magic is all handled in the DLR (Dynamic Runtime Library).

Note you are responsible for ensuring the field exists, or you will get an error at Runtime.

// Assuming your DbContext is named MyDbContext and is already defined and instantiated above

string Sql = "SELECT Field1, Field2 FROM MyTable WHERE Field1 = @Field1";
List<dynamic> MyList = MyDbContext.CollectionFromSql(Sql,
                             new Dictionary<string, object>() { { "@Field1", 1} }).ToList();

// Accessing a particaular "Row" of information and getting the Field2 property
MyList[0].Field2

// This will throw an error as the property doesn't exist
MyList[0].Field3 // Field3 doesn't exist

Summary

Hopefully this helps with one of the stumbling blocks that people have encountered when trying to migrate to Entity Framework 7.

History

• Version 2015-11-09-b - Refactored to take a Dictionary<string, object> instead of DbParameter[] so that you don't need to know the underlying database to pass parameters to the function
• Version 2015-11-09-a - Updated with generic (not typed to SQL Server) version based on user feedback. Note I left the original name "CollectionFromSql" as I don't want to run into issues once the entity framework team implements FromSql to a collection natively.
• Version 2015-11-06-a - Initial version