热门关键字: php net javascript java
Just Internet && Only Internet
您的位置:csharp > 执行数据库内置方法总结
执行数据库内置方法总结
发布时间:2018-12-07 15:24:44

        linq无法调用数据库内置格式化日期方法,用下面的方式变通
        错误方式demo(下面这个方法会提示Linq无法识别 Date.Date属性)
        using (var db = new DbContext())
        {
                var items = from a in db.Ques
                            group a by new { a.Date.Date } into gp
                            select new { key = gp.Key, val = gp.Count() };

                dic = items.Take(30).ToDictionary(it => it.key.ak, it => it.val);
        }
        正常demo
        using (var db = new DbContext())
        {
                var items = from a in db.Ques
                            group a by new { ak = a.Date.Year + \"/\" + a.Date.Month + \"/\" + a.Date.Day } into gp
                            select new { key = gp.Key, val = gp.Count() };

                dic = items.Take(30).ToDictionary(it => it.key.ak, it => it.val);
        }

        public Dictionary<DateTime, int> GetCount(Guid? rfid, DateTime? s, DateTime? e)
        {
            using (var db = new DbContext())
            {
                var arts = (from a in db.Article
                             select a);

                if (s.HasValue) arts = arts.Where(a => a.Date >= s.Value);
                if (e.HasValue) arts = arts.Where(a => a.Date < e.Value);

                var items = (from a in arts
                             group a by new { Y = a.Date.Year, M = a.Date.Month, D = a.Date.Day } into gp
                             orderby gp.Key
                             select new
                             {
                                 U = gp.Key,
                                 C = gp.Count()
                             });

                return items.ToDictionary(x => new DateTime(x.U.Y, x.U.M, x.U.D), y => y.C);
            }
        }

       //直接执行mysql语句,调用内置DATE_FORMAT方法
       public Dictionary<string, int> GetCount(Guid campusID,SubjectTypeEnum subject, DateTime? sdate, DateTime? edate)
        {
            using (var db = new MysqlContext())
            {

                DateTime start = DateTime.MinValue;
                DateTime end = DateTime.MaxValue;

                if (sdate.HasValue)
                {
                    start = sdate.Value;
                }
                if (edate.HasValue)
                {
                    end = edate.Value;
                }

                Dictionary<string, int> dic = new Dictionary<string, int>();

                string sql = \"select DATE_FORMAT(`Date`, \'%Y-%m-%d\') as YMD,count(*) as GCount from User where `Date` between @SDate and @EDate  group by YMD order by YMD asc\";

                var context = db.Database.SqlQuery<_Grouping>(sql,
                    new MySqlParameter(\"@SDate\", start),
                    new MySqlParameter(\"@EDate\", end));

                context.ForEachAsync(it =>
                {
                    dic.Add(it.Ymd, it.GCount);
                });

                return dic;
            }

        }
登录 评论文章
声明:本站部分内容来自网络,如您发现本站内容侵害到您的利益,请联系本站管理员处理。