Formatting of excel data to correspond to your table records

Guest User

Hi Team

I have a method that does linq in sql to retrieve data and currently it does. But the issue my records on excel when downloaded are duplicating and no logic if there is no records. When i ran query on the db records, year and weekNum i dont get but my mvc application is duplicating this and need some help.

public IList GetExtractionViewModels()
{
int Count;
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.CreatedDate descending, w.WeekNum descending, mt.Name descending, mx.Name descending, mv.Name descending
// Main problem is calling of these record to get correct data prior to the records when doing a sql query to the db. Yet no records logic cater for if there are not any(year, week)
where (mx.InActive == true)
where (mt.InActive == false)
where(m.InActive == false)
where(mv.InActive == false)
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.CreatedDate,
Week = w.WeekNum,
VW270 = mt.Name,
VW270PA = m.Name,
VW250 = mx.Name,
VW2502PA = mv.Name,
}).ToList();
return scheduleList;
}

SELECT TOP (1000) [WeekId]
,[WeekNum]
,[Year]
,[CreatedDate]
,[CreatedBy]
,[ModifiedDate]
,[ModifiedBy]
,[InActive]
FROM [ProductionManagement].[Schedule].[Week]
where WeekNum = 53
order by YEAR
SELECT TOP (1000) [ProductionDayId]
,[WeekId]
,[ProductionDate]
,[DayOfWeek]
,[CreatedDate]
,[CreatedBy]
,[ModifiedDate]
,[ModifiedBy]
,[InActive]
FROM [ProductionManagement].[Schedule].[ProductionDay]
Order by ProductionDate

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Rijwan Ansari5y ago
Hi,

Don't return query, you need to format into ExtractionViewModel list in for loop.
List extractionViewModels = new List();
ExtractionViewModel extractionViewModel;
foreach (var item in query)
{
       /// other select and form required list.
//form model based on query and other data

extractionViewModel = new ExtractionViewModel();
extractionViewModel.Year = item.Year;
//likewise other columns/Properties

extractionViewModels.Add(extractionViewModel)




}

Rertun extractionViewModels;
0
Guest User5y ago
Hi Ansari
The return object on this case its query? or scheduleList;?
public IList GetExtractionViewModels()
   {

       ProductionManagementEntities db = new ProductionManagementEntities();

       var schedueList = (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
                          select  new {

                   });
       var query = (from p in db.ProductionDays
                    join w in db.Weeks on p.WeekId equals w.WeekId
                    orderby w.Year descending, p.CreatedDate descending, w.WeekNum descending
                 select new { w.Year, p.CreatedDate, w.WeekNum });

       foreach(var item in query)
       {

       }

       return query;// complaining getting an error
   }
0
Rijwan Ansari5y ago
Hi,

Would you please keep these select query seperate from main query if there is no relation. I can oserve that there is no relation
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
Keep these selection seperate.
Make is simple like this:
var query= (from p in db.ProductionDays
                       join w in db.Weeks on p.WeekId equals w.WeekId
                       orderby w.Year descending , p.CreatedDate descending, w.WeekNum descending
                     Select new { w.Year, p.CreatedDate, w.WeekNum };

Then do other selection separately based on where condition and merge in last like
foreach (var item in query)
{
       /// other select and form required list.
}
You can give a try like this.
+1