I have been working on a multi-layer, sale management application developed with WPF and partly refactored with EF6 (long story). The application has some very complex tasks which require multiple calls to the database with obvious performance issues. Say, to issue a customer order the application run a task that requires reading, inserting and updating at least a dozen DB tables or more. There are obvious architectural issues. A refactoring is out of the question because it would require re-writing the whole application. But, I could not stop thinking about how I would deal with an application that requires writing data on so many tables if I had the chance to write it from the ground up.
So, if you had to develop an application with such requirements, and you had to keep EF6 as the underlying technology, my question is: how would you design tasks which required multiple DB calls? I thought of some options:
writing data on 1 or 2 main tables, writing the rest of the correlated data on a support table and enable triggers on the DB which would spread the data from the support table to the target tables.
calling a single stored procedure which would take care of writing all the data to the target tables (but I am not a fan of writing business logic in the DB)
using async calls where possible, but it may end up being complicated to keep things in sync.
splitting the task so that correlated data is written in an automated batch at a later time. This option looks to me as the most error-prone.
Any thought?