Good day, I tell you the following problem that I see to see what they advise me to do to solve the problem. Step to explain:
The system has several entities that refer to categories of vouchers such as purchase, sale, payments, etc. They are approximately 20 categories. In the database are represented as follows
I have the table of vouchers that has general data for all the categories and then a table for each category where specific columns of each category are specified. The model is assembled with the DataBase First method and we apply inheritance between the vouchers table and the rest, that is, all the voucher category tables inherit from the vouchers table
Proofs
- Vouchers_sales
- Recebantes_cobro
- etc
To access from EF to consult the vouchers I do it this way:
_context.Comprobantes.OfType<Comprobantes_Cobro>().Where(x => x.ID_Comprobante == id).FirstOrDefault();
But I have several parts of the system where you directly consult receipts without using ofType and that's where the problem comes from. A week ago we added columns to one of the category tables and from that change, the "NOT CASTED" queries generate a query that causes the SQL Server Engine to give me this error:
Internal error: An expression services limit has been reached. Please look for potentially complex expressions in your query, and try to simplify them.
What we did was make the refactor of the queries that caused problems, the thing is that soon I will have to continue putting columns and more tables of categories to EDMX and this will increasingly make the situation worse.
The situation is that EF can not replace it since the whole system is coupled to it, I have many SPs added to the EDMX and all the querys too. Nor can I change the structure of the tables, because it would be out to refactor more than 200 SPs.
So what I thought was to access the data in another way from here on and that lives with EF, such as Dapper or another ORM that builds the querys in a more optimal way. But I do not know if this is advisable or if there is another way to solve this scenario.
The version of EF we use is 6
I hope you can help me, greetings !!