Building a Dynamic LINQ to Entities Compiler (Part 1)

In this article I will explain how to build a dynamic LINQ to Entities compiler for any database provider that supports the Ado.net Entity Framework. Due to the wide range of technologies used this article, it will be broken up into two parts as listed below.

We are working on a dynamic linq query mechanism for the next major release of VistaDB.  Our goal is to provide a LinqPad type of environment in Data Builder for users to write LINQ queries against the database without having to first build an EF model.

Blog Article Sections

• Part I. How to use edmgen command line tool to generate an EF model.
• Part II. How to use CodeDom to dynamically compile a LINQ query. See blog post

Technologies Used

• Ado.net Entity Framework (EF) – EF is an Object Relational Mapping (ORM) technology from Microsoft that is built into the .Net framework 3.5 SP1 and higher.
• VistaDB 4 – Commercial embedded SQL database that supports EF
• edmgen Tool (.Net Framework) – Included in the .Net framework, this tool is used to generate the models used by the EF runtime.
• CodeDom.Compiler (.Net Framework) – CodeDom is also built into the .Net Framework and provides the way to dynamically compile code
• LINQ to Entities (.Net Framework) – This is the query mechanism against the EF runtime, it is how you ask questions of the EF model.

Part I. How to use the edmgen command line tool

There are several steps needed in the process of dynamically testing a LINQ to entities query, first of which being the EDMX model itself. Visual Studio has a great set of wizards built in to handle generating an ADO.NET data model.  These wizards handle creating the necessary files for the EF model, and adding the connection strings to the app.config.

These wizards are not available at runtime, and the model generation becomes slightly more complex. There is no API available to generate an EDMX but Microsoft does include a command line tool called edmgen which can be used to generate an EDMX from any database provider that supports Entity Framework. You can find the edmgen tool under the 3.5 and 4.0 .net framework folders (C:\Windows\Microsoft.NET\Framework\).

Edmgen.exe Parameters

Running edmgen with /help will list the available options or you can view them on MSDN. The tool offers a lot of functionality like full or partial generation and the ability to name the three separate parts of the EF model files.

In this example I am using VistaDB, but with very slight changes this sample can work with other providers. Below is an example of how I used edmgen to dynamically generate an EDMX model using a database connection string entered from the user.

StringBuilder sb = new StringBuilder();
sb.Append(@"/mode:fullgeneration ");
sb.Append(@"/prov:System.Data.VistaDB ");
sb.Append(string.Format(@"/c:""{0}"" ", ConnectionString));
sb.Append(string.Format(@"/project:{0} ", ModelName));
sb.Append(string.Format(@"/entitycontainer:{0}Entities ", ModelName));
sb.Append(string.Format(@"/namespace:{0}Model ", ModelName));
sb.Append(@"/language:CSharp ");

Process myproc = new Process();
myproc.StartInfo.CreateNoWindow = true;
myproc.StartInfo.UseShellExecute = false;
myproc.StartInfo.FileName = "edmgen.exe";
myproc.StartInfo.Arguments = sb.ToString();
myproc.Start();

The above code simply builds up the argument string and calls a process to run the edmgen tool with those arguments, this will produce the files that make up my EDMX and put them in my running directory.

The FullGeneration mode produces all three EF meta data files: msl, csdl, ssdl, and both classes needed to query the entity objects. These three files are combined in Visual Studio’s wizards to make the EDMX file (you can open it and look, it is just an XML document).

The classes contain the public partial  methods for the entities, and the EdmRelationshipAttributes telling the EF runtime how the relationships map to the classes.

The Test.Views.cs contains all of the handling of the database views.  These are not handled as entities by default, but as methods on the entities.

The whole point of this process though is that the user will not need to look at the generated source.  So we will not cover it in this article.  The source is compiled dynamically for the user to allow writing LINQ queries against the database.

Edmgen Needs a VistaDB License

Any assembly that tries to open a VistaDB database or in this case create an EDMX model must be licensed to used VistaDB. This will cause a licensing exception when the edmgen tool attempts to talk to VistaDB, and requires an extra step be taken to insure that the edmgen tool uses the local VistaDB user license. To get edmgen to work you will need to include a new config file for edmgen in the same directory.

Below is what the contents of the new file edmgen.exe.config file needs to look like.  The file is placed in the same directory with the edmgen you want to use with VistaDB.

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <appSettings>
    <add key="VistaDBUseDesignTimeLicense" value="true"/>
  </appSettings>
  <system.data>
    <DbProviderFactories>
      <remove invariant="System.Data.VistaDB" />
      <add name="VistaDB 4" description="VistaDB 4 ADO.NET Provider for .Net 2.0-3.5"
 invariant="System.Data.VistaDB"
type="VistaDB.Provider.VistaDBProviderFactory, VistaDB.4, 
Version=4.0.0.0, Culture=neutral, PublicKeyToken=dfc935afe2125461" />
    </DbProviderFactories>
  </system.data>
</configuration>

This allows the edmgen tool to use the app.config file, and if there is a VistaDB license present on the local user account, it will use it to generate the model.

This step is only needed with VistaDB due to how the VistaDB 4.0 product licensing works.

Summary

This concludes Part I of the process on how to use Microsoft’s edmgen tool to dynamicly build an EDMX model. Part II will include information on how to use CodeDom to compile a LINQ query against an EDMX model and how it all works together.