AutoQuery not using changed IDbConnection

ServiceStack Auto Query
1

I’m using the following to set the Db connection at runtime:

RegisterTypedRequestFilter<IForEnvironment>((req, res, requestDto) =>
{
    var db = GetDbFromHost(req);
    var connectionString = $"Server=localhost;User Id=postgres;Password=admin;Database={db};Pooling=true;MinPoolSize=0;MaxPoolSize=200";
    req.Items[Keywords.DbInfo] = new ConnectionInfo() { ConnectionString = connectionString };
});

I have an AutoQuery defined as:

public object Get(Files request)
{
    try
    {
        var q = AutoQuery.CreateQuery(request, Request);
        q.OrderBy(x => x.Name);
        return AutoQuery.Execute(request, q);
    }
    catch (Exception ex)
    {

        throw;
    }
}

Although I’m not sure where to see what connection string AutoQuery is using, it comes back with

“42P01: relation "file" does not exist”

Which leads me to believe its not using the connection set by the request filter as the “file” table does in fact exist in the tenant database, but not the default database.

What am I missing here?

2

So I just saw this post:

I’m pretty sure I’m doing everything this post is saying, and yet, AutoQuery doesn’t seem to be changing the connection.

3

The Service should call GetDbConnection() in your AppHost to resolve its db connection, override it to see what DB connection it’s returning, also verify that IRequest.Items[Keywords.DbInfo] is populated.

4

Seems like some strange behavior and possibly a bug. I overrided GetDbConnection() and it appears to be getting called twice, one at

var q = AutoQuery.CreateQuery(request, Request);

and another at

return AutoQuery.Execute(request, q);

The first one shows the correct connection string after RegisterTypedRequestFilter executes. Then on the second one, where it actually executes the query, it goes back to the default connection string.

So it seems that between the first and second line of code, it loses the connection set from the request filter. I also don’t understand why it would need to call GetDbConnection twice. Wouldn’t it just need to be called from AutoQuery.Execute()?

I’ll try to repro this in a clean project to make sure it’s not something I introduced.

5

It does that because you’re not passing in the DB connection & it needs to know which DB it is creating for. Note these are pooled connections and CreateQuery only uses it to access the dialect provider in order to be able to construct the correct query, it doesn’t actually use it to access the RDBMS.

The issue is due to not passing in the IRequest in the Execute of your Custom AutoQuery Implementation so it’s unable to unable to resolve the overridden connection for that request:

public class MyQueryServices : Service
{
    public IAutoQueryDb AutoQuery { get; set; }

    //Override with custom implementation
    public object Any(FindMovies query)
    {
        var q = AutoQuery.CreateQuery(query, Request);
        return AutoQuery.Execute(query, q, Request);
    }
}

If you want to avoid the double lookup you can resolve & pass in the db connection yourself:

public object Any(FindMovies query)
{
    using var db = AutoQuery.GetDb<Movie>(Request);
    var q = AutoQuery.CreateQuery(query, Request, db);
    return AutoQuery.Execute(query, q, Request, db);
}

Note this just reduces it to only 1 lookup, it doesn’t change how many times the connection is used.

6

Thank you. I believe that resolved the issue.

However, I’m curious about this statement though:

Note these are pooled connections and CreateQuery only uses it to access the dialect provider in order to be able to construct the correct query, it doesn’t actually use it to access the RDBMS.

It seems that CreateQuery is actually checking for the existence of the database since it errors out if the database doesn’t actually exist. Doesn’t that mean it’s hitting the database? Is that necessary?

7

GetDbConnection() returns an Open connection, if the pooled connection was already opened it would be a noop.

8

Gotcha.

Passing in Db to the AutoQuery calls solved my problem. Thanks!

1 Like