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

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

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()

Debarshi

My method uses IList GetExtractionViewModels

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

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);
}

Hi Dershani

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

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

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 }

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?

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

Hi Gcobani,

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

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.

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();

12 Replies

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

Debarshi Chakraborty5y ago
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 User5y ago
Debarshi

My method uses IList GetExtractionViewModels
public IList GetExtractionViewModels() {
// this complies errors. is this a unique method must i create?
}
Debarshi Chakraborty5y ago
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 User5y ago
Hi Dershani

Do you mind sharing psydo logic of those steps, i can figure it out and give feedback
Guest User5y ago
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 Chakraborty5y ago
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 User5y ago
Derbashi yes
Debarshi Chakraborty5y ago
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 User5y ago
1. //Week table
3. SELECT TOP (1000) [WeekId]
4. ,[WeekNum]
5. ,[Year]
6. ,[CreatedDate]
7. ,[CreatedBy]
8. ,[ModifiedDate]
9. ,[ModifiedBy]
10. ,[InActive]
11. FROM [ProductionManagement].[Schedule].[Week]
13. // ProductionDate
14. SELECT TOP (1000) [ProductionDayId]
15. ,[WeekId]
16. ,[ProductionDate]
17. ,[DayOfWeek]
18. ,[CreatedDate]
19. ,[CreatedBy]
20. ,[ModifiedDate]
21. ,[ModifiedBy]
22. ,[InActive]
23. FROM [ProductionManagement].[Schedule].[ProductionDay]
Rijwan Ansari5y ago
Hi Gcobani,

Can you share the table/model structure of both models and also expected outcome of Linq query?
Guest User5y ago
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 Chakraborty5y ago
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();