Entity Framework Core - Not thread safe - ServiceLifetime.Transient

Question

It appears that my database context is not thread safe, transient, even tho i specified:

ServiceLifetime.Transient 

in the startup configuration for the database.

Startup.cs

services.AddEntityFrameworkSqlServer().AddDbContext<DatabaseContext>((serviceProvider, options) => options.UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking).UseSqlServer(connectionString).UseInternalServiceProvider(serviceProvider), ServiceLifetime.Transient);

Api

[HttpPost("GetOrganisations")] //Yes post, dont harass me :)
public async Task<IActionResult> GetOrganisations([FromBody] GetOrganisationsModel model)
{
    Task<IEnumerable<OrganisationModel>> organisations = _organisationService.GetOrganisations(model?.Id, model?.StatusIds);

    Task<int> organisationTotalCount = _organisationService.GetOrganisationCount();

    await Task.WhenAll(organisations, organisationTotalCount);

    return Ok(new OrganisationViewModel
    {
        Organisations = await organisations,
        OrganisationTotalCount = await organisationTotalCount
    });
}

Repo

public class OrganisationRepository : IOrganisationRepository
{
    private readonly DatabaseContext _database;

    public OrganisationRepository(DatabaseContext database)
    {
        _database = database;
    }

    public async Task<List<OrganisationEntity>> GetOrganisations(int? organisationId, List<int> statusIds)
    {
        IQueryable<OrganisationEntity> organisations = Database.Organisation.Include(o => o.Status).OrderByDescending(d => d.Created).AsQueryable();

        if (organisationId != null)
        {
            organisations = organisations.Where(o => o.Id == organisationId);
        }

        if (statusIds != null && statusIds.Count > 0)
        {
            organisations = organisations.Where(o => statusIds.Contains(o.StatusId));
        }

        return await organisations.ToListAsync();
    }

    public async Task<int> GetOrganisationCount()
    {
        return await Database.Organisation.CountAsync();
    }
}

Exception

System.InvalidOperationException: The connection was not closed. The connection's current state is connecting.

The problem here is that I call GetOrganisations() and GetOrganisationCount() asynchronous and for some reason, (I think), the same database contex is used for both calls, (different threads).. I Thought that by adding the option:

ServiceLifetime.Transient 

It would solve my problem, (because default is scoped), but no..

However if I add using statements..

public async Task<List<OrganisationEntity>> GetOrganisations(int? organisationId, List<int> statusIds)
{
    var optionsBuilder = new DbContextOptionsBuilder<DatabaseContext>();
    optionsBuilder.UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking)
        .UseSqlServer(
            "Data Source=.;Initial Catalog=MYDATABASE;Integrated Security=True;Connect Timeout=30;");

    using (var db = new DatabaseContext(optionsBuilder.Options, null))
    {
        IQueryable<OrganisationEntity> organisations =
            db.Organisation.Include(o => o.Status).OrderByDescending(d => d.Created).AsQueryable();

        if (organisationId != null)
        {
            organisations = organisations.Where(o => o.Id == organisationId);
        }

        if (statusIds != null && statusIds.Count > 0)
        {
            organisations = organisations.Where(o => statusIds.Contains(o.StatusId));
        }

        return await organisations.ToListAsync();
    }
}

public async Task<int> GetOrganisationCount()
{
    var optionsBuilder = new DbContextOptionsBuilder<DatabaseContext>();
    optionsBuilder.UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking)
        .UseSqlServer(
            "Data Source=.;Initial Catalog=MYDATABASE;Integrated Security=True;Connect Timeout=30;");

    using (var db = new DatabaseContext(optionsBuilder.Options, null))
    {
        return await db.Organisation.CountAsync();
    }
}

Everything works!

Why is the dependency injection not working as it should?

Am I completely in the wrong here? Am I forced to use using statements for all my tasks if I want transient context?

ServiceLifetime.Transient bugged?!

Edit:

Stacktrade, (some info masked)

"at System.Data.ProviderBase.DbConnectionClosedConnecting.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions)\r\n
at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource1 retry)\r\n at System.Data.SqlClient.SqlConnection.OpenAsync(CancellationToken cancellationToken)\r\n--- End of stack trace from previous location where exception was thrown ---\r\n at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\r\n
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.d__31.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\r\n
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n at Microsoft.EntityFrameworkCore.Query.Internal.AsyncQueryingEnumerable.AsyncEnumerator.d__9.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\r\n
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n at Microsoft.EntityFrameworkCore.Storage.Internal.SqlServerExecutionStrategy.d__62.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\r\n
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n at Microsoft.EntityFrameworkCore.Query.Internal.AsyncQueryingEnumerable.AsyncEnumerator.<MoveNext>d__8.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\r\n
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n at Microsoft.EntityFrameworkCore.Query.AsyncQueryMethodProvider.<GetResult>d__161.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\r\n
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n at Microsoft.EntityFrameworkCore.Query.Internal.TaskResultAsyncEnumerable1.Enumerator.<MoveNext>d__3.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\r\n
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n at Microsoft.EntityFrameworkCore.Query.Internal.AsyncLinqOperatorProvider.ExceptionInterceptor1.EnumeratorExceptionInterceptor.d__5.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\r\n
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n at System.Runtime.CompilerServices.TaskAwaiter1.GetResult()\r\n at XXXXXXX.Data.Repositories.Organisation.OrganisationRepository.<GetOrganisationCount>d__8.MoveNext() in C:\\Users\\XXXXXXX\\Documents\\Visual Studio 2017\\Projects\\XXXXXXX\\XXXXXXX.Data\\Repositories\\Organisation\\OrganisationRepository.cs:line 65\r\n--- End of stack trace from previous location where exception was thrown ---\r\n at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\r\n
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n at System.Runtime.CompilerServices.TaskAwaiter1.GetResult()\r\n at XXXXXXX.Api.Services.Organisation.OrganisationService.d__4.MoveNext() in C:\Users\XXXXXXX\Documents\Visual Studio 2017\Projects\XXXXXXX\XXXXXXX.Api\Services\Organisation\OrganisationService.cs:line 31\r\n--- End of stack trace from previous location where exception was thrown ---\r\n at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\r\n
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n at System.Runtime.CompilerServices.TaskAwaiter.GetResult()\r\n at XXXXXXX.Api.Controllers.Administration.OrganisationController.d__5.MoveNext() in C:\Users\XXXXXXX\Documents\Visual Studio 2017\Projects\XXXXXXX\XXXXXXX.Api\Controllers\Administration\OrganisationController.cs:line 51"

Answer 1

Also note that in Microsoft Dependency Injection, transient services will not be disposed until the scope ends.

There is a clean way around this which is to use a WeakReference as the Transient instead of TService. The user of the WeakReference can then take responsibility for Dispose.

Answer 2

ServiceLifetime.Transient bugged?!

It is not bug. As we know DbContext is not thread safe, both methods calls use same OrganisationService instance which itself was created using same instance of DatabaseContext.

Task<IEnumerable<OrganisationModel>> organisations = 
    _organisationService.GetOrganisations(model?.Id, model?.StatusIds);

Task<int> organisationTotalCount = _organisationService.GetOrganisationCount();

It is a limitation of Entity Framework' DbContext in thread switching environment. Therefore, we use the following traditional approach -

[HttpPost("GetOrganisations")]
public async Task<IActionResult> GetOrganisations([FromBody] GetOrganisationsModel model)
{
    var organisations = 
        await  _organisationService.GetOrganisations(model?.Id, model?.StatusIds);

    var organisationTotalCount = await _organisationService.GetOrganisationCount();

    return Ok(new OrganisationViewModel
    {
        Organisations = organisations,
        OrganisationTotalCount = organisationTotalCount
    });
}

Downside of newing up inside Repository is they become tightly couple, and you cannot Unit Test the repository like this. If I have to choose between Task.WhenAll vs Unit Test in that particular scenario, I'll choose Unit Test.

If you see yourself using Task.WhenAll a lot, you might want look at Dapper ORM which can be used together with EF.