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: 13
  • filter.PNG
    filter.PNG
    21.9 KB · Views: 13
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?
 
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.
 
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;
        }
 
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.
 
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;
 
Don't you already have a thread about this open?
 
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:
Back
Top Bottom