CurrentUtcDateTime不存在-实体框架和MySql [英] CurrentUtcDateTime does not exist - Entity Framework and MySql

问题描述

我在Entity Framework 4.1和MySql Connector/Net 6.4.3中的规范功能有问题. 根据Microsoft的规范，所有数据库提供程序都将从LINQ生成的SQL中理解规范功能并将其转换为本地SQL方言. http://msdn.microsoft.com/en-us/library/bb738626.aspx 但是，我的代码对CurrentUtcDateTime()感到窒息，在此处列出； http://msdn.microsoft.com/en-us/library/bb738563.aspx

I am having a problem with canonical functions in Entity Framework 4.1 and MySql Connector/Net 6.4.3. According to Microsoft cannonical functions are understood and translated into the local SQL dialect by all database providers from the SQL generated by LINQ; http://msdn.microsoft.com/en-us/library/bb738626.aspx However, my code chokes on CurrentUtcDateTime(), which is listed here; http://msdn.microsoft.com/en-us/library/bb738563.aspx

这是LINQ查询(来自NopCommerce)，它生成令人反感的SQL:

Here is the LINQ query (from NopCommerce) that generates the offensive SQL:

    public List<Poll> GetPolls(int languageId, int pollCount, bool loadShownOnHomePageOnly)
    {
        bool showHidden = NopContext.Current.IsAdmin;


        var query = (IQueryable<Poll>)_context.Polls;
        if (!showHidden)
        {
            query = query.Where(p => p.Published);
            query = query.Where(p => !p.StartDate.HasValue || p.StartDate <= DateTime.UtcNow);
            query = query.Where(p => !p.EndDate.HasValue || p.EndDate >= DateTime.UtcNow);
        }
        if (loadShownOnHomePageOnly)
        {
            query = query.Where(p => p.ShowOnHomePage);
        }
        if (languageId > 0)
        {
            query = query.Where(p => p.LanguageId == languageId);
        }

        query = query.OrderBy(p => p.DisplayOrder);
        if (pollCount > 0)
        {
            query = query.Take(pollCount);
        }

        var polls = query.ToList();

        return polls;
    }

query.ToList()生成以下SQL:

query.ToList() generates the SQL below:

SELECT`Project1`.`PollID`, `Project1`.`LanguageID`, `Project1`.`Name`, 
`Project1`.`Published`, `Project1`.`ShowOnHomePage`, `Project1`.`DisplayOrder`, 
`Project1`.`SystemKeyword`, `Project1`.`StartDate`, `Project1`.`EndDate`
FROM (SELECT`Extent1`.`PollID`, `Extent1`.`LanguageID`, `Extent1`.`Name`, 
`Extent1`.`SystemKeyword`, `Extent1`.`Published`, `Extent1`.`ShowOnHomePage`, 
`Extent1`.`DisplayOrder`, `Extent1`.`StartDate`, `Extent1`.`EndDate`
FROM `Nop_Poll` AS `Extent1` WHERE ((((`Extent1`.`Published` = 1) AND 
((`Extent1`.`StartDate` IS  NULL) OR (`Extent1`.`StartDate` <= (CurrentUtcDateTime())))) 
AND ((`Extent1`.`EndDate` IS  NULL) OR (`Extent1`.`EndDate` >= (CurrentUtcDateTime())))) 
AND (`Extent1`.`ShowOnHomePage` = 1)) AND (`Extent1`.`LanguageID` = @p__linq__0)) 
AS `Project1` ORDER BY `Project1`.`DisplayOrder` ASC LIMIT 2147483647

这是错误输出:

*FUNCTION myDatabase.CurrentUtcDateTime does not exist 
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 
Exception Details: MySql.Data.MySqlClient.MySqlException: FUNCTION myDatabase.CurrentUtcDateTime does not exist*

我错过了什么吗?请指教.谢谢.

Am I missing something? Please advice. Thanks.

推荐答案

我遇到了同样的问题，并且花了近两天的时间才弄清楚.它似乎是MySql的EntityFramework映射中的错误.

I encountered this exact same problem and lost almost two days trying to figure it out. It appears to be a bug in the EntityFramework mappings for MySql.

解决方案是将 DateTime.UtcNow 计算移出有作用域的lambda并插入实际值.

The solution is to move the DateTime.UtcNow calculation outside of the scoped lambda and plug in the actual value.

var utcNow = DateTime.UtcNow;
query = query.Where(p => p.Published);
query = query.Where(p => !p.StartDate.HasValue || p.StartDate <= utcNow);
query = query.Where(p => !p.EndDate.HasValue || p.EndDate >= utcNow);

这篇关于CurrentUtcDateTime不存在-实体框架和MySql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！