I have a console application written in C# that runs as a service each hour. The application has a Data Access Layer (DAL) to connect to the database with a Db Context. This context is a property of the DAL and is created each time the DAL is created. I believe this has lead to errors when updating various elements.
Question: Should the application create a Db Context when it runs and use this throughout the application so that all objects are being worked on with the same context?
Since a service can be running for a long time, it is a good practice to open the connection, do the job and then close the connection.
If you have a cadence of methods then you could pass your opened DbContext as a parameter.
For instance:
call to A
call to B(DbConteext)
call to C(DbContext)
Another good practice is to protect your code with try/catch, because your database could be offline, not reachable, etc.
Question: Should the application create a Db Context when it runs and use this throughout the application so that all objects are being worked on with the same context?
You should (re)create your DbContext whenever you suspect the underlying data has changed. Because the DbContext assumes that data once fetched from the data source is never changed and can be returned as a result of a query, even if that query might come minutes, hours or years later. It's caching, with all it's advantages and disadvantages.
I would suggest you (re)create your DbContext whenever you start a new loop of your service.
DbContext is really an implementation of Unit of Work pattern so it represents a single business transaction which is typically a request in a web app. So it should be instantiated when a business transactions begins, then some operations on db should be performed and commited (thats SaveChanges) and the context should be closed.
If running a console app represents a business transaction so it's kind of like a web request then of course you can have a singleton instance of DbContext. You cannot use this instance from different threads so your app should be single-threaded and you should be aware that DbContext is caching some data so eventually you may have memory issues. If your db is used by many clients and the data changes often you may have concurrency issues if the time between fetching some data from db and saving them is too long which might be the issue here.
If not try to separate your app into some business transactions and resolve your db context per those transactions. Such a transaction could be a command entered by user.
