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,