Linq to EF 中的字符转日期问题

风淡云清2013 2017-07-14 03:04:07

IQueryable<ListForOpenTransactionVM2> localDB = null;
.......
IFormatProvider ifp = new CultureInfo("zh-CN", true);
DateTime tempStartDate = DateTime.ParseExact(querystartDate,"yyyy-MM",ifp); //开始年月
DateTime tempEndDate = DateTime.ParseExact(queryendDate,"yyyy-MM", ifp);//结束年月

localDB = localDB.Where(x => DateTime.ParseExact(x.OpenDateYM,"yyyy-MM", ifp) >= tempStartDate && DateTime.ParseExact(x.OpenDateYM, "yyyy-MM", ifp) <= tempEndDate);


提示错误
System.NotSupportedException:“LINQ to Entities 不识别方法“System.DateTime ParseExact(System.String, System.String, System.IFormatProvider)”,因此该方法无法转换为存储表达式。”

OpenDateYM,字符型,类似2017-06 ,现在 我需要根据页面的起始,终止日期(也是年月)来做限定查询。


我在一篇http://www.itdos.com/CSharp/20150408/0127321.html 这里找到了 linq/EF/lambda 比较字符串日期时间大小

可我改用后总是查询不到结果,不知为什么?

localDB = localDB.Where(x => x.OpenDateYM.CompareTo( querystartDate ) >= 0 && x.OpenDateYM.CompareTo( queryendDate ) <= 0);
...全文
706 5 打赏 收藏 转发到动态 举报
写回复
用AI写文章
5 条回复
切换为时间正序
请发表友善的回复…
发表回复
风淡云清2013 2017-07-19
  • 打赏
  • 举报
回复
我看了下,似乎 用 DbFunctions.CreateDateTim 函数 下面是主要的代码,先根据OpenDate字段的年月,以及部门分组,再动态查询

 //dynamic Query
                localDB = (from o in db.tz_OpenTransactions
                           group o by new { o.UID, OpenDateYM = o.OpenDate.Year + "-" + o.OpenDate.Month } into g
                           orderby g.Key.UID, g.Key.OpenDateYM descending
                           select new ListForOpenTransactionVM2
                           {
                               UID = g.Key.UID,
                               OpenDateYM = g.Key.OpenDateYM,
                               CountOT = g.Count(),
                               Account = db.Accounts.FirstOrDefault(a => a.ID == g.Key.UID)
                           }).Take(12);

                if (!string.IsNullOrEmpty(querystartDate) && !string.IsNullOrEmpty(queryendDate))
                {
                    IFormatProvider ifp = new CultureInfo("zh-CN", true);
                    DateTime tempStartDate = DateTime.ParseExact(querystartDate, "yyyy-MM", ifp);
                    DateTime tempEndDate = DateTime.ParseExact(queryendDate, "yyyy-MM", ifp);

                    if (tempStartDate > tempEndDate)
                    {
                        return Content("起始日期要小于终止日期!");
                    }

                    localDB.Where(x => DbFunctions.CreateDateTime(Int32.Parse(x.OpenDateYM.Split('-')[0]), Int32.Parse(x.OpenDateYM.Split('-')[1]), 1, 0, 0, 0) >= tempStartDate && DbFunctions.CreateDateTime(Int32.Parse(x.OpenDateYM.Split('-')[0]), Int32.Parse(x.OpenDateYM.Split('-')[1]), 1, 0, 0, 0) <= tempEndDate);
                }
使用了CreateDateTime函数,选择起止年月,点击查询,数据还是没有变化,这是生产的sql

{SELECT TOP (12) 
    [Project2].[UID] AS [UID], 
    [Project2].[C1] AS [C1], 
    [Project2].[C2] AS [C2], 
    [Project2].[ID] AS [ID], 
    [Project2].[SimpleName] AS [SimpleName], 
    [Project2].[FullName] AS [FullName], 
    [Project2].[Password] AS [Password], 
    [Project2].[RoleType] AS [RoleType]
    FROM ( SELECT 
        [Distinct1].[UID] AS [UID], 
        [Distinct1].[C1] AS [C1], 
        [Limit1].[ID] AS [ID], 
        [Limit1].[SimpleName] AS [SimpleName], 
        [Limit1].[FullName] AS [FullName], 
        [Limit1].[Password] AS [Password], 
        [Limit1].[RoleType] AS [RoleType], 
        (SELECT 
            COUNT(1) AS [A1]
            FROM [dbo].[tz_OpenTransactions] AS [Extent3]
            WHERE ([Distinct1].[UID] = [Extent3].[UID]) AND (([Distinct1].[C1] = (CASE WHEN (DATEPART (year, [Extent3].[OpenDate]) IS NULL) THEN N'' ELSE  CAST( DATEPART (year, [Extent3].[OpenDate]) AS nvarchar(max)) END + N'-' + CASE WHEN (DATEPART (month, [Extent3].[OpenDate]) IS NULL) THEN N'' ELSE  CAST( DATEPART (month, [Extent3].[OpenDate]) AS nvarchar(max)) END)) OR (([Distinct1].[C1] IS NULL) AND (CASE WHEN (DATEPART (year, [Extent3].[OpenDate]) IS NULL) THEN N'' ELSE  CAST( DATEPART (year, [Extent3].[OpenDate]) AS nvarchar(max)) END + N'-' + CASE WHEN (DATEPART (month, [Extent3].[OpenDate]) IS NULL) THEN N'' ELSE  CAST( DATEPART (month, [Extent3].[OpenDate]) AS nvarchar(max)) END IS NULL)))) AS [C2]
        FROM   (SELECT DISTINCT 
            [Extent1].[UID] AS [UID], 
            CASE WHEN (DATEPART (year, [Extent1].[OpenDate]) IS NULL) THEN N'' ELSE  CAST( DATEPART (year, [Extent1].[OpenDate]) AS nvarchar(max)) END + N'-' + CASE WHEN (DATEPART (month, [Extent1].[OpenDate]) IS NULL) THEN N'' ELSE  CAST( DATEPART (month, [Extent1].[OpenDate]) AS nvarchar(max)) END AS [C1]
            FROM [dbo].[tz_OpenTransactions] AS [Extent1] ) AS [Distinct1]
        OUTER APPLY  (SELECT TOP (1) [Extent2].[ID] AS [ID], [Extent2].[SimpleName] AS [SimpleName], [Extent2].[FullName] AS [FullName], [Extent2].[Password] AS [Password], [Extent2].[RoleType] AS [RoleType]
            FROM [dbo].[Accounts] AS [Extent2]
            WHERE [Extent2].[ID] = [Distinct1].[UID] ) AS [Limit1]
    )  AS [Project2]
    ORDER BY [Project2].[UID] ASC, [Project2].[C1] DESC}
笑容融化坚冰 2017-07-16
  • 打赏
  • 举报
回复
看看SqlFunctions、DbFunctions、EntityFunctions类里有没有相关函数
雷诺兹 2017-07-14
  • 打赏
  • 举报
回复
改用SqlFunctions的方法

110,499

社区成员

发帖
与我相关
我的任务
社区描述
.NET技术 C#
社区管理员
  • C#
  • Web++
  • by_封爱
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告

让您成为最强悍的C#开发者

试试用AI创作助手写篇文章吧