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 DataTable
s 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.
string Sql = "SELECT Field1, Field2 FROM MyTable WHERE Field1 = @Field1";
List<dynamic> MyList = MyDbContext.CollectionFromSql(Sql,
new Dictionary<string, object>() { { "@Field1", 1} }).ToList();
MyList[0].Field2
MyList[0].Field3
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