Leverage ef core to get more performant joins and filters

[omelhus]

Hey,

Have you considered using an EF core db context to get more performant joins and filters on joined tables?

I know that you have to support fetching computed fields from VBS, but this can be solved with a dataloader. 

- Ole

[Arnstein By]

I asked our System Architect about this and his reply is this:
There are multiple considerations why the core logic cannot make use of EF core that range from on-prem and cloud compatibility down to legacy code. We are constantly working on addressing query performance but even if given the chance to work on fresh and new solution, I wouldn't resort to EF core or classic. First of all, query performance is given by how well you write the query itself and how well your tables are designed and indexed. To work with an ORM, I would resort to Dapper or not work with ORMs at all if performance is my top priority. 

@omelhus We're happy to hear your comments on this.

- Arnstein

[omelhus]

Thank you for your reply.

My comment was not related to the absolute best performance, but a more performant way to fetch data than going through VBS for every request. I think wiring up all the queries and joins manually for a complex system as VB will take so much time that the API is obsolete before it's done, and with the performance in existing ORMs I see no reason to invent the wheel again.

I'm mentioning specifically EF Core because that's what I have experience with and have been tinkering with using the Business data model, scaffolding and GraphQL. Dapper or other ORMs might have scaffolding solutions, but I know nothing about that.

I think the API would benefit tremendously by skipping the VBS for queries that don't access calculated fields. This would probably also lower the compute power required to run the API-solution, as much of the query can be off-loaded to the SQL-server(s). Computed fields can be fetched using a data loader using primary keys. I'm seeing a 10x improvement in query times using the SQL+dataloader approach over the implementation in nxt, using approximately the same dataset.

I'm happy to demonstrate this over teams.