Doing Left Join with 3 tables using EF Core - multiple on condition

Question

Doing Left Join with 3 tables using EF Core - multiple on condition

Kalpana 291

Hi,

This is my sql query.

SELECT  [ID],[MachineName], b.Target_Date, b.Job_Name1, c.StopReasonStart, c.StopReasonEnd
  FROM [OEEDashboard].[dbo].[Machine] a
  left join AllMachines b
    on a.ID = b.MachineID 
    left join OeeDetailsAll c
	on a.ID =c.OeeMachine and b.Target_Date = CAST(c.StopReasonStart as date)
  where b.Target_Date = '2023-10-05' 
  order by a.ID, c.StopReasonStart

How can I get the ef core to return this query, I tried this.

 var emp = from a in _dbContext.Machines
                      join b in _dbContext.AllMachines
                      on  a.Id equals b.MachineId 
                      join c in _dbContext.OeeDetailsAlls
                      on a.Id equals c.OeeMachine
                      
                      select new 
                      {
                         EntA = a, EntB = b
                      };

but I am a bit lost on including the last ON condition in it . This specific line into the ef core query. Kindly help.

	on a.ID =c.OeeMachine and b.Target_Date = CAST(c.StopReasonStart as date)

Kalpana 291

Hi @Viorel

It works..thank you so much. In fact, I have learnt the linq..as well. Thank you for not giving me the direct answer, it help me to google and fix the error..anyway this is the working code..Hope it helps someone.

var query = from a in _dbContext.Machines
                        join b in _dbContext.AllMachines
                        on a.Id equals b.MachineId into ab_jointable
                        from columns in ab_jointable.DefaultIfEmpty()
                        join c in _dbContext.OeeDetailsAlls
                        on new {a.Id, columns.TargetDate}
                        equals
                        new { Id = c.OeeMachine, TargetDate = c.StopReasonStart.Date} into ac_jointable
                        from sectable in ac_jointable.DefaultIfEmpty()
                        where columns.TargetDate == DateTime.Parse("2023-10-05") 
                        select new
                        {
                            ID = a.Id,
                            MachineName = a.MachineName,
                            Target_Date = columns.TargetDate,
                            JobName1 = columns.JobName1,
                            StopReasonStart  = sectable.StopReasonStart.ToString() == null ? "NoValue" : sectable.StopReasonStart.ToString()
        };

var result = query.ToList();

Translated to the SQL...

SELECT [m].[ID], [m].[MachineName], [a].[Target_Date], [a].[Job_Name1] AS [JobName1], CASE
    WHEN [o].[StopReasonStart] IS NULL THEN N'NoValue'
    ELSE CONVERT(varchar(100), [o].[StopReasonStart])
END AS [StopReasonStart]
FROM [Machine] AS [m]
LEFT JOIN [AllMachines] AS [a] ON [m].[ID] = [a].[MachineID]
LEFT JOIN [OeeDetailsAll] AS [o] ON [m].[ID] = [o].[OeeMachine] AND [a].[Target_Date] = CONVERT(date, [o].[StopReasonStart])
WHERE [a].[Target_Date] = '2023-10-05'

Viorel 122.5K Reputation points

2023-10-11T09:06:39.9133333+00:00

(Since it helped, the comment was converted to answer).

Accepted answer

0 additional answers

Your answer

Kalpana 291 Reputation points

2023-10-11T06:16:10.68+00:00

Hi @Viorel

It works..thank you so much. In fact, I have learnt the linq..as well. Thank you for not giving me the direct answer, it help me to google and fix the error..anyway this is the working code..Hope it helps someone.

var query = from a in _dbContext.Machines join b in _dbContext.AllMachines on a.Id equals b.MachineId into ab_jointable from columns in ab_jointable.DefaultIfEmpty() join c in _dbContext.OeeDetailsAlls on new {a.Id, columns.TargetDate} equals new { Id = c.OeeMachine, TargetDate = c.StopReasonStart.Date} into ac_jointable from sectable in ac_jointable.DefaultIfEmpty() where columns.TargetDate == DateTime.Parse("2023-10-05") select new { ID = a.Id, MachineName = a.MachineName, Target_Date = columns.TargetDate, JobName1 = columns.JobName1, StopReasonStart = sectable.StopReasonStart.ToString() == null ? "NoValue" : sectable.StopReasonStart.ToString() }; var result = query.ToList();

Translated to the SQL...

SELECT [m].[ID], [m].[MachineName], [a].[Target_Date], [a].[Job_Name1] AS [JobName1], CASE WHEN [o].[StopReasonStart] IS NULL THEN N'NoValue' ELSE CONVERT(varchar(100), [o].[StopReasonStart]) END AS [StopReasonStart] FROM [Machine] AS [m] LEFT JOIN [AllMachines] AS [a] ON [m].[ID] = [a].[MachineID] LEFT JOIN [OeeDetailsAll] AS [o] ON [m].[ID] = [o].[OeeMachine] AND [a].[Target_Date] = CONVERT(date, [o].[StopReasonStart]) WHERE [a].[Target_Date] = '2023-10-05'
Viorel 122.5K Reputation points

2023-10-11T09:06:39.9133333+00:00

(Since it helped, the comment was converted to answer).

Answer 1

Viorel 122.5K

Check if this works: on a.Id equals c.OeeMachine where b.Target_Date == c.StopReasonStart.Date.

Or maybe: on new { a.Id, b.Target_Date } equals new { Id = c.OeeMachine, Target_Date = c.StopReasonStart.Date }.

Share via

Doing Left Join with 3 tables using EF Core - multiple on condition

0 additional answers

Your answer