Marten: Connections not disposed: Max connections exception

[dapalmi]

I am using the DocumentStore in an ASP.NET Core 2.2 application.
Here the definition in the Startup.cs:

            var store = DocumentStore.For(_ =>
            {
                _.Connection(Configuration.GetConnectionString("PostgresDb"));
                _.DatabaseSchemaName = "search";
                var serializer = new JsonNetSerializer();
                serializer.Customize(c => c.ContractResolver = new ResolvePrivateSetters());
                _.Serializer(serializer);
                _.Events.UseAggregatorLookup(AggregationLookupStrategy.UsePrivateApply);
                _.Schema.Include<SearchMartenRegistry>();
            });
            services.AddSingleton<IDataAccess>(new DataAccess(store));

Then from the API controller I call this method in the DataAccess class:

        public IQueryable<Search> Search(string text)
        {
            using (var session = store.QuerySession())
            {
                return session.Query<Search>().Where(s => s.Name.Contains(text));
            }
        }

And in the controller a few extra things are executed on the IQueryable object (sort, skip, take, etc.).

If I understand the documentation right, the connection is established with the session and likewise disposed with it.

But the DB connections don't seem to be disposed, because after 100 time (default max connection in Postgres db) I get following error:
The connection pool has been exhausted

[mdissel]

your session is disposed when exiting the using clause. Use

public List<Search> Search(string text)
        {
            using (var session = store.QuerySession())
            {
                return session.Query<Search>().Where(s => s.Name.Contains(text)).ToList();
            }
        }

or if you want to the result of the function to be used in memory Linq (not in db):

public IQueryable<Search> Search(string text)
        {
            using (var session = store.QuerySession())
            {
                return session.Query<Search>().Where(s => s.Name.Contains(text)).ToList().AsQueryable();
            }
        }

[dapalmi]

@mdissel That's what I thought too. But evidently it does not dispose the connection when exiting the using clause, because I get the max connections exception.

[mdissel]

Can you update the code with one of my examples and try again?

[oskardudycz]

@dapalmi ➕ for the @mdissel request.

Could you also give full usage scenario? So how are you using Search method?

[dapalmi]

@oskardudycz The search method is called from a api controller method:

        [HttpGet]
        public object Search(string text, DataSourceLoadOptions loadOptions)
        {
            var queryable = _searchDataAccess.Search(text);
            return DataSourceLoader.Load(queryable, loadOptions);
        }

The DataSourceLoader.Load() executes sort, skip, take, etc. on the queryable object.
@mdissel I can confirm that the addition of .ToList().ToQueryable() solved the problem.

So, if I return an IQueryable object the connection will not be disposed. Is that correct?

[mysticmind]

@dapalmi when you call return session.Query<Search>().Where(s => s.Name.Contains(text)), you are returning an instance of type IMartenQueryable<T> which holds a Postgres connection. So wrapping it in a using block does not magically dispose of the connection due to your pattern of usage.

As @mdissel indicated, by using return session.Query<Search>().Where(s => s.Name.Contains(text)).ToList().AsQueryable(), here .ToList() resulted in the execution of the query and the results returned. By calling ToList().AsQueryable(), you are using IQueryable on the returned results.

In summary, your pattern of usage is causing the issue at hand.

[mysticmind]

Closing this. we can re-open if anyone thinks we need to add some documentation around usage patterns.

[kinl99]

@mysticmind Thanks for the explanation, we made the same mistake as @dapalmi. But we'd still like to use the repository pattern. Could you please give some advice on how to implement a repository where the lib of the repository is the only lib that holds a reference to marten but without sacrificing the generic query methods?

Edit: We looked already into specific methods and precompiled queries. Specific methods tend to end up in big pile of different, not resuable methods which are awfull to maintain and precompiled queries force us to use the marten reference in the application layer of the project which we'd rather keep encapsuled through a part of the data layer

[mysticmind]

@kinl99 please take a look at https://github.com/oskardudycz/GoldenEye/tree/master/src/Core/Backend.Core.Marten, this is part of GoldenEye library, possibly you can use the same.

[kinl99]

Thanks @mysticmind!!!
Using dependency injection consequently, like in the example provided, not only resolved the problem but also removed quite some usings from the data layer.

[oskardudycz]

@mysticmind thank you for the recommending GoldenEye 👍 😄
@kinl99 here you can find wrappers for the Marten https://github.com/oskardudycz/GoldenEye/tree/master/src/Core/Backend.Core.Marten/Context, they're called DataContexts but they're in fact following repository pattern.
I'd personally recommend to not do manual disposal of the DocumentSession but let the Dependency Injection container let manage it properly - so eg. use request scope registration for DocumentSession and Singleton for Document Store. Then you shouldn't have such issues.
For the future reference if that doesn't answer your question or we have