Building a Dynamic LINQ to Entities Compiler (Part 2)

In this article I will continue to explain how to build a dynamic LINQ to Entities compiler for any database provider that supports the Ado.net Entity Framework.  See part 1 of building a dynamic linq to entities compiler for background information. This part of the series will cover using the .Net CodeDom Compiler to dynamically execute LINQ queries against an EF model.

We are working on a dynamic LINQ query mechanism for the next major release of VistaDB.  Our goal is to provide a dynamic LINQ execution panel (like LinqPad does for Linq to Sql) in Data Builder.  Users will be able to write LINQ to Entities queries against the database without having to first build an EF model.  We include a default data context object that can be used to write the queries the same way they will appear in your code.

Allow users to write a LINQ query

The first step needed in the process of compiling the query is to allow the user to supply me with the database and query they wish to execute. In this example the user must return their result set to a var named query and use the name “context” for the EF model context.

Compile the LINQ query

There are many ways to perform this step but i have chosen to use the System.CodeDom.Compiler built into .Net. I will need to create a class around the users query and compile it with both classes created from the edmgen in part I. 

Create new compiler and parameters objects.

ICodeCompiler compiler = new CSharpCodeProvider().CreateCompiler();
CompilerParameters parameters = new CompilerParameters();

Add needed reference assemblies for the compile class to work. (this is only a snippet)

parameters.ReferencedAssemblies.Add("System.dll");
parameters.ReferencedAssemblies.Add("System.Windows.Forms.dll");

Design my dynamic class that contains the instantiation of the dynamic EF model and LINQ query to return a result set.

string source = @"
using System;
using System.ComponentModel;
using System.Linq;
using System.Windows.Forms;
using System.Data;
using System.Diagnostics;
using VistaDB.Provider;
using VistaDB;

[assembly: VistaDB.UseVistaDBDesignTimeLicense]

namespace VistaDBScratchPad
{
public class SourceClass
{
public object DynamicCode(string connection)
{
" + ModelInstanciation + "" + Query +
" return query as Object;}}}";

Compile the above created dynamic class along with both EF model classes and return the results.

CompilerResults results = compiler.CompileAssemblyFromFileBatch(parameters,
new string[]{string.Format("{0}\\Dynamic.cs", Directory.GetCurrentDirectory()),
string.Format("{0}\\{1}.ObjectLayer.cs", Directory.GetCurrentDirectory(), ModelName),
string.Format("{0}\\{1}.Views.cs", Directory.GetCurrentDirectory(), ModelName)});

If the compilation passes, we can create an instance of the assembly and invoke the dynamic class by passing in the EF connection string and use the returned object to bind to a WinForms grid view.

object queryResult = sourceClass.GetType().InvokeMember("DynamicCode",
BindingFlags.InvokeMethod, null, sourceClass, new object[] { EDMXConnection });
dataGridView_Results.DataSource = queryResult;

Testing with VistaDB Northwind Sample

This article series has explained how to use Microsoft’s edmgen tool to dynamically create an EDMX model which can then be compiled using CodeDom along with a new dynamic class containing a LINQ query to test LINQ syntax against a VistaDB database on the fly. These are the results returned from the VistaDB Scratchpad sample using the VistaDB Northwind sample database.

Summary

We have this working in an internal prototype to build up the EF model dynamically and allow for execution.  Of course making it work in the general purpose case is always a lot harder than in a controlled test.  But we do expect this type of ability to be in the next major release of VistaDB.

It is hard to write LINQ queries in a complex product when you have to go through a compile, load the application, step through to the correct point, get an exception, and then modify it and start all over.  This ability to write your LINQ in a dynamic execution context increases developer productivity with LINQ and Entity Framework dramatically.