Exception message: System.InvalidOperationException: Invalid operation. The connection is closed
Stack trace:
System.InvalidOperationException: Invalid operation. The connection is closed.
at System.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__122_0(Task`1 result)
at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
--- End of stack trace from previous location where exception was thrown ---
at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot)
--- End of stack trace from previous location where exception was thrown ---
at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteAsync(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary`2 parameterValues, CancellationToken cancellationToken)
2019-02-19 13:02:44.942 +03:00 [Error] An exception occurred while iterating over the results of a query for context type '"Api.Counter.Infrastructure.CounterContext"'."
""System.InvalidOperationException: Invalid operation. The connection is closed.
at System.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__122_0(Task`1 result)
at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
--- End of stack trace from previous location where exception was thrown ---
at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot)
--- End of stack trace from previous location where exception was thrown ---
at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteAsync(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary`2 parameterValues, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Query.Internal.AsyncQueryingEnumerable`1.AsyncEnumerator.BufferlessMoveNext(DbContext _, Boolean buffer, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy.ExecuteImplementationAsync[TState,TResult](Func`4 operation, Func`4 verifySucceeded, TState state, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy.ExecuteImplementationAsync[TState,TResult](Func`4 operation, Func`4 verifySucceeded, TState state, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Query.Internal.AsyncQueryingEnumerable`1.AsyncEnumerator.MoveNext(CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Query.Internal.AsyncLinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext(CancellationToken cancellationToken)"
System.InvalidOperationException: Invalid operation. The connection is closed.
at System.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__122_0(Task`1 result)
at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
--- End of stack trace from previous location where exception was thrown ---
at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot)
--- End of stack trace from previous location where exception was thrown ---
at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteAsync(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary`2 parameterValues, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Query.Internal.AsyncQueryingEnumerable`1.AsyncEnumerator.BufferlessMoveNext(DbContext _, Boolean buffer, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy.ExecuteImplementationAsync[TState,TResult](Func`4 operation, Func`4 verifySucceeded, TState state, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy.ExecuteImplementationAsync[TState,TResult](Func`4 operation, Func`4 verifySucceeded, TState state, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Query.Internal.AsyncQueryingEnumerable`1.AsyncEnumerator.MoveNext(CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Query.Internal.AsyncLinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext(CancellationToken cancellationToken)
Steps to reproduce
Finished with exeption:
public async Task<IEnumerable<CounterIdentifierBase>> GetCountersByGuid(IEnumerable<Guid> guids)
return await _counterContext.Counters
.Where(x => guids.Contains(x.Guid))
.Select(x => new CounterIdentifierBase { CounterID = x.ID, CounterGuid = x.Guid})
.ToListAsync();
Finished success:
public IEnumerable<CounterIdentifierBase> GetCountersByGuid(IEnumerable<Guid> guids)
return _counterContext.Counters
.Where(x => guids.Contains(x.Guid))
.Select(x => new CounterIdentifierBase { CounterID = x.ID, CounterGuid = x.Guid})
.ToList();
Further technical details
EF Core version: 2.1.4 ,2.2.2
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: Centos 7
IDE: Visual Studio 2017 15.9.6
Note for triage: I was able to reproduce (see below) but also note that the same code fails for non-async with a sufficiently large number of elements. Also, it appears to be a real command timeout, but increasing the timeout eventually leads to this:
Unhandled Exception: System.Data.SqlClient.SqlException: The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.
at System.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__122_0(Task`1 result)
at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
--- End of stack trace from previous location where exception was thrown ---
at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot)
--- End of stack trace from previous location where exception was thrown ---
at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteAsync(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary`2 parameterValues, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Query.Internal.AsyncQueryingEnumerable`1.AsyncEnumerator.BufferlessMoveNext(DbContext _, Boolean buffer, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Query.Internal.AsyncQueryingEnumerable`1.AsyncEnumerator.MoveNext(CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Query.Internal.AsyncLinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext(CancellationToken cancellationToken)
at System.Linq.AsyncEnumerable.Aggregate_[TSource,TAccumulate,TResult](IAsyncEnumerable`1 source, TAccumulate seed, Func`3 accumulator, Func`2 resultSelector, CancellationToken cancellationToken) in D:\a\1\s\Ix.NET\Source\System.Interactive.Async\Aggregate.cs:line 120
at BloggingContext.GetCountersByGuid(IEnumerable`1 guids) in C:\Stuff\TwoTwoPreview\TwoTwoPreview\Program.cs:line 53
at Program.Main() in C:\Stuff\TwoTwoPreview\TwoTwoPreview\Program.cs:line 89
at Program.<Main>()
Code:
public class CounterIdentifierBase
public int CounterID { get; set; }
public Guid CounterGuid { get; set; }
public class Counter
public int ID { get; set; }
public Guid Guid { get; set; }
public class BloggingContext : DbContext
private static readonly LoggerFactory Logger
= new LoggerFactory(new[] { new ConsoleLoggerProvider((_, __) => true, true) });
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
optionsBuilder
//.UseLoggerFactory(Logger)
.UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=Test;ConnectRetryCount=0",
b => b.CommandTimeout(120));
public DbSet<Counter> Counters { get; set; }
public async Task<IEnumerable<CounterIdentifierBase>> GetCountersByGuid(IEnumerable<Guid> guids)
return await Counters
.Where(x => guids.Contains(x.Guid))
.Select(x => new CounterIdentifierBase { CounterID = x.ID, CounterGuid = x.Guid })
.ToListAsync();
public class Program
public static async Task Main()
var guids = new List<Guid>();
for (var i = 0; i < 100000; i++)
guids.Add(Guid.NewGuid());
using (var context = new BloggingContext())
context.Database.EnsureDeleted();
context.Database.EnsureCreated();
for (var i = 0; i < 200000; i++)
context.Add(new Counter
Guid = i % 2 == 0 ? guids[i / 2] : Guid.NewGuid()
});
context.SaveChanges();
using (var context = new BloggingContext())
var counters = (await context.GetCountersByGuid(guids)).ToList();
Logs:
dbug: Microsoft.EntityFrameworkCore.Query[10101]
=> Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor
Compiling query model:
'from Counter x in DbSet<Counter>
where
(from Guid <generated>_1 in __guids_0
select [<generated>_1]).Contains([x].Guid)
select new CounterIdentifierBase{
CounterID = [x].ID,
CounterGuid = [x].Guid
dbug: Microsoft.EntityFrameworkCore.Query[10104]
=> Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor
Optimized query model:
'from Counter x in DbSet<Counter>
where
(from Guid <generated>_1 in __guids_0
select [<generated>_1]).Contains([x].Guid)
select new CounterIdentifierBase{
CounterID = [x].ID,
CounterGuid = [x].Guid
dbug: Microsoft.EntityFrameworkCore.Query[10107]
=> Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor
(QueryContext queryContext) => IAsyncEnumerable<CounterIdentifierBase> _InterceptExceptions(
|__ source: IAsyncEnumerable<CounterIdentifierBase> _ShapedQuery(
| |__ queryContext: queryContext,
| |__ shaperCommandContext: SelectExpression:
| | SELECT [x].[ID] AS [CounterID], [x].[Guid] AS [CounterGuid]
| | FROM [Counters] AS [x]
| | WHERE 0 = 1,
| |__ shaper: (QueryContext queryContext | ValueBuffer x) => new CounterIdentifierBase{
| CounterID = int TryReadValue(x, 0, Counter.ID),
| CounterGuid = Guid TryReadValue(x, 1, Counter.Guid)
| }
|__ ),
|__ contextType: BloggingContext,
|__ logger: DiagnosticsLogger<Query>,
|__ queryContext: Unhandled parameter: queryContext)
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20000]
Opening connection to database 'Test' on server '(localdb)\mssqllocaldb'.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20001]
Opened connection to database 'Test' on server '(localdb)\mssqllocaldb'.
dbug: Microsoft.EntityFrameworkCore.Database.Command[20100]
Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='120']
SELECT [x].[ID] AS [CounterID], [x].[Guid] AS [CounterGuid]
FROM [Counters] AS [x]
WHERE [x].[Guid] IN ('ed4e8334-9718-44f6-9e71-5ab0d8cd5e29', '137453c0-266c-416e-85b2-a9e7fb599b63', ......
Excellent!
Can you add Index on Guid in Counter and check again?
In my case ToList executed less then 2 seconds and this cannot be a timeout connection.
But ToListAsync trow an error.
ToListAsync on a query with thousands of entries inside a "where contains" statement requires MARS=True (inside Docker container)
#23560
