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 ?
ASP.NET 5 MVC Core application uses databases whose table columns are slightly different.
</div> <div></div> <div>Npgsql EF Core provider is used.</div> <div></div> <div>How to create DbContext at runtime
? If dependency injection requires DbContext object first time, </div> <div>scaffold should create source code. This code should be compiled into assembly, this assembly shoud loaded into memory, </div> <div>DbContext constructor
should called and resulting instance shoudl returned.</div> <div></div> <div>How to implement this ? Is there some NuGet package or framework for this ?
</div>
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.
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?
Your response does not answer the question. How does your code base know the Product table exists?
kobruleht
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?
kobruleht
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.
kobruleht
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?
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.
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.
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.
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.
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:
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.
.NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today.
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.
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.
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.
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.
Member
52 Points
594 Posts
How to create DbContext at runtime
Jan 26, 2021 11:14 AM|kobruleht|LINK
All-Star
52971 Points
23574 Posts
Re: How to create DbContext at runtime
Jan 26, 2021 12:42 PM|mgebhard|LINK
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
All-Star
48490 Points
18071 Posts
Re: How to create DbContext at runtime
Jan 26, 2021 01:30 PM|PatriceSc|LINK
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?
Member
52 Points
594 Posts
Re: How to create DbContext at runtime
Jan 26, 2021 01:45 PM|kobruleht|LINK
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 ?
Member
52 Points
594 Posts
Re: How to create DbContext at runtime
Jan 26, 2021 01:54 PM|kobruleht|LINK
dynamic is used to get custom data like
There are also examples in
https://medium.com/@zaikinsr/roslyn-ef-core-runtime-dbcontext-constructing-285a9d67bc87
https://github.com/jdtcn/RuntimeEfCore
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 ?
All-Star
52971 Points
23574 Posts
Re: How to create DbContext at runtime
Jan 26, 2021 02:32 PM|mgebhard|LINK
Your response does not answer the question. How does your code base know the Product table exists?
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?
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.
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?
Member
52 Points
594 Posts
Re: How to create DbContext at runtime
Jan 26, 2021 02:57 PM|kobruleht|LINK
Product table existis always. There are same tables in all databases. Custom columns are added to existing tables.
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.
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.
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.
All-Star
52971 Points
23574 Posts
Re: How to create DbContext at runtime
Jan 26, 2021 04:36 PM|mgebhard|LINK
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.
Member
52 Points
594 Posts
Re: How to create DbContext at runtime
Jan 26, 2021 05:01 PM|kobruleht|LINK
Hi!
It looks like there are two options if entity pypes arew not created directly by code.
If entity types with custom properties are created in code there is more possibility:
3. Create Generic DbContext class
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 ?
Contributor
3730 Points
1412 Posts
Re: How to create DbContext at runtime
Jan 27, 2021 05:50 AM|yij sun|LINK
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
All-Star
194428 Points
28074 Posts
Moderator
Re: How to create DbContext at runtime
Jan 27, 2021 07:00 AM|Mikesdotnetting|LINK
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.Member
52 Points
594 Posts
Re: How to create DbContext at runtime
Jan 27, 2021 07:40 AM|kobruleht|LINK
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.
All-Star
52971 Points
23574 Posts
Re: How to create DbContext at runtime
Jan 27, 2021 01:05 PM|mgebhard|LINK
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.
All-Star
48490 Points
18071 Posts
Re: How to create DbContext at runtime
Jan 27, 2021 02:08 PM|PatriceSc|LINK
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.