Linq 排序问题

[风淡云清2013]

表里有一个字段OpenDate为日期型，我只取该字段的年月，以及部门字段UID 来分组，取年月后就变成了字符型
代码如下：

private GHEntities db = new GHEntities();
..............................略
IQueryable<ListForOpenTransactionVM> localDB = null;

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.OpenDateYM,g.Key.UID descending
                           select new ListForOpenTransactionVM
                           {
                               UID = g.Key.UID,
                               OpenDateYM = g.Key.OpenDateYM,
                               CountOT = g.Count(),
                               Account = db.Accounts.FirstOrDefault(a => a.ID == g.Key.UID)
                           }).Take(12);

分组排序后显示的结果如下
部门 年月 记录总数
财务部 2017-12 1
人事部 2017-4 1
财务部 2017-5 1
财务部 2017-6 2
人事部 2017-7 3
财务部 2017-7 1

我想要以年月为主降序排，该如何修改！

[csdnFUCKINGSUCKS]

group o by new { o.UID, OpenDateYM = new DateTime(int.Parse(o.OpenDate.Year), int.Parse(o.OpenDate.Month), 0)} into g

[q107770540]

localDB = (from o in db.tz_OpenTransactions
                           
                           group o by new { o.UID, OpenDateYM = o.OpenDate.Year + "-" + o.OpenDate.Month} into g
                           let date = g.Key.OpenDateYM.Split('-')
                           orderby  Convert.ToInt32(date[0]) descending, Convert.ToInt32(date[1]) descending
                           select new ListForOpenTransactionVM
                           {
                               UID = g.Key.UID,
                               OpenDateYM = g.Key.OpenDateYM,
                               CountOT = g.Count(),
                               Account = db.Accounts.FirstOrDefault(a => a.ID == g.Key.UID)
                           }).Take(12);

[风淡云清2013]

linq to ef 不支持 addDays语法 共享一下 from o in db.tz_OpenTransactions group o by new { o.UID, Year = o.OpenDate.Year, Month = o.OpenDate.Month } into g orderby g.Key.Year descending, g.Key.Month descending select new ListForOpenTransactionVM { UID = g.Key.UID, OpenDateYM = g.Key.Year + "-" + g.Key.Month, CountOT = g.Count(), Account = db.Accounts.FirstOrDefault(a => a.ID == g.Key.UID) }

[正怒月神]

引用 8 楼 u012048297 的回复:

linq to ef 不支持 addDays语法

并不是linq to ef不支持adddays， 只是使用这个方法你需要对象已经是一个 IEnumerable<T> 加载到本地的对象。 不过你可以使用 SqlFunctions.DateAdd https://msdn.microsoft.com/en-us/library/dd466214(v=vs.110).aspx

[ilikeff8]

引用 8 楼 u012048297 的回复:

linq to ef 不支持 addDays语法 共享一下 from o in db.tz_OpenTransactions group o by new { o.UID, Year = o.OpenDate.Year, Month = o.OpenDate.Month } into g orderby g.Key.Year descending, g.Key.Month descending select new ListForOpenTransactionVM { UID = g.Key.UID, OpenDateYM = g.Key.Year + "-" + g.Key.Month, CountOT = g.Count(), Account = db.Accounts.FirstOrDefault(a => a.ID == g.Key.UID) }

是演示代码，懒得去捡数据库表和EF实体， 忘了说了，如果是EF6，则AddDays可以替换为 using System.Data.Entity; DbFunctions.AddDays

[ilikeff8]

如果是老版本ef,可以用SqlFuncrtions，或EdmFunctions，都大同小异的

[风淡云清2013]

q107770540 + $exception {"LINQ to Entities 不识别方法“System.String[] Split(Char[])”，因此该方法无法转换为存储表达式。"} System.NotSupportedException

[liubin911]

try orderby g.Key.OpenDateYM,g.Key.UID descending=》orderby g.Key.OpenDateYM descending,g.Key.UID descending

[风淡云清2013]

to: liubin911 改成 orderby g.Key.OpenDateYM descending, g.Key.UID descending 显示如下 部门 年月 记录总数 人事部 2017-7 3 财务部 2017-7 1 财务部 2017-6 2 财务部 2017-5 1 人事部 2017-4 1 财务部 2017-12 1 12月又排到了后面

[ilikeff8]

[ilikeff8]

            DataTable dt = new DataTable();
            dt.Columns.Add("Department");
            dt.Columns.Add("OpenDate");

            dt.Rows.Add(new object[] { "财务部", new DateTime(2017, 12, 1, 0, 0, 0) });
            dt.Rows.Add(new object[] { "财务部", new DateTime(2017, 5, 1, 0, 0, 1) });
            dt.Rows.Add(new object[] { "财务部", new DateTime(2017, 6, 1, 0, 0, 2) });
            dt.Rows.Add(new object[] { "财务部", new DateTime(2017, 6, 2, 0, 0, 3) });
            dt.Rows.Add(new object[] { "财务部", new DateTime(2017, 6, 3, 0, 0, 4) });
            dt.Rows.Add(new object[] { "财务部", new DateTime(2017, 7, 1, 0, 0, 5) });
            dt.Rows.Add(new object[] { "人事部", new DateTime(2017, 4, 1, 0, 0, 6) });
            dt.Rows.Add(new object[] { "人事部", new DateTime(2017, 7, 1, 0, 0, 7) });
            dt.Rows.Add(new object[] { "人事部", new DateTime(2017, 7, 2, 0, 0, 8) });
            dt.Rows.Add(new object[] { "人事部", new DateTime(2017, 7, 3, 0, 0, 9) });

            var q = from d in dt.AsEnumerable()
                    group d by new
                    {
                        Department = d[0].ToString(),
                        OpenDate = DateTime.Parse(d[1].ToString()).Date.AddDays(-DateTime.Parse(d[1].ToString()).Day + 1),
                    } into g
                    orderby g.Key.OpenDate descending,g.Key.Department 
                    select new
                    {
                        部门 = g.Key.Department,
                        年月 = g.Key.OpenDate.ToString("yyyy-M"),
                        记录总数 = g.Count()
                    };


            var result = q.ToList();
            MessageBox.Show(string.Join(Environment.NewLine, result.ToList()));

[q107770540]

引用 3 楼 u012048297 的回复:

q107770540 + $exception {"LINQ to Entities 不识别方法“System.String[] Split(Char[])”，因此该方法无法转换为存储表达式。"} System.NotSupportedException

try this: localDB = (from o in db.tz_OpenTransactions.ToList() group o by new { o.UID, OpenDateYM = o.OpenDate.Year + "-" + o.OpenDate.Month} into g let date = g.Key.OpenDateYM.Split('-') orderby Convert.ToInt32(date[0]) descending, Convert.ToInt32(date[1]) descending select new ListForOpenTransactionVM { UID = g.Key.UID, OpenDateYM = g.Key.OpenDateYM, CountOT = g.Count(), Account = db.Accounts.FirstOrDefault(a => a.ID == g.Key.UID) }).Take(12);