How to extract data correctly using linq sql in asp.net mvc 5?

Question

SELECT TOP (1000) [WeekId] ,[WeekNum] ,[ Year ] ,[CreatedDate] ,[CreatedBy] ,[ModifiedDate] ,[ModifiedBy] ,[InActive] FROM [ProductionManagement].[Schedule].[Week] where WeekNum = 53 order by Year Hi team I need some help and i have a problem the problem, the issue i have my excel data its duplicating a year, week column. e.g if data is available for 2021 its counting on week 2 as 2021 2, 2, 3. How do i fix this issue and must check if there is no data available for a specific year and week. 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 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 ; } }

Rijwan Ansari · Answer

Hi Gcobani, Can you share the table/model structure of both models and also expected outcome of Linq query?

Debarshi Chakraborty · Answer

made the minor change in the STEP 2 STEP 2: //iterate thru the above list and create your entity collection IList view = new List (); ExtractionViewModel v = null ; bool alreadyAdded = false ; foreach (item in list1) { //we need to check if the item is already there in the collection based on the year and week number var alreadyAddedItem = list1.Where(i => i.Year == item.Year && i.Week == item.Week && i.Day == item.Day); if (alreadyAddedItem != null ) { v = alreadyAddedItem; alreadyAdded = true ; } else { v = new ExtractionViewModel(); alreadyAdded = false ; } if (!alreadyAdded) { v.Year = item.Year; v.Week = item.WeekNum; v.Day = item.ProductionDate; } select (item.Name) { case "VW270" : v.VW270 = item.InActive == false ? item.Name : null ; break ; case "VW250" : v.VW250 = item.InActive == false ? item.Name : null ; break ; case "VW270PA" : v.VW270PA = item.InActive == false ? item.Name : null ; break ; case "VW250/2PA" : v.VW2502PA = item.InActive == false ? item.Name : null ; break ; } if (!alreadyAdded) view.Add(v); } return view; IList is an interface and List is a concrete class that uses IList. it should work with the minor change above. now put the whole code Step 1 and Step 2 inside your method public IList GetExtractionViewModels()

Guest User · Answer

Debarshi My method uses IList GetExtractionViewModels public IList GetExtractionViewModels() { // this complies errors. is this a unique method must i create? }

Debarshi Chakraborty · Answer

Check if this helps. You may require to do some changes based on what's not compiling: //declare a variable string[] containing all the models you are looking for: var myModels = new string [] { "VW270" , "VW250" , "VW270PA" , "VW250/2PA" }; STEP 1: //get the data for this models var list1 = (from w in db.Weeks from p in db.ProductionDays on w.WeekId = p.WeekId from m in db.Models on p.ProductionDate = m.CreatedDate where (myModels.Contains(m.Name.ToString().ToUpper())) select { w.Year, w.WeekNum, p.ProductionDate, p.DayOfWeek, m.Name, m.Code, m.InActive }).ToList(); //please make user you are comparing the right casing - lower or upper //also not sure, but plz check if you have to compare Name or Code from Models STEP 2: //iterate thru the above list and create your entity collection List view = new List (); ExtractionViewModel v = null ; bool alreadyAdded = false ; foreach (item in list1) { //we need to check if the item is already there in the collection based on the year and week number var alreadyAddedItem = view.Where(i => i.Year == item.Year && i.Week == item.Week && i.Day == item.Day); if (alreadyAddedItem != null ) { v = alreadyAddedItem; alreadyAdded = true ; } else { v = new ExtractionViewModel(); alreadyAdded = false ; } if (!alreadyAdded) { v.Year = item.Year; v.Week = item.WeekNum; v.Day = item.ProductionDate; } select (item.Name) { case "VW270" : v.VW270 = item.InActive == false ? item.Name : null ; break ; case "VW250" : v.VW250 = item.InActive == false ? item.Name : null ; break ; case "VW270PA" : v.VW270PA = item.InActive == false ? item.Name : null ; break ; case "VW250/2PA" : v.VW2502PA = item.InActive == false ? item.Name : null ; break ; } if (!alreadyAdded) view.Add(v); }

Guest User · Answer

Hi Dershani Do you mind sharing psydo logic of those steps, i can figure it out and give feedback

Guest User · Answer

Hi Debarshi I am stuck with that query, but it should be something like this below var scheduleList = ( from p in db.ProductionDays from m in db.Models join w in db.Weeks on p.WeekId equals w.WeekId join m in db. Name on p.WeekId equals w.WeelId orderby w. year ascending, m. Name descending where (m.Inactive == false ) select new ExtractionViewModel { Year = w.Yer, Day = p.ProductionDate, VW270 = mt. Name }).ToList(); return scheduleList; // Sql for Model SELECT TOP (1000) [ModelId] ,[ Name ] ,[Code] ,[CreatedDate] ,[CreatedBy] ,[ModifiedDate] ,[ModifiedBy] ,[InActive] FROM [ProductionManagement].[Schedule].[Model] where Name = 'VW240' order by InActive

Debarshi Chakraborty · Answer

Can u share the query for Models entity as well, just like u gave for week and production days? I can chk whats to join on Also, u can try this: break your steps into 2. Step 1 : use the linq to get data in the list by joining the three entities. Your data shouls look something like this: { 2021, 36, model1,etc 2021, 35, model2,etc 2021, 35, model3,etc 2021, 34, model4,etc 2021, 33, model4 } Etc. Step 2: now iterate thru the above list and get to arrange the items in your extractionView. I.e. Foreach (item in aboveList) { Yr = item.year; Wk = item.week; Dt = item.etc field; Switch (item.model) Case model1: model1= item.model or count; Case model2: model2= item.model or count; So on }

Guest User · Answer

Derbashi yes

Debarshi Chakraborty · Answer

Hi, i see you are using three entities - productuonDays, Weeks and Models. But you havn't joined the Models entity with the other two. Also, are you looking a output like this ? Yr : week : model1 : model2 : model3 2021 : 34 :. Y. :. : 2021 : 33 :. :. Y. :. Y 2021.: 32.:. Y. :. Y. :. 2021 : 31 :. Y. : Y. :. Y?

Guest User · Answer

//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]

Guest User · Answer

Hi Debarshi The first linq query i can seem to understand, but the problem now im using model to interact with my database record. Therefore if i am using into must now decide which record, to allow defaultIfEmpty() method.

Debarshi Chakraborty · Answer

Start with the linq object of weeks and put other details in the left join . i.e. if there is no data for a (year + week) combination, then it should return you empty row. from c in categories join p in products on c equals p.Category into ps from p in ps.DefaultIfEmpty() The DefaultIfEmpty() makes it Left outer join. Re create your query using the above tip. It should probably fix the first issue. Incase not, then to get the distinct (year+week) combination, you can use the groupBy clause. Below is an example. var distinctYrWeekSet = totalRecordSet.GroupBy(item => new { item.Year, item.Week }).Select(group => group.First()).ToList();