.toList() 返回一个异常

Question

我有这个针对MySQL数据库运行的方法，结果是所有代码的异常。

然后尝试了使用 take(10( 方法查询，它工作正常，但还无法弄清楚原因。

我正在迭代的表有数百万行。 有人可以解释发生了什么以及为什么吗？ 谢谢

public IHttpActionResult retentionRate() {   
DateTime today = DateTime.Today;
DateTime firstOfThisMonth = new DateTime(today.Year, today.Month, 1);
DateTime  LastYearDate = DateTime.Today.AddDays(-365);
var activeInDateRange = (from mp in FitnessDbo.memberproductinfoes
where mp.mepi_expirydate >= LastYearDate &&
mp.mepi_activationdate < today
select new
{
mepi_memberproductinfoid = mp.mepi_memberproductinfoid,
mepi_memberid = mp.mepi_memberid,
mepi_activationdate = (mp.mepi_activationdate < LastYearDate ? LastYearDate : mp.mepi_activationdate),
mepi_expirydate = (mp.mepi_expirydate > today ? today : mp.mepi_expirydate),
mepi_prodtype = mp.mepi_prodtype,
mepi_producttypeinfo = mp.mepi_producttypeinfo,
mepi_memberproducttype = mp.mepi_memberproducttype,
mepi_status = mp.mepi_status
}).ToList();
return Ok(activeInDateRange);
}

使用 Take(( 方法：

public IHttpActionResult retentionRate() {
DateTime today = DateTime.Today;
DateTime firstOfThisMonth = new DateTime(today.Year, today.Month, 1);
DateTime  LastYearDate = DateTime.Today.AddDays(-365);


var activeInDateRange = (from mp in FitnessDbo.memberproductinfoes
where mp.mepi_expirydate >= LastYearDate &&
mp.mepi_activationdate < today
select new
{
mepi_memberproductinfoid = mp.mepi_memberproductinfoid,
mepi_memberid = mp.mepi_memberid,
mepi_activationdate = (mp.mepi_activationdate < LastYearDate ? LastYearDate : mp.mepi_activationdate),
mepi_expirydate = (mp.mepi_expirydate.Value > today? today : mp.mepi_expirydate),
mepi_prodtype = mp.mepi_prodtype,
mepi_producttypeinfo = mp.mepi_producttypeinfo,
mepi_memberproducttype = mp.mepi_memberproducttype,
mepi_status = mp.mepi_status
}).Take(10);
}

我收到的异常

{"message":"An error has occurred.",
"exceptionMessage":"Calling 'Read' when the data reader is closed is not a valid operation.",
"exceptionType":"System.Data.Entity.Core.EntityCommandExecutionException",
"stackTrace":
"   at System.Data.Entity.Core.Common.Internal.Materialization.Shaper`1.HandleReaderException(Exception e)rn
at System.Data.Entity.Core.Common.Internal.Materialization.Shaper`1.StoreRead()rn   at System.Data.Entity.Core.Common.Internal.Materialization.Shaper`1.SimpleEnumerator.MoveNext()rn
at System.Data.Entity.Internal.LazyEnumerator`1.MoveNext()rn
at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)rn
at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)rn
at BI_leejam.Controllers.MembersController.retentionRate() in C:\Users\ahijazi\source\repos\BI_leejam\BI_leejam\Controllers\MembersController.cs:line 123rn
at lambda_method(Closure , Object , Object[] )rn
at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ActionExecutor.<>c__DisplayClass6_2.<GetExecutor>b__2(Object instance, Object[] methodParameters)rn
at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ActionExecutor.Execute(Object instance, Object[] arguments)rn
at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ExecuteAsync(HttpControllerContext controllerContext, IDictionary`2 arguments, CancellationToken cancellationToken)rn
--- End of stack trace from previous location where exception was thrown ---rn
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)rn
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)rn
at System.Web.Http.Controllers.ApiControllerActionInvoker.<InvokeActionAsyncCore>d__1.MoveNext()rn
--- End of stack trace from previous location where exception was thrown ---rn
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)rn
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)rn
at System.Web.Http.Controllers.ActionFilterResult.<ExecuteAsync>d__5.MoveNext()rn
--- End of stack trace from previous location where exception was thrown ---rn
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)rn
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)rn
at System.Web.Http.Dispatcher.HttpControllerDispatcher.<SendAsync>d__15.MoveNext()",
"innerException":{"message":"An error has occurred.",
"exceptionMessage":"Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.",
"exceptionType":"MySql.Data.MySqlClient.MySqlException",
"stackTrace":
"   at MySql.Data.MySqlClient.Interceptors.ExceptionInterceptor.Throw(Exception exception)rn
at MySql.Data.MySqlClient.MySqlConnection.Throw(Exception ex)rn   at MySql.Data.MySqlClient.MySqlConnection.HandleTimeoutOrThreadAbort(Exception ex)rn
at MySql.Data.MySqlClient.MySqlDataReader.Read()rn
at MySql.Data.EntityFramework.EFMySqlDataReader.Read()rn
at System.Data.Entity.Core.Common.Internal.Materialization.Shaper`1.StoreRead()",
"innerException":{"message":"An error has occurred.",
"exceptionMessage":"Timeout in IO operation",
"exceptionType":"System.TimeoutException",
"stackTrace":
"   at MySql.Data.MySqlClient.TimedStream.StopTimer()rn
at MySql.Data.MySqlClient.TimedStream.Read(Byte[] buffer, Int32 offset, Int32 count)rn
at MySql.Data.MySqlClient.MySqlStream.ReadFully(Stream stream, Byte[] buffer, Int32 offset, Int32 count)rn
at MySql.Data.MySqlClient.MySqlStream.LoadPacket()rn
at MySql.Data.MySqlClient.MySqlStream.ReadPacket()rn
at MySql.Data.MySqlClient.NativeDriver.FetchDataRow(Int32 statementId, Int32 columns)rn
at MySql.Data.MySqlClient.Driver.FetchDataRow(Int32 statementId, Int32 columns)rn
at MySql.Data.MySqlClient.ResultSet.GetNextRow()rn
at MySql.Data.MySqlClient.ResultSet.NextRow(CommandBehavior behavior)rn
at MySql.Data.MySqlClient.MySqlDataReader.Read()"}}}

Answer 1

尝试将其添加到控制器：

protected override void Dispose(bool disposing)
{
if (disposing)
{
if (this.FitnessDbo != null) { this.FitnessDbo.Dispose(); }
}
base.Dispose(disposing);
}

编辑一些解释：
从通过评论的问题来看，听起来即使控制器在请求结束时被释放，上下文使用的连接也被重用，但不处于可用状态。通过在释放控制器时显式释放上下文，应正确关闭上下文和连接，确保我们在下一个请求中获得新的连接。