How to use session in linq c# export data as excel

Question

HI Team

 

I need some help, i have a problem. I want to filter records on my excel sheets as data. Now i dont know how sessions works to export data as excel. Below is my logic i have attempted to join tables using linq. Some how the data itself on excel is not filtering correct values. e.g if i search by YEAR(2021) and Week(53) on query get record this. On the excel itself its not corresponding to my query and need help to it via session.

public IList GetExtractionViewModels()
{
var db = new ProductionManagementEntities();
var scheduleList = (from p in db.ProductionDays
from m in db.Models
from mx in db.Models
from mt in db.Models
from mv in db.Models
from wk 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, mt.Name descending, mx.Name descending, mv.Name descending
where (mx.InActive == true)
where (mt.InActive == false)
where(m.InActive == false)
where(mv.InActive == false)
where(w.WeekNum < 53)
where (mt.Name == "VW270")
where(mx.Name == "VW250")
where(m.Name == "VW270PA")
where(mv.Name == "VW250/2PA")
select new ExtractionViewModel
{
Year = w.Year,
Day = p.ProductionDate,
Week = w.WeekNum,
VW270 = mt.Name,
VW270PA = m.Name,
VW250 = mx.Name,
VW2502PA = mv.Name
}).ToList();
return scheduleList;
}

// model
public class ExtractionViewModel
{
    public string Year { get; set; }
    public int Week { get; set; }
    [DataType(DataType.Date)]
    public DateTime Day { get; set; }
    public string VW250 { get; set; }
    public string VW270 { get; set; }
    public string VW2502PA { get; set; }
    public int VW270PA { get; set; }
}

// Sql for Model
SELECT TOP (1000) [ModelId]
      ,[Name]
      ,[Code]
      ,[CreatedDate]
      ,[CreatedBy]
      ,[ModifiedDate]
      ,[ModifiedBy]
      ,[InActive]
  FROM [ProductionManagement].[Schedule].[Model]
  where Name = 'VW240'
  order by InActive

//Week table

SELECT TOP (1000) [WeekId]
      ,[WeekNum]
      ,[Year]
      ,[CreatedDate]
      ,[CreatedBy]
      ,[ModifiedDate]
      ,[ModifiedBy]
      ,[InActive]
  FROM [ProductionManagement].[Schedule].[Week]

// ProductionDate
SELECT TOP (1000) [ProductionDayId]
      ,[WeekId]
      ,[ProductionDate]
      ,[DayOfWeek]
      ,[CreatedDate]
      ,[CreatedBy]
      ,[ModifiedDate]
      ,[ModifiedBy]
      ,[InActive]
  FROM [ProductionManagement].[Schedule].[ProductionDay]

 // Controller for exporting data
 public ActionResult List()
 {
     string constr = ConfigurationManager.AppSettings["connectionString"];
     var Client = new MongoClient(constr);
     var db = Client.GetDatabase("ProductionManagementEntities");

     var collection = db.GetCollection<ExactionViewModel>("ExtractionViewModel").Find(new BsonDocument()).ToList();

     return View(collection);
 }

void DownloadExcel()
{
    string constr = ConfigurationManager.AppSettings["connectionString"];
    var Client = new MongoClient(constr);
    var db = Client.GetDatabase("Employee");

    var collection = db.GetCollection<ExactionViewModel>("ExtractionViewModel").Find(new BsonDocument()).ToList();


    ExcelPackage Ep = new ExcelPackage();
    ExcelWorksheet Sheet = Ep.Workbook.Worksheets.Add("Report");
    Sheet.Cells["A1"].Value = "Year";
    Sheet.Cells["B1"].Value = "Week";
    Sheet.Cells["C1"].Value = "Day";
    Sheet.Cells["D1"].Value = "VW250";
    Sheet.Cells["E1"].Value = "VW270";
    int row = 2;
    foreach (var item in collection)
    {

        Sheet.Cells[string.Format("A{0}", row)].Value = item.Year;
        Sheet.Cells[string.Format("B{0}", row)].Value = item.Week;
        Sheet.Cells[string.Format("C{0}", row)].Value = item.Day;
        Sheet.Cells[string.Format("D{0}", row)].Value = item.VW250;
        Sheet.Cells[string.Format("E{0}", row)].Value = item.VW270;
        row++;
    }


    Sheet.Cells["A:AZ"].AutoFitColumns();
    Response.Clear();
    Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
    Response.AddHeader("content-disposition", "attachment: filename=" + "Report.xlsx");
    Response.BinaryWrite(Ep.GetAsByteArray());
    Response.End();
}