Calling AutoQuery Services through the ServiceController

Hi,

Trying to call Autoquery services internally, when using the ServiceController the DB connection seems to not be disposed properly and we are getting a lot of errors related to using a closed connection:
“System.InvalidOperationException: ExecuteReader requires an open and available Connection. The connection’s current state is closed.”

It is more obvious when the load increases so it might have to do with concurrent connections. Following is an attempt at a repro that highlights the problem. I’m using the ServiceController here since I ultimately want my Autoquery service to have a custom implementation. When invoking regular services that are not leveraging AutoQuery everything works as expected, and I did test the same code with a plain service implementation that just does a Db.Select() and have no errors.

// Assets DTOs
[Route("/api/assets", "GET")]
public class AssetsQuery : QueryDb<Asset> { }

[Route("/api/assets/plain", "GET")]
public class AssetsQueryPlain : IReturn<List<Asset>> { }

// Invoker DTO
[Route("/api/invoker", "GET")]
public class InvokerQuery : IReturn<object> 
{ 
    public string Path { get; set; }
}

// Invoker Service
public class InvokerService : Service
{
    public object Get(InvokerQuery query)
    {
        Parallel.ForEach(Enumerable.Range(0, 20), i =>
        {
            Invoke(query.Path);
        });

        return Invoke(query.Path);
    }

    object Invoke(string url)
    {
        var pathAndQuery = url.SplitOnFirst('?');
        var req = new BasicHttpRequest()
        {
            Verb = "GET",
            PathInfo = pathAndQuery[0],
            QueryString = pathAndQuery.Length > 1 ? new NameValueCollection(HttpUtility.ParseQueryString(pathAndQuery[1])) : new NameValueCollection(),
        };

        strinmatted text`g contentType;
        string pathInfo = RestHandler.GetSanitizedPathInfo(req.PathInfo, out contentType);
        var restPath = HostContext.ServiceController.GetRestPathForRequest(req.Verb, pathInfo, req as IHttpRequest);
        req.SetRoute(restPath);
        req.PathInfo = restPath.Path;
        req.OperationName = restPath.RequestType.GetOperationName();
        req.Dto = RestHandler.CreateRequest(req, restPath, req.GetRequestParams(), req.Dto);
        return HostContext.ServiceController.Execute(req.Dto, req, true);
    }
}

Note that the parallel loop is here simply to simulate 20 simultaneous connections to highlight the problem. Also my connection string is setting the Max Pool Size to 200.

Can you provide more details, e.g. what’s the full Exception StackTrace, the missing Asset Type, the implementation of your AssetsQueryPlain Service, what RDBMS are you using and anything else I need to try reproduce the issue locally.

Note: don’t use IReturn<object>, it needs to be of a specific type that the response should be deserialized into, e.g:

public class InvokerQuery : IReturn<QueryResponse<Asset>> {}

I’ve stubbed out the missing classes and have tried calling this Test Service like:

var response = client.Get(new InvokerQuery {
    Path = "/api/assets"
});
response.PrintDump();

And it’s returning the expected AutoQuery Response so I guess I don’t understand what the issue is or how to repro it.

It is true that I created that repro within the scope of our project, and we have several filters that might use the DB connection. I’m in the proccess of reviewing these.

With that said, I also created a project from scracth to provide you with a clean repro. While I wasn’t able to get it to crash as it does within our project, I made some interesting observations. But first here is what you requested:

The StackTrace of the error I’m tracking

System.InvalidOperationException: ExecuteReader requires an open and available Connection. The connection’s current state is closed.
at System.Data.SqlClient.SqlCommand.ValidateCommand(String method, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at ServiceStack.OrmLite.OrmLiteResultsFilterExtensions.ExprConvertToList[T](IDbCommand dbCmd, String sql, IEnumerable1 sqlParams, HashSet1 onlyFields) in C:\BuildAgent\work\27e4cc16641be8c0\src\ServiceStack.OrmLite\OrmLiteResultsFilterExtensions.cs:line 150 at ServiceStack.OrmLite.Support.LoadList2…ctor(IDbCommand dbCmd, SqlExpression1 q) in C:\BuildAgent\work\27e4cc16641be8c0\src\ServiceStack.OrmLite\Support\LoadList.cs:line 39 at ServiceStack.OrmLite.OrmLiteReadCommandExtensions.LoadListWithReferences[Into,From](IDbCommand dbCmd, SqlExpression1 expr, IEnumerable1 include) in C:\BuildAgent\work\27e4cc16641be8c0\src\ServiceStack.OrmLite\OrmLiteReadCommandExtensions.cs:line 957 at ServiceStack.OrmLite.OrmLiteExecFilter.Exec[T](IDbConnection dbConn, Func2 filter) in C:\BuildAgent\work\27e4cc16641be8c0\src\ServiceStack.OrmLite\OrmLiteExecFilter.cs:line 70
at ServiceStack.TypedQuery`2.Execute[Into](IDbConnection db, ISqlExpression query) in C:\BuildAgent\work\3481147c480f4a2f\src\ServiceStack.Server\AutoQueryFeature.cs:line 1096

Here is the code

// Asset Type

public class Asset
{
    public Guid Id { get; set; }
    public string ContentType { get; set; }
    public string SourceUrl { get; set; }
    public string Name { get; set; }
    public string FilePath { get; set; }
}

// Asset service

[Route("/api/assets", "GET")]
public class AssetsQuery : QueryDb<Asset> { }

[Route("/api/assets/custom", "GET")]
public class AssetsQueryCustom : QueryDb<Asset> { }

[Route("/api/assets/plain", "GET")]
public class AssetsQueryPlain : IReturn<List<Asset>> { }

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

    public List<Asset> Get(AssetsQueryPlain query)
    {
        return Db.Select<Asset>();
    }

    public QueryResponse<Asset> Get(AssetsQueryCustom query)
    {
        var q = AutoQuery.CreateQuery(query, base.Request);
        return AutoQuery.Execute(query, q);
    }
}

// Invoker Service

[Route("/api/invoker", "GET")]
public class InvokerQuery : IReturn<object>
{
    public string Path { get; set; }
}

[Route("/api/invoker/test", "GET")]
public class InvokerQueryTest : IReturn<object> { }

public class InvokerService : Service
{
    public object Get(InvokerQuery query)
    {
        Parallel.ForEach(Enumerable.Range(0, 100), i =>
        {
            Invoke(query.Path);
        });

        return Invoke(query.Path);
    }

    public object Get(InvokerQueryTest query)
    {
        using (var service = HostContext.Resolve<AssetService>())
            return service.Get(new AssetsQueryCustom());
    }

    object Invoke(string url)
    {
        var pathAndQuery = url.SplitOnFirst('?');
        var req = new BasicHttpRequest()
        {
            Verb = "GET",
            PathInfo = pathAndQuery[0],
            QueryString = pathAndQuery.Length > 1 ? new NameValueCollection(HttpUtility.ParseQueryString(pathAndQuery[1])) : new NameValueCollection(),
        };

        string contentType;
        string pathInfo = RestHandler.GetSanitizedPathInfo(req.PathInfo, out contentType);
        var restPath = HostContext.ServiceController.GetRestPathForRequest(req.Verb, pathInfo, req as IHttpRequest);
        req.SetRoute(restPath);
        req.PathInfo = restPath.Path;
        req.Dto = RestHandler.CreateRequest(req, restPath, req.GetRequestParams(), req.Dto);
        return HostContext.ServiceController.Execute(req.Dto, req, true);
    }
}

We are using SQL Server. Must point out that tests where conducted on an AWS server. AppHost registering a simple OrmLiteConnectionFactory, with SqlServerOrmLiteDialectProvider.

Regarding your note, I agree but the purpose is ultimately to be able to call internally our different services, hence disregarding the reponse type. Tests and code are targeting just one class for simplification.

So here is what I observed:

#1 Increasing the load to 100 connections, I noted a very wide difference with the above code in performance when running these two different queries:

a/ http://localhost:56500/api/invoker?Path=/api/assets&format=json
b/ http://localhost:56500/api/invoker?Path=/api/assets/plain&format=json

The a/ query while returning the same data (yet wrapped in a QueryResponse) is significantly slower than the b/ query. With 2500 row in my Asset table, a/ takes frequently 40-70 seconds and sometimes times out (90+ seconds) while b/ is almost always completing in less than 15s.

#2 InvokerQueryTest is throwing an ArgumentNullException. Thought I could call a targeted service that way but seems AutoQuery is not avaialblepreventing me from it:

[InvokerQueryTest: 1/27/2020 7:20:33 PM]: [REQUEST: {}] System.ArgumentNullException: Value cannot be null. Parameter name: request at ServiceStack.RequestExtensions.GetRequestParams(IRequest request) at ServiceStack.AutoQueryExtensions.CreateQuery[From](IAutoQueryDb autoQuery, IQueryDb1 model, IRequest request) at Astucemedia.DataPlatform.Test.ServiceStack.Services.AssetService.Get(AssetsQueryCustom query) at Astucemedia.DataPlatform.Test.ServiceStack.Services.InvokerService.Get(InvokerQueryTest query) at lambda_method(Closure , Object , Object ) at ServiceStack.Host.ServiceRunner1.d__15.MoveNext()

I understand that it might not be designed that way to be able to call AutoQuery service internally since most of the examples I see and the posts I read are using the combination AutoQuery.CreateQuery() and AutoQuery.Execute() to achieve it. Unfortunately what I’m trying to achieve here is to call internally any of our services, some are regular services going to the DB directly (like the AssetQueryPlain), some are simple AutoQuery defined with no implementation (like AssetsQuery), and others have a custom implementation over AutoQuery (like AssetsQueryCustom).

I might be missing something here and I will make sure to check out AutoQuery’s code to understand how the magic works,

Matt,

Where’s the clean repro? Can you publish it on GitHub and post the link here please.

Just invited you to the repo. It’s private and located here:

https://github.com/matthieu-astuce/ServiceStack-AutoQuery-Invoke.git

FYI the locking/perf issues should now be resolved from the latest v5.8.1 on MyGet.

Performance indeed seem to be greatly improved! Will conduct more tests and keep you posted if I find anything.

Thank you!

1 Like