How do you inject multiple QueryFactory?

Question

The SqlKata documentation lists the following code:

.NET 6

builder.Services.AddTransient<QueryFactory>((e) =>
{
    var connection = new SqlConnection(CONST.KenticoConnectionString);
    var compiler = new SqlServerCompiler();

    return new QueryFactory(connection, compiler);
});

HomeController.cs

using SqlKata;
using SqlKata.Execution;

public class HomeController {

    private readonly QueryFactory db;

    public HomeController(QueryFactory db) {

        this.db = db;

    }

    public IActionResult Index() {

        // Here books is of type `IEnumerable<dynamic>`
        var books = db.Query("Books").Where("IsPublished", true).Get();

        // or `IEnumerable<Book>` if using the Generic overload
        var books = db.Query("Books").Where("IsPublished", true).Get<Book>();

        return Ok(books);

    }

}

How would I go about using multiple QueryFactory? I have two different databases that I need to query which have separate connection strings.

I can set up multiple QueryFactor within Program / Startup, but I have no idea how to inject the correct one.

Answer 1

You can create a factory for QueryFactory that will dynamically return you an instance with dynamically injected ConnectionString:

public class QueryFactoryFactory : IQueryFactoryFactory
{
    public QueryFactory CreateQueryFactory(string connectionString)
    {
        var connection = new SqlConnection(connectionString);

        return new QueryFactory(connection, new SqlServerCompiler());
    }
}

public interface IQueryFactoryFactory
{
    QueryFactory CreateQueryFactory(string connectionString);
}

It should be registered in your ServiceCollection:

builder.Services.AddScoped<IQueryFactoryFactory, QueryFactoryFactory>();

After that, you can inject your factory to the controller and get the needed QueryFactory based on the connection string provided you can get from IConfiguration (or in any other way):

public class HomeController
{
    private readonly IConfiguration _configuration;
    private readonly IQueryFactoryFactory _factory;

    public HomeController(IConfiguration configuration, IQueryFactoryFactory factory)
    {
        _factory = factory;
        _configuration = configuration;
    }

    public IActionResult Index()
    {
        // use the needed connectionString name instead of 'first'
        var books = _factory.CreateQueryFactory(_configuration.GetConnectionString("first"))
            .Query("Books")
            .Where("IsPublished", true)
            .Get();

        return Ok(books);
    }
}

It is also possible to create the separate derived class for each of your connection strings and register them in IoC, however, this is a bad approach, to avoid which the factory pattern was invented as well:

public class ConcreteQueryFactory1 : QueryFactory
{
    public ConcreteQueryFactory1()
        : base(new SqlConnection("connectionString1"), new SqlServerCompiler())
    {

    }
}

public class ConcreteQueryFactory2 : QueryFactory
{
    public CustomQueryFactory2(IDbConnection connection, Compiler compiler, int timeout = 30)
        : base(connection, compiler, timeout)
    {

    }
}

The main benefits of factory over the second approach are:

1. You can change the way objects are created without affecting the rest of your code - flexibility;
2. The factory provides a single point for the creation of objects - encapsulation;
3. By using a factory, you separate the concerns of object creation from the concerns of object usage - responsibility separation;
4. You can easily create mock objects for testing purposes - testability.