title: Converting MySql to LINQ
body: <p>I am building APIs that return data from MySQL DB first i have created the query and tried to convert it Into LINQ and ended up with an exception which i think caused by the way I formed the Query.
  bellow the code for both MySQL and LINQ.</p>
  
  <p>// Query </p>
  
  <pre><code>Select COUNT(m.mepi_memberid)
                  FROM(
                      SELECT mepi_memberid, MAX(mepi_expirydate) as dateMax 
                        from memberproductinfo
                        INNER JOIN product_type AS pt ON mepi_prodtype = pt.prty_typeid
                        WHERE pt.prty_name = 'Membership'
                       GROUP BY mepi_memberid
                  ) as m
                WHERE m.dateMax BETWEEN DATE_FORMAT(CURRENT_DATE(),'%Y-%m-01') AND DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY);
  </code></pre>
  
  <p>//API with the c# code</p>
  
  <pre><code>public IHttpActionResult leaveMember()
      {
  
          var leaversCount = (from mp in FitnessDbo.memberproductinfoes
                              join pt in FitnessDbo.product_type on mp.mepi_prodtype equals pt.prty_typeid.ToString()
                              where pt.prty_name == "MEMBERSHIP"
                              group mp.mepi_memberid by mp.mepi_expirydate  into g
                              select new { id = g.Key, date = g.ToList().Max()  });
          var currentDate = DateTime.Now.Date;
          var beginOfMonthDate = new DateTime(currentDate.Year, currentDate.Month, 1);
          var LeaversMTD = (from p in leaversCount
                                   where DbFunctions.TruncateTime(p.date) &gt; beginOfMonthDate.Date &amp;&amp;
                                   DbFunctions.TruncateTime(p.date) &lt; currentDate.Date
                                   select p.id).Distinct().Count();
          return Ok(LeaversMTD);
      }
  </code></pre>
  
  <p>Exception I have Received </p>
  
  <pre><code>{"message":"An error has occurred.","exceptionMessage":"An error occurred while executing the command definition. See the inner exception for details.","exceptionType":"System.Data.Entity.Core.EntityCommandExecutionException","stackTrace":"   at System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)\r\n   at System.Data.Entity.Core.Objects.Internal.ObjectQueryExecutionPlan.Execute[TResultType](ObjectContext context, ObjectParameterCollection parameterValues)\r\n   at System.Data.Entity.Core.Objects.ObjectQuery`1.&lt;&gt;c__DisplayClass7.&lt;GetResults&gt;b__6()\r\n   at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)\r\n   at System.Data.Entity.Core.Objects.ObjectQuery`1.&lt;&gt;c__DisplayClass7.&lt;GetResults&gt;b__5()\r\n   at System.Data.Entity.Infrastructure.DefaultExecutionStrategy.Execute[TResult](Func`1 operation)\r\n   at System.Data.Entity.Core.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption)\r\n   at System.Data.Entity.Core.Objects.ObjectQuery`1.&lt;System.Collections.Generic.IEnumerable&lt;T&gt;.GetEnumerator&gt;b__0()\r\n   at System.Data.Entity.Internal.LazyEnumerator`1.MoveNext()\r\n   at System.Linq.Enumerable.Single[TSource](IEnumerable`1 source)\r\n   at System.Data.Entity.Core.Objects.ELinq.ObjectQueryProvider.&lt;GetElementFunction&gt;b__3[TResult](IEnumerable`1 sequence)\r\n   at System.Data.Entity.Core.Objects.ELinq.ObjectQueryProvider.ExecuteSingle[TResult](IEnumerable`1 query, Expression queryRoot)\r\n   at System.Data.Entity.Core.Objects.ELinq.ObjectQueryProvider.System.Linq.IQueryProvider.Execute[TResult](Expression expression)\r\n   at System.Data.Entity.Internal.Linq.DbQueryProvider.Execute[TResult](Expression expression)\r\n   at System.Linq.Queryable.Count[TSource](IQueryable`1 source)\r\n   at BI_leejam.Controllers.MembersController.leaveMember() in C:\\Users\\ahijazi\\source\\repos\\BI_leejam\\BI_leejam\\Controllers\\MembersController.cs:line 80\r\n   at lambda_method(Closure , Object , Object[] )\r\n   at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ActionExecutor.&lt;&gt;c__DisplayClass6_2.&lt;GetExecutor&gt;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.&lt;InvokeActionAsyncCore&gt;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.&lt;ExecuteAsync&gt;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.&lt;SendAsync&gt;d__15.MoveNext()","innerException":{"message":"An error has occurred.","exceptionMessage":"FUNCTION dbo.TruncateTime does not exist","exceptionType":"MySql.Data.MySqlClient.MySqlException","stackTrace":"   at MySql.Data.MySqlClient.MySqlStream.ReadPacket()\r\n   at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32&amp; affectedRow, Int64&amp; insertedId)\r\n   at MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32&amp; affectedRows, Int64&amp; insertedId)\r\n   at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force)\r\n   at MySql.Data.MySqlClient.MySqlDataReader.NextResult()\r\n   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)\r\n   at MySql.Data.EntityFramework.EFMySqlCommand.ExecuteDbDataReader(CommandBehavior behavior)\r\n   at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)\r\n   at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.&lt;Reader&gt;b__c(DbCommand t, DbCommandInterceptionContext`1 c)\r\n   at System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch[TTarget,TInterceptionContext,TResult](TTarget target, Func`3 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed)\r\n   at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.Reader(DbCommand command, DbCommandInterceptionContext interceptionContext)\r\n   at System.Data.Entity.Internal.InterceptableDbCommand.ExecuteDbDataReader(CommandBehavior behavior)\r\n   at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)\r\n   at System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)"}}
  </code></pre>