I have this method that run against MySQL database the result is an exception with all the code bellow.
Then tried the query with take(10) method and it works fine but could not figure out why yet.
The table that I'm iterating over has a millions of rows. can someone explain whats happening and why ? Thanks
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);
}
With Take() method:
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);
}
Exception I have Received
{"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)\r\n
at System.Data.Entity.Core.Common.Internal.Materialization.Shaper\`1.StoreRead()\r\n at System.Data.Entity.Core.Common.Internal.Materialization.Shaper\`1.SimpleEnumerator.MoveNext()\r\n
at System.Data.Entity.Internal.LazyEnumerator\`1.MoveNext()\r\n
at System.Collections.Generic.List\`1..ctor(IEnumerable\`1 collection)\r\n
at System.Linq.Enumerable.ToList[TSource](IEnumerable\`1 source)\r\n
at BI_leejam.Controllers.MembersController.retentionRate() in C:\\Users\\ahijazi\\source\\repos\\BI_leejam\\BI_leejam\\Controllers\\MembersController.cs:line 123\r\n
at lambda_method(Closure , Object , Object[] )\r\n
at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ActionExecutor.<>c__DisplayClass6_2.<GetExecutor>b__2(Object instance, Object[] methodParameters)\r\n
at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ActionExecutor.Execute(Object instance, Object[] arguments)\r\n
at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ExecuteAsync(HttpControllerContext controllerContext, IDictionary\`2 arguments, CancellationToken cancellationToken)\r\n
--- End of stack trace from previous location where exception was thrown ---\r\n
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)\r\n
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n
at System.Web.Http.Controllers.ApiControllerActionInvoker.<InvokeActionAsyncCore>d__1.MoveNext()\r\n
--- End of stack trace from previous location where exception was thrown ---\r\n
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)\r\n
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n
at System.Web.Http.Controllers.ActionFilterResult.<ExecuteAsync>d__5.MoveNext()\r\n
--- End of stack trace from previous location where exception was thrown ---\r\n
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)\r\n
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n
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)\r\n
at MySql.Data.MySqlClient.MySqlConnection.Throw(Exception ex)\r\n at MySql.Data.MySqlClient.MySqlConnection.HandleTimeoutOrThreadAbort(Exception ex)\r\n
at MySql.Data.MySqlClient.MySqlDataReader.Read()\r\n
at MySql.Data.EntityFramework.EFMySqlDataReader.Read()\r\n
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()\r\n
at MySql.Data.MySqlClient.TimedStream.Read(Byte[] buffer, Int32 offset, Int32 count)\r\n
at MySql.Data.MySqlClient.MySqlStream.ReadFully(Stream stream, Byte[] buffer, Int32 offset, Int32 count)\r\n
at MySql.Data.MySqlClient.MySqlStream.LoadPacket()\r\n
at MySql.Data.MySqlClient.MySqlStream.ReadPacket()\r\n
at MySql.Data.MySqlClient.NativeDriver.FetchDataRow(Int32 statementId, Int32 columns)\r\n
at MySql.Data.MySqlClient.Driver.FetchDataRow(Int32 statementId, Int32 columns)\r\n
at MySql.Data.MySqlClient.ResultSet.GetNextRow()\r\n
at MySql.Data.MySqlClient.ResultSet.NextRow(CommandBehavior behavior)\r\n
at MySql.Data.MySqlClient.MySqlDataReader.Read()"}}}
Try adding this to your controller:
protected override void Dispose(bool disposing)
{
if (disposing)
{
if (this.FitnessDbo != null) { this.FitnessDbo.Dispose(); }
}
base.Dispose(disposing);
}
Edit for some explanation:
From the questions via comments, it sounds like even though the controller is disposed at the end of a request, the connections used by the context are being reused but are not in a useable state. By explicitly disposing of the context when the controller is disposed, the context and connections should be closed properly ensuring we get a fresh connection on the next request.