Summary
In this post I should 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.
public abstract class CommonDBContext : DbContext { private readonly string tablePrefix; protected CommonDBContext(string connectionString, string tablePrefix) :base(connectionString) { this.tablePrefix = tablePrefix; } public DbSet Customer { get; set; } public DbSet Order { get; set; } public DbSet Product { get; set; } public DbSet Invoice { get; set; } protected override void OnModelCreating(DbModelBuilder modelBuilder) { modelBuilder.Configurations.Add(new CustomerMap(tablePrefix)); modelBuilder.Configurations.Add(new OrderMap(tablePrefix)); modelBuilder.Configurations.Add(new ProductMap(tablePrefix)); modelBuilder.Configurations.Add(new InvoiceMap(tablePrefix)); } } class DynamicContext { private readonly static Dictionary<string, Type> CompiledContexts = new Dictionary<string, Type>(); private readonly string _classNamePrefix; private static readonly object LockObject = new object(); private readonly string _sourceCode = @" using System.Data.Entity; namespace Implementation.Context {{ public class {0}_Context : CommonDBContext {{ public {0}_Context(string connectionString, string tablePrefix) : base(connectionString, tablePrefix) {{ Database.SetInitializer<{0}_Context>(null); }} }} }}"; #region Constructors public DynamicContext(string classNamePrefix) { _sourceCode = string.Format(_sourceCode, classNamePrefix); _classNamePrefix = classNamePrefix; } #endregion #region Public Methods public CommonDBContext GetContext(string connectionString, string tablePrefix) { Type[] constructorTypes = new Type[2] { typeof(string), typeof(string) }; Type compiledType; //Double lock to prevent multiple compilations of same code if (!CompiledContexts.TryGetValue(_classNamePrefix, out compiledType)) { lock (LockObject) { if (!CompiledContexts.TryGetValue(_classNamePrefix, out compiledType)) { compiledType = CompileContext(_classNamePrefix); CompiledContexts.Add(_classNamePrefix, compiledType); } } } CommonDBContext contextObject = GetInstanceOfCompiledContext(connectionString, tablePrefix, compiledType, constructorTypes); return contextObject; } #endregion #region Private Methods private CommonDBContext GetInstanceOfCompiledContext(string connectionString, string tablePrefix, Type compiledContext, Type[] constructorTypes) { ConstructorInfo contextConstructorInfo = compiledContext.GetConstructor(constructorTypes); object contextObject = contextConstructorInfo.Invoke(new object[] { connectionString, tablePrefix }); return contextObject as CommonDBContext; } /// <summary> /// This is a one time hit for each class compiled. /// </summary> /// <param name="assemblyNames"></param> /// <returns></returns> private string[] GetAssembly(string[] assemblyNames) { string [] locations = new string[assemblyNames.Length]; 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(); } return locations; } private Type CompileContext(string classNamePrefix) { var compilerParameters = new CompilerParameters { GenerateInMemory = true }; compilerParameters.ReferencedAssemblies.AddRange(GetAssembly(new string[] { "Implementation.dll", "EntityFramework.dll" })); var provider = new CSharpCodeProvider(); var compiled = provider.CompileAssemblyFromSource(compilerParameters, _sourceCode); Type compliedType = compiled.CompiledAssembly.GetType(string.Format("Implementation.Context.{0}_Context", classNamePrefix)); return compliedType; } #endregion } public class CustomerMap : EntityTypeConfiguration { public CustomerMap(string tablePrefix) { // Primary Key this.HasKey(t => t.CustomerID); //snip // Map the table name string tableName = string.Format("{0}$Customer", tablePrefix); this.ToTable(tableName) } }
If you have trouble getting this working, get in touch.
Hello Byran,
First of all great post. Thanks for it.
Now, I have a different scenario, where Table with unique names
i.e.,
Table 1 -> 123-asd-45
Table2 -> 676-bvs-98
are added dynamically
to DB when i execute a store procedure to create Table. So how can i achieve this.
Any help appreciated !!!
Regards
Rizwan
Glad it was helpful to you.
If the columns in the tables are known ahead of time you should be able to follow my example with tablePrefix, of course you will pass in the whole table name instead of just the prefix.
If the columns change, you will probably need dynamic class compilation and construction. You can follow my example for that too.
Hope that helps.
Bryan
Hi Bryan,
Thanks for reply. I will try it out.
Regards
Hello Bryan,
One question. I have to create. Create Dynamic Tables frequently. And all other operation
are performed on this Tables, So will this not let to performance issue, As EF will get configure each time new table is created? Can you explain the process of above code.
Regards
Take a look at https://msdn.microsoft.com/library/cc853327(v=vs.100).aspx for performance concerns.
How did you initialize the object?
Which object?
Hi Bryan,
I am getting exception in GetAssembly function
locations[loop] = AppDomain.CurrentDomain.GetAssemblies().Where(a => !a.IsDynamic && a.ManifestModule.Name == assemblyNames[loop]).Select(a => a.Location).First();
Exception:
System.InvalidOperationException occurred
HResult=0x80131509
Message=Sequence contains no elements
Source=MoviesExample
StackTrace:
at MoviesExample.Models.DynamicContext.GetAssembly(String[] assemblyNames) in C:\Users\hyarlagadda\Documents\Visual Studio 2017\Projects\MoviesExample\MoviesExample\Models\CommonDBContext.cs:line 108
at MoviesExample.Models.DynamicContext.CompileContext(String classNamePrefix) in C:\Users\hyarlagadda\Documents\Visual Studio 2017\Projects\MoviesExample\MoviesExample\Models\CommonDBContext.cs:line 117
at MoviesExample.Models.DynamicContext.GetContext(String connectionString, String tablePrefix) in C:\Users\hyarlagadda\Documents\Visual Studio 2017\Projects\MoviesExample\MoviesExample\Models\CommonDBContext.cs:line 75
at MoviesExample.Controllers.MoviesController..ctor() in C:\Users\hyarlagadda\Documents\Visual Studio 2017\Projects\MoviesExample\MoviesExample\Controllers\MoviesController.cs:line 15
If I change First() to FirstOrDefault() then it is failing at
var compiled = provider.CompileAssemblyFromSource(compilerParameters, _sourceCode); with null reference exception.
Can you please help me?
Hi Harish,
If you can put all your code on github I will take a look.
Bryan
I’m having the same issue. Did you find a solution?
Brett, can you share you code on GitHub?
I can recreate my test project at home and upload it later. My office restricts uploads.
It seems to be an issue with finding Implementation.dll in the following line of code.
compilerParameters.ReferencedAssemblies.AddRange(GetAssembly(new string[] { “Implementation.dll”, “EntityFramework.dll” }));
Brilliant. You saved my day, my friend
Glad to hear that.
I am new to Entity Framework, and am having a bit of trouble seeing how to properly implement this… which is frustrating as it is exactly what I need. Any help and/or pointers in the right direction would be appreciated.
Hi Brian,
I suggest trying EF Core, maybe you won’t need any dynamic compilation.
We couldn’t implement your code to our testproject. We call your GetContext method like that; CommonDBContext b = db.GetContext(conStr, cmpName); but we are not sure about this usage. Can you help us about the issue?
The tricky thing here was the dynamic compilation. But, if you use EF Core you might not even need this.
All you might have to do is pass the table name to the constructor of the context and map to the right table.
what is db here??
and how we use this method.
I was using SqlServer, don’t know what version.
How would you implement this with login? For instance the username is name@companyx.com and we use companyx as the table prefix. So each time a user from a different company logs in they are directed to their company table
Hopefully with the newer versions of Entity Framework you won’t need to do all the dynamic complication I was doing.
You might be able to pass a table name prefix parameter to the constructor of your context.
Thanks Bryan for posting some interesting thing.
I tried to compile & get new context in my code, but some how it takes data from old table even after passing table prefix.
In my case it never come in OnModelCreating event when context created.
Here is what I have,
SQL Server DB with table name Employee & Company1_Employee.
here is my code
DynamicContext d = new DynamicContext(“Company1”);
CommonDBContext CContext = d.GetContext(connstr, “Company1”);
var data = CContext.Employees.ToList(); // this should return data from Company1_Employee table.
Let me know if you have any idea about this.
Hi Pritam,
It is a very long time since I worked on this, I’m afraid I can’t help you beyond what is in the post.
Good luck,
Bryan
Initially I was facing some problems related to ReferencedAssemblies & after resolving it, instance was not creating and when instance created I was not able to get data from new table. On digging more on code, after sometime I am able to get data from new table successfully. Thanks for posting this article. It was really helpful.
Glad to hear it!
Hi Pritam,
Could you please post your complete solution in Github
Hi Pritam,
I’m sorry to say I don’t have the code anymore.