I ran across a unique scenario recently, and I’m not sure if I should be proud of the solution, or appalled at the Frankenstein result. Either way, I thought I’d post about it to possibly help others in the same situation.
I was developing a website leveraging jQuery / AJAX to query views of data at runtime. The kicker? I didn’t know which views. The choice of database view was determined via configuration and user choice.
I was leveraging jqGrid for the client-side grid presentation, sorting, filtering, etc. I was also proving out an approach using the MVC library for jqGrid to bind and query for results (this isn’t necessary for this discussion, but was the original reason to create this solution, as we’ll soon see).
The jqGrid MVC library uses IQueryable data sources to bind to the grid. IQueryable in and of itself isn’t that hard to produce, but what about when you don’t know what the type will look like (read => the view you’re binding to is unknown at runtime)? It sure would be nice to leverage an existing technology like Entity Framework, but EF assumes you know the tables / views in advance. It’s not a bad assumption; but I definitely have an edge case here.
So, the summary is: how do you return an IQueryable<T> when T is unknown at compile-time?
There are some constraints here, all of which makes this solution more palatable/scary:
- I don’t need to worry about database updates. Views are hard to update, anyway, usually requiring INSTEAD OF triggers.
- I could eliminate EF and just run a straight database query, mapping the datareader into a List<T>, where T is dynamically typed at runtime. I could then return List<T>.AsQueryable(), but I lose something significant: deferred execution. I’m returning the entire resultset and then using Linq in memory to sort and filter the result. What if I have a million rows? Awful.
So, I set about to force EF into helping me solve the problem. Nothing like forcing a square peg into a round hole.
First off, how do I create an EF context that will return dynamic results? This part is fairly simple.
/// <summary>
/// Context class for dynamic queries and catalog information
/// </summary>
public
class QueryCatalogContext : DbContext {
/// <summary>
/// Initializes a new instance of the <see cref="QueryCatalogContext" />
/// class.
/// </summary>
/// <param name="model">The model.</param>
public
QueryCatalogContext(DbCompiledModel model) : base("DomainDB", model) {
modelProvided = true;
}EF allows us to provide a model at runtime, so assuming I can define a model based on my run-time view, I’m OK there. Now how do I return an IQueryable<T> from this context?
/// <summary>
/// Gets a set of generalized query results.
/// </summary>
/// <param name="query">The query.</param>
/// <param name="columns">The columns.</param>
/// <returns></returns>
public
IQueryable<T> GetDynamicQueryResults<T>(QueryMetadata query) {
List<ColumnMetadata> columns = this.GetColumnMetadata(query);
string className = QueryTypeFactory.GetType(query, columns).Name;
// Build the query and execute
string sql = string.Format(
"SELECT VALUE {0} FROM CodeFirstContainer.{0} AS {0}", className);
return new ObjectQuery<T>(sql, ((IObjectContextAdapter)this).ObjectContext,
MergeOption.NoTracking);
}GetColumnMetadata is an internal function that returns a list of column (class property) metadata based on the particular query metadata (it does this by querying the database for view column metadata). QueryTypeFactory uses the Dynamic Linq Library to stamp out (and cache) types based on a set of properties (the columns I just retrieved). I then turn that type info into an EF SQL statement and return an ObjectQuery<T> with no tracking.
OK, that’s fine and dandy, but how do I provide the model and, more importantly, how do I call the generic method if I don’t know what T is in advance?
The service class that actually executes the query using the context passes in the model and calls the generic method. That mechanism follows:
/// <summary>
/// Gets the dynamic query results.
/// </summary>
/// <param name="query">The query.</param>
/// <param name="columns">The columns.</param>
/// <returns></returns>
public
IQueryable<T> GetDynamicQueryResults<T>(QueryMetadata query,
List<ColumnMetadata> columns) where T
: class {
// Define a dynamic type and create a configuration for it
DbModelBuilder builder = new DbModelBuilder(DbModelBuilderVersion.V4_1);
EntityTypeConfiguration<T> configuration =
(EntityTypeConfiguration<T>)Activator.CreateInstance(
typeof(DynamicViewConfiguration<>).MakeGenericType(typeof(T)), query,
columns);
// Add configuration for dynamic type
builder.Configurations.Add(configuration);
using(SqlConnection connection = new SqlConnection(
ConfigurationManager.ConnectionStrings["MyDB"]
.ConnectionString)) // Only need the connection for building
// the model;
{
// Can't use implicit IDisposable because the context is needed when the
// IQueryable is evaluated.
QueryCatalogContext context =
new QueryCatalogContext(builder.Build(connection).Compile());
return context.GetDynamicQueryResults<T>(query);
}
}First, the service class creates a DbModelBuilder. The magic happens in the next line. Because I don’t know the type T at runtime, I need to use reflection to create an instance of the DynamicViewConfiguration<T> class. I then pass this to the model builder (this class is an EntityTypeConfiguration<T>). I use a new connection string for building the model (EF has to query the view for metadata) and then compile the model, passing it into the context constructor. I then return the GetDynamicQueryResults<T> method, returning a true IQueryable<T> object.
The DynamicViewConfiguration<T> class is used to define the model for EF. At a minimum, models must define what table they map to and what constitutes the primary key. This is what the DynamicViewConfiguration<T> class does.
/// <summary>
/// A dynamic entity configuration for views specified at run-time.
/// </summary>
public
class DynamicViewConfiguration<TDynamic>
: EntityTypeConfiguration<TDynamic> where TDynamic : class {
/// <summary>
/// Initializes a new instance of the <see cref="DynamicViewConfiguration" />
/// class.
/// </summary>
/// <param name="query">The query.</param>
/// <param name="columns">The columns.</param>
public
DynamicViewConfiguration(QueryMetadata query, List<ColumnMetadata> columns)
: base() {
// Locate key column (TODO: Handle multiple key fields)
ColumnMetadata key = columns.FirstOrDefault(c = > c.IsKey) ? ? columns[0];
Activator.CreateInstance(
typeof(DynamicViewLambdaGenerator<, >)
.MakeGenericType(typeof(TDynamic), key.DataType.ClrType),
this, key.Name);
ToTable(query.Name);
}
}DynamicViewLambdaGenerator<T> is an internal class that defines the lambda for the PK (the key field).
/// <summary>
/// Internal class to generate lambda expressions for the configuration
/// </summary>
internal class DynamicViewLambdaGenerator<TDynamic, TKey> where TDynamic
: class {
// TODO: Change to a stateful approach and cache reflection and expressions if
// usage of this class increases
private
readonly Type type;
private
readonly string keyPropertyName;
/// <summary>
/// Initializes a new instance of the <see cref="PropertyLambdaGenerator" />
/// class.
/// </summary>
/// <param name="propertyName">Name of the property.</param>
public
DynamicViewLambdaGenerator(DynamicViewConfiguration<TDynamic> configuration,
string keyPropertyName) {
this.keyPropertyName = keyPropertyName;
this.type = typeof(TDynamic);
configuration.HasKey(this.Generate());
}
/// <summary>
/// Generates this instance.
/// </summary>
/// <returns></returns>
private
Expression<Func<TDynamic, TKey>> Generate() {
PropertyInfo property = type.GetProperty(keyPropertyName);
ParameterExpression thing = Expression.Parameter(type);
MemberExpression keyExpression = Expression.Property(thing, property);
return Expression.Lambda<Func<TDynamic, TKey>>(keyExpression, thing);
}
}OK, bear with me. This is where all the real funny stuff goes on. In the DynamicViewConfiguration<T> constructor, we find the primary key column. We use that to (again, through reflection, because we don’t know any generic types at compile time) create the lambda function for the EF entity configuration HasKey method. We use the key property name, reflection, and more of the Dynamic Linq library to return the property of the type within the Generate() method.
After that, we call ToTable passing in the name of the view. Voila. We’ve defined the underlying view name and the key field.
With the context (and model) defined for our runtime type, the service class can then call GetDynamicQueryResults<T>, and as mentioned earlier, I get a true IQueryable<T> back. I can use Linq to filter or sort the results and the view is not queried until the set is iterated.
The last missing piece is how to call the service class. This is done in an MVC controller.
// Generate a type based on the return columns
Type type = QueryTypeFactory.GetType(query, columns);
MethodInfo generic = typeof(IQueryCatalogDynamic).GetMethod("GetDynamicQueryResults").MakeGenericMethod(type);
var data = generic.Invoke(repository, new object[] { query, columns });The controller gets the runtime type through our type factory class, then calls the GetDynamicQueryResults on the service instance, and we invoke the method, passing in our parameters. The controller uses this functionality to return an IQueryable<T> to the grid databinding function, which actually performs the sort and filter. With literally millions of rows in some tables, the jqGrid client-side paging and sorting was nearly instant.
Now I know that this is sort of a bastardization of technology, but the use case is narrow, and it worked great for my scenario. The purpose isn’t really how to uglify EF, but more so how some solutions can be solved with a little ingenuity (and a lot of head-banging against keyboards).
