How to use linq to join 3 tables in C#?

Question

1.00/5 (1 vote)

See more:

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 28-Apr-21 5:56am

Gcobani Mkontwana 2021

Updated 28-Apr-21 16:36pm

Add a Solution

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Wendelius · Answer 1 · 2021-04-28T16:36:00

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