Currently we are using a combination of SqlKata and RepoDB for our database implementation. This works great, and allows us to seamlessly query multiple types of databases by just creating a single query which is automatically converted to the correct syntax depending on the database that is currently being used.
For object relationships, this is typically done using RepoDB's ExecuteQueryMultiple methods. However, these methods has a performance issue because we are executing multiple queries in order to get the information we need. This can normally be solved by using JOIN queries.
JOIN's works fine with RepoDB as long as it is used to return one type of data structure, or we can create DTO's tailored towards certain JOIN queries 3. However, a problem quickly arises when we are trying to join multiple tables that has the same column names. This throws an exception because RepoDB does not understand how to deal with this.
The RepoDB developers does also not seem to like the idea of supporting multi-JOIN queries as it tends to be complex, which goes against RepoDB's culture 1.
Solutions
Solution 1
One solution is to implement our own mapping functionality for JOIN queries that supports what we need. However, after trying to figure out how RepoDB's mapping works, I realized that it does not provide any extension points in this area. For JOIN mapping to work, we need a way to individually map columns to their counterpart property in the model, but RepoDB has this functionality locked internally. Which means we would need to implement our own mapping as an additional type of ORM. Meaning that we are essentially creating a duplicate of RepoDB's value mapper. Which is not very ideal.
Solution 2
The other solution still involves creating our own mapper, but instead of using RepoDB, we do it all on our own. There are some libraries like AutoMapper 4 that can help with this. However, creating our own ORM is very time consuming and complex.
Solution 3
Go back to dapper, because dapper supports JOIN's mostly in the way we need it to work. We can still use SqlKata to build queries and make sure everything works with multiple database types. However, dapper is a very simple ORM and we will lose alot of the features that RepoDB provides.
Other solutions
Perhaps there is a ORM library that we have not seen yet, which has everything we need. You may think that EFCore can be a solution, however we already tried it, and it only caused massive issues and complexity due to it's context-based system which caches results. And this simply does not work in the stateful multithreaded architecture that EvoSC# is designed around.
But if anyone has any other good solutions, feel free to shout them out here!
Problem
Currently we are using a combination of SqlKata and RepoDB for our database implementation. This works great, and allows us to seamlessly query multiple types of databases by just creating a single query which is automatically converted to the correct syntax depending on the database that is currently being used.
For object relationships, this is typically done using RepoDB's
ExecuteQueryMultiplemethods. However, these methods has a performance issue because we are executing multiple queries in order to get the information we need. This can normally be solved by using JOIN queries.JOIN's works fine with RepoDB as long as it is used to return one type of data structure, or we can create DTO's tailored towards certain JOIN queries 3. However, a problem quickly arises when we are trying to join multiple tables that has the same column names. This throws an exception because RepoDB does not understand how to deal with this.
The RepoDB developers does also not seem to like the idea of supporting multi-JOIN queries as it tends to be complex, which goes against RepoDB's culture 1.
Solutions
Solution 1
One solution is to implement our own mapping functionality for JOIN queries that supports what we need. However, after trying to figure out how RepoDB's mapping works, I realized that it does not provide any extension points in this area. For JOIN mapping to work, we need a way to individually map columns to their counterpart property in the model, but RepoDB has this functionality locked internally. Which means we would need to implement our own mapping as an additional type of ORM. Meaning that we are essentially creating a duplicate of RepoDB's value mapper. Which is not very ideal.
Solution 2
The other solution still involves creating our own mapper, but instead of using RepoDB, we do it all on our own. There are some libraries like AutoMapper 4 that can help with this. However, creating our own ORM is very time consuming and complex.
Solution 3
Go back to dapper, because dapper supports JOIN's mostly in the way we need it to work. We can still use SqlKata to build queries and make sure everything works with multiple database types. However, dapper is a very simple ORM and we will lose alot of the features that RepoDB provides.
Other solutions
Perhaps there is a ORM library that we have not seen yet, which has everything we need. You may think that EFCore can be a solution, however we already tried it, and it only caused massive issues and complexity due to it's context-based system which caches results. And this simply does not work in the stateful multithreaded architecture that EvoSC# is designed around.
But if anyone has any other good solutions, feel free to shout them out here!
References