How to organize a "repository" pattern with the ability to switch to different DBMS, namely in c#?

Question

.NET

Hello.
I write in c#. But the language is not important for the most part.
I have a library in which I work with data from a database. In order not to write queries to the database for typical operations, I decided to organize access to the database through the "Repository" pattern, the main class of which can do CRUD (generalized class). I inherit this class when I need to create a specific repository. For example:
News Repository, Users Repository and so on. In fact, for each table I have my own repository that can CRUD and sometimes something specific, for example, for news there is a "FindByUrl" method and so on.
I am currently working with mysql. Therefore, in the project I have a Repositories / Mysql folder, in which all the repository files are located.
In the future, it is planned to switch simply by switching to other subds. I thought about how to implement this and for this I created my own interface for each repository. Now, for another subd, I just need to create the Repositories / OtherBD folder using the interfaces and create repositories in it.
To switch between subds, I decided to make a Factory. But on this and there was a stupor. And a number of questions arose, for which I ask for advice:

1. It turns out now for each repository I have to write my own factory? Is it possible to do this with one factory, so that you select a subd and then just call the necessary repository?
   
2. Is my approach of avoiding sql query in the code and switching subds in the program correct? Are there other options?
   
3. For the "Repository" pattern, is it correct to constantly increment methods like "FindByUrl"? If not, what's the suggestion? And if you need a selection of two repositories (actually join)?
   

PS On the first point, I thought of creating a type of Repository of Repositories, which stores all the repositories in itself and then, if necessary, it simply issues them. For example, MysqlRepo has methods - GetNewsRepo, GetUsersRepo and so on. But there will be at least 30 repositories. Is it necessary to write a getter / setter in the main repository for each repository? A lot and hard.
Maybe there is a solution for .net?

Answer 1

It turns out for each repository you need to write a getter / setter in the main repository? A lot and hard.

This can be solved by metaprogramming through the Reflection API and generics.
Factory implementations for this have already been written and are called IoC containers. The principles of working with them can be found here . Or anywhere on the internet. Popular implementations: Castle Windsor, Unity, Ninject, etc.
The bottom line is that the container can first be configured:

interface IMyEntityRepository {...}
class MySqlEntityRpository: IMyEntityRepository  {...}
...
config.Bind<IMyEntityRepository>().ImplementsBy<MySqlEntityRpository>();

And then call:
In addition to the obvious advantages, IoC containers can:

1. collect a complete dependency graph in its table (A depends on B, B on C, etc., introducing the necessary implementations automatically, i.e. we can create A right away)
   
2. be configured without recompilation (autowiring (automatic search for implementations of given interfaces) or according to information from the config file, like XML).
   

Answer 2

It was necessary to use ORM - this is already Generic Repository and Unit Of Work.
For example Entity Framework. Using EF Code First and different Entity Configurations (in general, one is enough), you can easily solve your problem of switching between databases by changing the connection string and DB provider.
And there will be no problems with JOIN.
Don't like EF - use Dapper.net, lightweight and fast ORM, whole stackoverflow runs on it

Answer 3

Containers are a very good thing, but in order to use them you need to know and apply agile development techniques. Just having a container doesn't rid your code of terrible dependencies. So read about dependency inversion first. There is even a book in Russian: "Dependency Inversion in .Net".

Answer 4

3. The canonical repository should only be able to do CRUD. Look towards CQRS. In this matter, just Queries will do.
I also recommend reading about Domain Driven Design (DDD), for example, Eric Evans.