Click here to Skip to main content
14,937,943 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
Hi Team

I am struggling to join 3 different tables by using linq in c#, the idea is i want to avoid having duplicate records of values of my filtering. Kindly please assist me, thanks.

What I have tried:

public IList<ExtractionViewModel> GetExtractionViewModels(string year, int week)
        {

            ProductionManagementEntities db = new ProductionManagementEntities();


            var scheduleList = (from p in db.ProductionDays
                                join w in db.Weeks
                                on p.WeekId equals w.WeekId

                                orderby w.Year ascending
                                orderby w.WeekNum ascending


                                where (w.WeekNum == 5)

                                select new ExtractionViewModel
                                {

                                    Day = p.ProductionDate,
                                    Week = w.WeekNum,

                                    // Model = m.Name,
                                    Year = w.Year

                                }).ToList();
            return scheduleList;
        }


SELECT TOP (1000) [ModelId]
      ,[Name]
      ,[Code]
      ,[CreatedDate]
      ,[CreatedBy]
      ,[ModifiedDate]
      ,[ModifiedBy]
      ,[InActive]
  FROM [ProductionManagement].[Schedule].[Model]


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


SELECT TOP (1000) [ProductionDayId]
      ,[WeekId]
      ,[ProductionDate]
      ,[DayOfWeek]
      ,[CreatedDate]
      ,[CreatedBy]
      ,[ModifiedDate]
      ,[ModifiedBy]
      ,[InActive]
  FROM [ProductionManagement].[Schedule].[ProductionDay]
Posted
Updated 28-Apr-21 16:36pm
Comments
Wendelius 28-Apr-21 12:07pm
   
How Model is related to Week or ProductionDay?
Gcobani Mkontwana 2021 28-Apr-21 12:52pm
   
Week
Wendelius 28-Apr-21 12:58pm
   
What I mean is, what is the column that joins the rows in Model and Week? As far as I can see, there's no WeekId nor ProductionDayId
Gcobani Mkontwana 2021 28-Apr-21 13:39pm
   
On the query for ProductionDay and Week table there are ID's
Wendelius 28-Apr-21 22:02pm
   
Yes, those tables contain the id's but how should Model be joined?

Can you post some example data from the tables and the result you would like to have?

1 solution

Seems that a reposted question (Joining 3 table in linq is it legal in C#[^] ) contains information how Model and ProuctionDay are related, In other words
p.ProductionDate =  m.CreatedDate

Based on that information, one simple way could be to split the operations to two parts. Something like
C#
var dayResult = from w in db.Weeks  
                join p in db.ProductionDays on w.WeekId equals p.WeekId  
                select new {
                   w.Year,  
                   w.WeekNum,  
                   p.ProductionDate,  
                   p.DayOfWeek
                };

var actualResult = from item in dayResult
                   join m in db.Models on p.ProductionDate equals item.CreatedDate 
                   where  --- //other conditions here
                   select new {  
                      item.Year,  
                      item.WeekNum,  
                      item.ProductionDate,  
                      item.DayOfWeek,  
                      m.Name,  
                      m.Code,  
                      m.InActive  
                   };


Another option could be to try to combine the queries by creating intermediate results using into. Something like
C#
var result = from w in db.Weeks  
             join p in db.ProductionDays on w.WeekId equals p.WeekId into res1
             from item in res1
             join m in db.Models on p.ProductionDate equals item.CreatedDate 
             where  --- //other conditions here
             select new {  
                w.Year,  
                w.WeekNum,  
                p.ProductionDate,  
                p.DayOfWeek,  
                m.Name,  
                m.Code,  
                m.InActive  
             };
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900