Linq到SQL格式一次完成日期时间

本文关键字：一次 日期 时间 SQL 格式 Linq | 更新日期: 2023-09-27 18:00:01

这在stackoverflow上被多次问及如何在linq-to-sql表达式中格式化日期时间，解决方案始终是先使用ToList（）强制编译linq，然后使用的豪华处理日期时间。ToString（"hh:mm"）格式选项。

但我试图一次完成，我部分成功了，只是代码既糟糕又丑陋，任何更短的方法都可以做到这一点，假设日期对象是存储在数据库中的unix时间戳，我试图只返回时间部分为4:54 pm。

            production_cycles = from p in db.ProductionCycles
                                where p.IsRunning == true
                                select new Rest.ProductionCycle {
                                    id = p.ID,
                                    name = p.Name,
                                    created = p.Created,
                                    steps = from s in p.Logs
                                            where user_permissions.Contains(s.Permission.ID)
                                            orderby s.ID ascending
                                            select new Rest.Step {
                                                created = s.Created,
                                                created_label = DbFunctions.CreateDateTime(
                           (int)SqlFunctions.DatePart("yyyy", (DateTime)SqlFunctions.DateAdd("ss", s.Created + (offset * 60 * 60), new DateTime(1970, 1, 1, 0, 0, 0, 0))),
                           (int)SqlFunctions.DatePart("m", (DateTime)SqlFunctions.DateAdd("ss", s.Created + (offset * 60 * 60), new DateTime(1970, 1, 1, 0, 0, 0, 0))),
                           (int)SqlFunctions.DatePart("d", (DateTime)SqlFunctions.DateAdd("ss", s.Created + (offset * 60 * 60), new DateTime(1970, 1, 1, 0, 0, 0, 0))),
                           (int)SqlFunctions.DatePart("hh", (DateTime)SqlFunctions.DateAdd("ss", s.Created + (offset * 60 * 60), new DateTime(1970, 1, 1, 0, 0, 0, 0))),
                           (int)SqlFunctions.DatePart("mi", (DateTime)SqlFunctions.DateAdd("ss", s.Created + (offset * 60 * 60), new DateTime(1970, 1, 1, 0, 0, 0, 0))),
                           0
                          ).Value.Hour.ToString() 
                          + ":" +
                            DbFunctions.CreateDateTime(
                           (int)SqlFunctions.DatePart("yyyy", (DateTime)SqlFunctions.DateAdd("ss", s.Created + (offset * 60 * 60), new DateTime(1970, 1, 1, 0, 0, 0, 0))),
                           (int)SqlFunctions.DatePart("m", (DateTime)SqlFunctions.DateAdd("ss", s.Created + (offset * 60 * 60), new DateTime(1970, 1, 1, 0, 0, 0, 0))),
                           (int)SqlFunctions.DatePart("d", (DateTime)SqlFunctions.DateAdd("ss", s.Created + (offset * 60 * 60), new DateTime(1970, 1, 1, 0, 0, 0, 0))),
                           (int)SqlFunctions.DatePart("hh", (DateTime)SqlFunctions.DateAdd("ss", s.Created + (offset * 60 * 60), new DateTime(1970, 1, 1, 0, 0, 0, 0))),
                           (int)SqlFunctions.DatePart("mi", (DateTime)SqlFunctions.DateAdd("ss", s.Created + (offset * 60 * 60), new DateTime(1970, 1, 1, 0, 0, 0, 0))),
                           0
                          ).Value.Minute.ToString()                         

                          ,
                                                username = s.User.Name,

Linq到SQL格式一次完成日期时间

请尝试一下：

var baseDate = new DateTime(1970, 1, 1, 0, 0, 0, DateTimeKind.Utc);
production_cycles = from p in db.ProductionCycles
    where p.IsRunning == true
    select new Rest.ProductionCycle
    {
        id = p.ID,
        name = p.Name,
        created = p.Created,
        steps = from s in p.Logs
            where user_permissions.Contains(s.Permission.ID)
            orderby s.ID ascending
            select new Rest.Step
            {
                created = s.Created,
                created_label = DbFunctions.DiffHours(baseDate, s.Created).ToString + ":" + DbFunctions.DiffMinutes(baseDate, s.Created).ToString + ":" +DbFunctions.DiffSeconds(baseDate, s.Created).ToString
                ////// the rest as usual
            },
    };
相关文章：