在sql asp.net mvc 5中使用linq时，如何格式化记录上的数据？

vs3odd8k 于 2021-07-26 发布在 Java

关注(0)|答案(1)|浏览(291)

我有一个spreedsheet和数据，但问题是我的数据是错误的，是它的格式不符合要求，需要一些帮助。这是我的应用逻辑。sql查询是针对我的记录列表，从数据库中查询1个数据库中的3个不同表，控制器和模型是针对我的逻辑，以根据我的要求生成以下图像。

// SQL query

  SELECT TOP (1000) [WeekId]
          ,[WeekNum]
          ,[Year]
          ,[CreatedDate]
          ,[CreatedBy]
          ,[ModifiedDate]
          ,[ModifiedBy]
          ,[InActive]
      FROM [ProductionManagement].[Schedule].[Week]
    SELECT TOP (1000) [ProductionDayId]
          ,[WeekId]
          ,[ProductionDate]
          ,[DayOfWeek]
          ,[CreatedDate]
          ,[CreatedBy]
          ,[ModifiedDate]
          ,[ModifiedBy]
          ,[InActive]
      FROM [ProductionManagement].[Schedule].[ProductionDay]
    SELECT TOP (1000) [ModelId]
          ,[Name]
          ,[Code]
          ,[CreatedDate]
          ,[CreatedBy]
          ,[ModifiedDate]
          ,[ModifiedBy]
          ,[InActive]
      FROM [ProductionManagement].[Schedule].[Model]

    // controller
           public IList<ExtractionViewModel> GetExtractionViewModels()
            {
                 var db = new ProductionManagementEntities();

                var scheduleList = (from p in db.ProductionDays
                                    from m in db.Models
                                    join w in db.Weeks on p.WeekId equals w.WeekId
                                    orderby w.Year ascending
                                    orderby m.Name descending
                                    where(m.InActive == true)

                                    select new ExtractionViewModel
                                    {

                                        Year = w.Year,
                                        Week = w.WeekNum,
                                        Day = p.ProductionDate,
                                        VW250 = m.Name,
                                        VW270 = m.Name,
                                        VW2502PA = m.Name,
                                        VW270PA = m.Name

                                    }).ToList();

                return scheduleList;
            }
      public class ExtractionViewModel
        {
            public string Year { get; set; }

            public int Week { get; set; }

            [DataType(DataType.Date)]
            [DisplayFormat(DataFormatString = "{0;yyyy-MM-dd}", ApplyFormatInEditMode =false)]
            public DateTime Day { get; set; }

            public string VW250 { get; set; }

            public string VW270 { get; set; }

            public string VW2502PA { get; set; }

            public string VW270PA { get; set; }
        }

sql c#linq asp.net-mvc

来源：https://stackoverflow.com/questions/67267605/how-to-format-your-data-on-a-record-when-using-linq-in-sql-asp-net-mvc-5

1条答案

按热度按时间

cyvaqqii1#

尝试以下操作：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace ConsoleApplication
{
    public class Program
    {

        public static void Main(string[] args)
        {

            var db = new ProductionManagementEntities();

            var scheduleList = (from p in db.ProductionDays.OrderBy(x => x.CreatedDate)
                                join w in db.Weeks on p.WeekId equals w.WeekId
                                join m in db.Models.Where(x =>x.InActive == false) on p.CreatedDate equals m.CreatedDate
                                select new { p = p, w = w, m = m}
                                ).GroupBy(x => x.p.CreatedDate)
                                .Select(x => new ExtractionViewModel() {
                                        Year = x.Key.Year,
                                        Week = x.FirstOrDefault().w.WeekId,
                                        Day = x.FirstOrDefault().p.ProductionDate,
                                        VW250 = x.Where(y => y.m.Name == "VM250").Count(),
                                        VW270 = x.Where(y => y.m.Name == "VM270").Count(),
                                        VW2502PA = x.Where(y => y.m.Name == "VM2502PA").Count(),
                                        VW270PA = x.Where(y => y.m.Name == "VM270PA").Count()    
                                 }).ToList();
            }
    }
    public class ProductionManagementEntities
    {
        public List<Week> Weeks { get;set;}
        public List<ProductionDay> ProductionDays { get;set;}
        public List<Model> Models { get;set;}
    }

    public class Week
    {
        public int WeekId { get;set;}
        public DateTime CreatedDate { get;set;}
        public string CreatedBy { get;set;}
        public DateTime ModifiedDate { get;set;}
        public string ModifiedBy { get;set;}
        public Boolean InActive { get;set;}
    }
    public class ProductionDay
    {
        public int WeekId { get;set;}
        public DateTime ProductionDate { get;set;}
        public DayOfWeek DayOfWeek { get;set;}
        public DateTime CreatedDate { get;set;}
        public string CreatedBy { get;set;}
        public DateTime ModifiedDate { get;set;}
        public string ModifiedBy { get;set;}
        public Boolean InActive { get;set;}
    }
    public class Model
    {
        public string Name { get; set; }
        public int Code { get; set; }
        public DateTime CreatedDate { get; set; }
        public string CreatedBy { get; set; }
        public DateTime ModifiedDate { get; set; }
        public string ModifiedBy { get; set; }
        public Boolean InActive { get; set; }
    }

    public class ExtractionViewModel
    {
        public int Year { get; set; }
        public int Week { get; set; }
        public DateTime Day { get; set; }
        public int VW250 { get; set; }
        public int VW270 { get; set; }
        public int VW2502PA { get; set; }
        public int VW270PA { get; set; }
    }
}

赞(0）回复(0）举报 2021-07-26

我来回答

在sql asp.net mvc 5中使用linq时，如何格式化记录上的数据？

1条答案

相关问题

热门标签

最新问答