110,499
社区成员
发帖
与我相关
我的任务
分享
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);
localDB = localDB.Where(x => x.OpenDateYM.CompareTo( querystartDate ) >= 0 && x.OpenDateYM.CompareTo( queryendDate ) <= 0);
//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}