mysql - 将 MySql 转换为 LINQ

Question

我正在构建从 MySQL DB 返回数据的 API，首先我创建了查询并尝试将其转换为 LINQ，结果出现了一个异常，我认为这是由我形成查询的方式引起的。下面是 MySQL 和 LINQ 的代码。

// 询问

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);

//带有c#代码的API

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) > beginOfMonthDate.Date &&
                                 DbFunctions.TruncateTime(p.date) < currentDate.Date
                                 select p.id).Distinct().Count();
        return Ok(LeaversMTD);
    }

我收到的异常

{"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.<>c__DisplayClass7.<GetResults>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.<>c__DisplayClass7.<GetResults>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.<System.Collections.Generic.IEnumerable<T>.GetEnumerator>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.<GetElementFunction>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.<>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":"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& affectedRow, Int64& insertedId)\r\n   at MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32& affectedRows, Int64& 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.<Reader>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)"}}

标签： mysqllinqasp.net-web-api