Entity Framework in an Dynamics Nav (Navision) environment (or using EF to access tables with unknown table names)

Summary

In this post I show how to use dynamic compilation to allow Entity Framework to reference tables whose names are not known at compile time.

Details

Entity Framework works great for the majority of scenarios; it handles tables in one database or spread across multiple databases very well.  Let’s say you have a site that sells products online for small companies you would need tables like company, customer, order, product, invoice etc, you would probably put all your data into a single set of tables and EF will work just fine.

However, if you have ever worked Microsoft Dynamics Nav (Navision) you know that it does not follow this standard structure.

Dynamics segregates the data differently. Each company had its own set of customer, order, product, invoice, etc, tables which were prefixed with the company name. For example, if you had two companies called ACME and SuperCorp the table structure would be like the following –

  • [ACME$Customer]
  • [ACME$Order]
  • [ACME$Product]
  • [ACME$Invoice]
  • [SuperCorp$Customer]
  • [SuperCorp$Order]
  • [SuperCorp$Product]
  • [SuperCorp$Invoice]

EF does not play nicely with this.

My first attempt was to pass in the table prefix to my DbContext when mapping, this worked for the first company passed in, but when I create a new DbContext for the second company, it would give model errors because somewhere deep in the workings of EF, something was being set statically and the mapping was failing (it was still pointing to the first company). If I created another new DbContext for the first company it worked fine again.

My second attempt was to pass in a DbCompiledModel for the company when constructing  the DbContext -

DbContext(string nameOrConnectionString, System.Data.Entity.Infrastructure.DbCompiledModel model)

This worked a bit better than the first attempt, I could create a DbContext for the first and second and first (again) companies. I could retrieve data, but as soon as a there was a join in the query it failed, again it looked like something was being set the first time I created the DbContext and this was not changing when for the second company. I wasn’t happy with this approach anyway as it required a significant step away from well known EF practices of mapping and constructing a context. My third approach was to create a context file specifically for each company which would inherit from a base context that contained all the DbSets and column mappings, table mapping was done in each individual context. This approached worked for all database queries, but has the huge issue of requiring a recompile and redeployment for each new company; a working, but impractical approach.

Building on this I came up with my solution. I created a CommonDBContext, which has the DbSets and the OnModelCreating where the table prefix is passed to the Mapping classes. A DynamicContext class that is used to dynamically compile and construct DbContexts for each of the companies as needed. It also caches the compiled DbContext so you only take the compilation hit once for the lifetime of the application.

  1public abstract class CommonDBContext : DbContext
  2{
  3
  4private readonly string tablePrefix;
  5
  6protected CommonDBContext(string connectionString, string tablePrefix) :base(connectionString)
  7    {
  8        this.tablePrefix = tablePrefix;
  9    }
 10
 11    public DbSet Customer { get; set; }
 12    public DbSet Order { get; set; }
 13    public DbSet Product { get; set; }
 14    public DbSet Invoice { get; set; }
 15    protected override void OnModelCreating(DbModelBuilder modelBuilder)
 16    {
 17        modelBuilder.Configurations.Add(new CustomerMap(tablePrefix));
 18        modelBuilder.Configurations.Add(new OrderMap(tablePrefix));
 19        modelBuilder.Configurations.Add(new ProductMap(tablePrefix));
 20        modelBuilder.Configurations.Add(new InvoiceMap(tablePrefix));
 21    }
 22}
 23
 24class DynamicContext
 25{
 26    private readonly static Dictionary<string, Type> CompiledContexts = new Dictionary<string, Type>();
 27    private readonly string _classNamePrefix;
 28    private static readonly object LockObject = new object();
 29    private readonly string _sourceCode =
 30            @"
 31            using System.Data.Entity;
 32            namespace Implementation.Context
 33            {{
 34                public class {0}_Context : CommonDBContext
 35                {{
 36                  public {0}_Context(string connectionString, string tablePrefix)
 37                        : base(connectionString, tablePrefix)
 38                    {{
 39                        Database.SetInitializer<{0}_Context>(null);
 40                    }}
 41                }}
 42            }}";
 43
 44    #region Constructors
 45
 46    public DynamicContext(string classNamePrefix)
 47    {
 48        _sourceCode = string.Format(_sourceCode, classNamePrefix);
 49        _classNamePrefix = classNamePrefix;
 50    }
 51
 52    #endregion
 53
 54    #region Public Methods
 55
 56    public CommonDBContext GetContext(string connectionString, string tablePrefix)
 57    {
 58        Type[] constructorTypes = new Type[2] { typeof(string), typeof(string) };
 59
 60        Type compiledType;
 61        //Double lock to prevent multiple compilations of same code
 62        if (!CompiledContexts.TryGetValue(_classNamePrefix, out compiledType))
 63        {
 64            lock (LockObject)
 65            {
 66                if (!CompiledContexts.TryGetValue(_classNamePrefix, out compiledType))
 67                {
 68                    compiledType = CompileContext(_classNamePrefix);
 69                    CompiledContexts.Add(_classNamePrefix, compiledType);
 70                }
 71            }
 72        }
 73
 74        CommonDBContext contextObject = GetInstanceOfCompiledContext(connectionString, tablePrefix, compiledType, constructorTypes);
 75        return contextObject;
 76    }
 77
 78    #endregion
 79
 80    #region Private Methods
 81
 82    private CommonDBContext GetInstanceOfCompiledContext(string connectionString, string tablePrefix, Type compiledContext, Type[] constructorTypes)
 83    {
 84        ConstructorInfo contextConstructorInfo = compiledContext.GetConstructor(constructorTypes);
 85        object contextObject = contextConstructorInfo.Invoke(new object[] { connectionString, tablePrefix });
 86        return contextObject as CommonDBContext;
 87    }
 88
 89    ///
 90<summary>
 91    /// This is a one time hit for each class compiled.
 92    /// </summary>
 93
 94    /// <param name="assemblyNames"></param>
 95    /// <returns></returns>
 96    private string[] GetAssembly(string[] assemblyNames)
 97    {
 98        string [] locations = new string[assemblyNames.Length];
 99
100        for (int loop = 0; loop <= assemblyNames.Length - 1; loop++) { locations[loop] = AppDomain.CurrentDomain.GetAssemblies().Where(a => !a.IsDynamic && a.ManifestModule.Name == assemblyNames[loop]).Select(a => a.Location).First();
101        }
102        return locations;
103    }
104
105    private Type CompileContext(string classNamePrefix)
106    {
107        var compilerParameters = new CompilerParameters { GenerateInMemory = true };
108
109        compilerParameters.ReferencedAssemblies.AddRange(GetAssembly(new string[] { "Implementation.dll", "EntityFramework.dll" }));
110        var provider = new CSharpCodeProvider();
111        var compiled = provider.CompileAssemblyFromSource(compilerParameters, _sourceCode);
112
113        Type compliedType = compiled.CompiledAssembly.GetType(string.Format("Implementation.Context.{0}_Context", classNamePrefix));
114        return compliedType;
115    }
116
117    #endregion
118
119}
120
121public class CustomerMap : EntityTypeConfiguration
122{
123    public CustomerMap(string tablePrefix)
124    {
125        // Primary Key
126        this.HasKey(t => t.CustomerID);
127        //snip
128
129        // Map the table name
130        string tableName = string.Format("{0}$Customer", tablePrefix);
131        this.ToTable(tableName)
132    }
133}
comments powered by Disqus

Related