How make a query dynamic to filter records in the tables?

Gcobani54

Member
Joined
Apr 26, 2021
Messages
7
Programming Experience
3-5
Hi Team

I am struggling to make my query dynamic, what i mean here the record list must be formatted. Currently now i will attached the output of my excel and columns are not formatted for data be exposed. The first attachment is my current data. The out output must be second attachment, will attached it here for clarity. Here is my logic below for more information.
exporting record list as excel using asp.net mvc 5:
  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; }
    }

  public ActionResult DataResult()
        {
            return View(this.GetExtractionViewModels());
        }

    public void ExportToExcel()
        {

            var v = new GridView();
            v.DataSource = this.GetExtractionViewModels();
            v.DataBind();
            Response.ClearContent();
            Response.Buffer = true;
            Response.AddHeader("content-disposition", "attachment; filename=ExtractionRecords.xls");
            Response.ContentType = "application/ms-excel";
            Response.Charset = "";
            StringWriter objStringWriter = new StringWriter();
            HtmlTextWriter htmlTextWriter = new HtmlTextWriter(objStringWriter);
            v.RenderControl(htmlTextWriter);
            Response.Output.Write(objStringWriter.ToString());
            Response.Flush();
            Response.End();
            //return View("DataResult");
        }
 

Attachments

  • taken.PNG
    taken.PNG
    9.1 KB · Views: 2
  • filter.PNG
    filter.PNG
    21.9 KB · Views: 2

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
2,994
Location
Chesapeake, VA
Programming Experience
10+
Your desired screenshot has numbers for the non-date related columns. I assume those values are counts. So why don't you make your view model compute the counts?
 

Gcobani54

Member
Joined
Apr 26, 2021
Messages
7
Programming Experience
3-5
Your desired screenshot has numbers for the non-date related columns. I assume those values are counts. So why don't you make your view model compute the counts?
I think if can have an example, will attempt something on my side and get back if i am stuck, thanks.
 

Gcobani54

Member
Joined
Apr 26, 2021
Messages
7
Programming Experience
3-5
Hi Team

I have a problem and need some help, problem my data on excel is duplicating year and week, e.g if there is a data on year 2020, it does, but still count on week as 2, 2, 3, 3. While on front end it count only 1,2 only. Yet if there is no data on any year my excel must do the same and need help here. Please see my logic below;

extracting data using linq in query:
  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 descending, m.Name descending, p.ProductionDate descending,w.WeekNum descending
                                where(m.InActive == true)

                                select new ExtractionViewModel
                                {

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


                                }).ToList();
        
          
            return scheduleList;
        }
 

Herman

Active member
Joined
May 5, 2016
Messages
32
Programming Experience
10+
That would be because you have two different models (m)...

A sample of data, and the table structures, would help a lot answering your question.
 

Gcobani54

Member
Joined
Apr 26, 2021
Messages
7
Programming Experience
3-5
That would be because you have two different models (m)...

A sample of data, and the table structures, would help a lot answering your question.
SQL:
SELECT TOP (1000) [ModelId]
      ,[Name]
      ,[Code]
      ,[CreatedDate]
      ,[CreatedBy]
      ,[ModifiedDate]
      ,[ModifiedBy]
      ,[InActive]
  FROM [ProductionManagement].[Schedule].[Model]
  where Name = 'VW270'
  order by InActive;
 

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
2,994
Location
Chesapeake, VA
Programming Experience
10+
Don't you already have a thread about this open?
 

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
2,994
Location
Chesapeake, VA
Programming Experience
10+
Yup, you did. Merged the two threads together.

Also moving this thread out of C# General Discussion since this looks to be more Entity Framework specific...
 
Last edited:
Top Bottom