How to create DbContext at runtime

Question

User-1262787652 posted

ASP.NET 5 MVC Core application uses databases whose table columns are slightly different.

Npgsql EF Core provider is used.

How to create DbContext at runtime ? If dependency injection requires DbContext object first time, scaffold should create source code. This code should be compiled into assembly, this assembly shoud loaded into memory, DbContext constructor should called and resulting instance shoudl returned.

How to implement this ? Is there some NuGet package or framework for this ?

Answer 1

User475983607 posted

ASP.NET 5 MVC Core application uses databases whose table columns are slightly different.

 

Npgsql EF Core provider is used.

 

How to create DbContext at runtime ? If dependency injection requires DbContext object first time, 

scaffold should create source code. This code should be compiled into assembly, this assembly shoud loaded into memory, 

DbContext constructor should called and resulting instance shoudl returned.

 

How to implement this ? Is there some NuGet package or framework for this ?

As far as I can tell, your requirement has a major design bug.  How will the code base know about the dynamically created entities?  

I think your question can be rephrased as, How to create and build a project based on a database.  I believe the entire project can be created, built, and run using openly published CLI commands and a batch file.   

https://docs.microsoft.com/en-us/dotnet/core/tools/

https://docs.microsoft.com/en-us/ef/core/cli/dotnet#common-options

Answer 2

User753101303 posted

Hi,

The purpose of this difference being? For example if I wanted to allow users to add custom columns I would rather look if JSON support such as   https://www.postgresql.org/docs/current/datatype-json.html could help.. What is your scenario?

Answer 3

User-1262787652 posted

Hi!

Development is done against base database and base DbContext.

Customers are adding additional columns to their databases and I want application to save data to those columns also. Something like

if ( DatabaseName=="Child1" ) {
  dynamic product = ctx.Find<Product>(123);
  product.CustomPrice = 12.3m;
  ctx.SaveChanges();
  }

CustomPrice column exists only in Child1 database. 

In Child2 database there may be CustomColor column:

if ( DatabaseName=="Child2" ) {
  dynamic product = ctx.Find<Product>(123);
  product.CustomColor = "white";
  ctx.SaveChanges();
  }

Running this code causes exception since Find<> does not return custom properties (those do not exists in base DbContext used for development).

How to allow this code to run ? 

Answer 4

User-1262787652 posted

mgebhard

How will the code base know about the dynamically created entities?  

dynamic is used to get custom data like

dynamic product = ctx.Find<Product>(1);

product.CustomColumn = "custom value";

There are also examples in

https://medium.com/@zaikinsr/roslyn-ef-core-runtime-dbcontext-constructing-285a9d67bc87
https://github.com/jdtcn/RuntimeEfCore

mgebhard

I believe the entire project can be created, built, and run using openly published CLI commands and a batch file.   

There are lot of databases lot of tables. Custom properies are changed frequenty.

Should I create new project for every database change and run separate application for every database ?

Answer 5

User475983607 posted

dynamic is used to get custom data like

dynamic product = ctx.Find<Product>(1);

product.CustomColumn = "custom value";

There are also examples in

https://medium.com/@zaikinsr/roslyn-ef-core-runtime-dbcontext-constructing-285a9d67bc87
https://github.com/jdtcn/RuntimeEfCore

Your response does not answer the question.  How does your code base know the Product table exists?

There are lot of databases lot of tables. Custom properies are changed frequenty.

Many of the developers on these forums supports a lot of database that contains a lot of tables with custom properties and frequent changes.  Why is your project different?  Maybe you are new to building and maintaining applications?

Should I create new project for every database change

Well no.  How you go about adding features to an application is called the Software Development Lifecycle which is a process established by your team or organization or you.

Typically, application feature updates follow a database first or code first approach.  In a database first, changes are made directly to the database using TSQL scripts.  At the same time, the code base is updated to handle the database changes.   In a code first approach, changes are made to the code which drives the database changes.

and run separate application for every database

You are the only one that knows the answer to this question.  It is not uncommon for an  application to require one or many databases.  It is also not uncommon for an application to consume many different HTTP services. 

Why are you unaware of the databases your application require to operate?

Answer 6

User-1262787652 posted

mgebhard

How does your code base know the Product table exists?

Product table existis always. There are same tables in all databases. Custom columns are added to existing tables.

mgebhard

Why is your project different? 

In my case customers can add custom columns to database from application. Application generates

ALTER TABLE product ADD COLUMN customprice  TYPE NUMERIC(8,2)

and runs it.

mgebhard

In a database first, changes are made directly to the database using TSQL scripts.  At the same time, the code base is updated to handle the database changes. 

Using this after adding column to database application should generate new dbcontext.

Should DbContext placed to separate project so that separate dll file is created?

Should application generate new dll file and replace existing one if it adds new column?

Every web site ues different (single) database. This requires running separate application for every web site. Also single-file deplyoment cannot used, dbcontext.dll file should exist on disk.

mgebhard

Why are you unaware of the databases your application require to operate?

For every web site there is only one database containing fixed number of tables.  This is know to application. Business rules are continuously changing.

Customers needs to add columns to existing tables without changing application.

Answer 7

User475983607 posted

In my case customers can add custom columns to database from application. Application generates

ALTER TABLE product ADD COLUMN customprice  TYPE NUMERIC(8,2)

and runs it.

No matter how many time you ask this question, the problem is the same.  It does not matter if you created a DbContext on the fly or returned SELECT * FROM SomeTable into an ADO.NET DataSet or DataTable, or implemented a JSON column,  The current code base has no idea the new "customprice" column exists.  The best you can do is display or update the "customprice" column value.

The last sentence comes with a second programming problem that must be solved.  The existing code base must be designed to expect dynamic column names.  Not just the new columns, but all the columns.  Most likely this change will filter to the business logic because you're dealing with an name/value pairs.  

Answer 8

User-1262787652 posted

Hi!

The existing code base must be designed to expect dynamic column names.  Not just the new columns, but all the columns.  Most likely this change will filter to the business logic because you're dealing with an name/value pairs.  

It looks like there are two options if entity pypes arew not created directly by code.

1. Create specific DbContext for every database. Re-create it if properties are added. In this case Find<> returns all columns names.  GetProperties() or dynamic type can used on returned entities then.
2. Use key value-pairs design. Using plain ADO .NET DataReader looks best for this.

If entity types with custom properties are created in code there is more possibility:

3. Create  Generic DbContext  class

MyDbContext<TProduct, TCustomer, TOrder, TInvoice, ... >

and pass created custom types to it to create DbContext for specific child database.

Which option should used ?

Option 3, Generic  DbContext looks interesting. Is it fully supported by EF Core or will there be issues ?

Answer 9

User1535942433 posted

Hi kobruleht,

You could  create a generic repository.And it support to EF core.

This generic repository will handle typical CRUD requirements. When a particular entity type has special requirements, such as more complex filtering or ordering, you can create a derived class that has additional methods for that type.

More details,you could refer to below article:

https://docs.microsoft.com/en-us/aspnet/mvc/overview/older-versions/getting-started-with-ef-5-using-mvc-4/implementing-the-repository-and-unit-of-work-patterns-in-an-asp-net-mvc-application

Best regards,

Yijing Sun

Answer 10

User-821857111 posted

What you are talking about is usually referred to as user-defined fields. They are common in CMS and CRM apps. You give the customer a base Contact form, and they can add fields to customise their version of the application. However, the fields aren't added to the original contact table. They are stored in a separate table specific for that purpose. That table will hold information about each field, such as its label name, data type, which form or entity it belongs to, its position in the UI, its tab order, whether there are any validation rules that should apply to it etc. And from day 1, your context has a DbSet<UserDefinedField>. No need to alter the schema of anyone's database dynamically.

Answer 11

User-1262787652 posted

Hi!

Thank you. 

If user-defined fields are used in business logic.

Different main tables have different type pimary keys.  Some main tables have composite primary keys, up to 3 columns. Primay key columns may have integer, character and date type.

So user-defined fields table should have 3 character columns storing references to primary keys in main table.

Foreign keys cannot used since user-defined colums table contains references to all ERP tables and types are different. Database structure becomes unnormalized.  If primary key value changes (natural primary keys are used in some tables), values shoud manually changed in user-defined fields table also (in normal tables REFERENCES maintable ON UPDATE CASCADE handles this automatically).

To make joins, primary key values should casted to character values. This may cause perfomance hit and require additional optimmization.

To delete row in main table, separate manual queries shoul used to delete from user-defined table also. Single delete statements should changed to two statements and wrapped to  transaction.

Databases with additional user-defined columns in main tables are in production use. Changing structure requires re-writing existing applications and creating migration scripts.

I think separate table for  user-defined columns is not reasonable in my case.

Answer 12

User475983607 posted

As far as I can tell, you are looking for a magical solution that plugs into your existing application.  It does not exist.  You must rewrite your application to support your customer's requirements.

Answer 13

User753101303 posted

You could also use a JSON column for example. Anyway the problem is rather than your app requires to generate an,d recompile code to take those changes into account.

AFAIK most if not all applications that are handling custom columns are written so that the application code can "discover" and use those new columns.

Also the level of customizatin you allow is an important parameter into which solution you"ll use. Also at some point you ar back at being a developer. For example "SAP or Dynamics AX" are highly customiezable but you are back at using programming tools.