Answered by:
problem with left join on select

Question
-
hi,
i have this sql query which i want to put into linq:
SELECT p.PID, f.ID, ft.[Name], p.LastName, et.[Status], a.Street, ptt.PostalCode, s.[SettlementName], t.[TownName], c.[CountyName], InputDocument.DateInput, f.DateEnd FROM Farm.Farm AS f INNER JOIN Farm.DCFarmType AS ft ON ft.ID = f.FarmTypeID INNER JOIN Farm.Employee AS e ON e.FarmID = f.ID INNER JOIN Farm.DCEmployeType AS et ON et.ID = e.EmployeeTypeID INNER JOIN Person.Person AS p ON p.ID = e.PersonID INNER JOIN Structure.Adress AS a ON a.ID = p.AdressID INNER JOIN Structure.DCSettlement AS s ON s.ID = a.SettlementID INNER JOIN Structure.DCPtt AS ptt ON ptt.ID = s.PttID INNER JOIN Structure.DCTown AS t ON t.ID = s.TownID INNER JOIN Structure.DCCounty c ON c.ID = t.CountyID LEFT JOIN ( SELECT FarmID, MIN(DateInput) AS DateInput FROM DOCUMENT.Document doc WHERE DocumentTypeID = 1 AND doc.Active = 1 GROUP BY FarmID ) AS InputDocument ON InputDocument.FarmID = f.ID WHERE p.PID = '853' AND et.ID = 1 AND e.IsLastOwner = CAST(1 AS BIT)
the main problem for me is how to write "left join (select ...)" in linq.
i have done it so far:
from f in _context.Farm join ft in _context.DCFarmType on f.FarmTypeID equals ft.ID join e in _context.Employee on f.ID equals e.FarmID join et in _context.DCEmployeType on e.EmployeeTypeID equals et.ID join p in _context.Person on e.PersonID equals p.ID join a in _context.Adress on p.AdressID equals a.ID join s in _context.DCSettlement on a.SettlementID equals s.ID join ptt in _context.DCPtt on s.PttID equals ptt.ID join t in _context.DCTown on s.TownID equals t.ID join c in _context.DCCounty on t.CountyID equals c.ID join d in _context.Document on f.ID equals d.FarmID into DocJoin from d1 in DocJoin.DefaultIfEmpty() .... where p.PID == 853 && et.ID == 1 select new { ID = f.ID, Name = ft.Name, LastName = p.LastName, Status = et.Status, Street = a.Street, PostalCode = ptt.PostalCode, SettlementName = s.SettlementName, TownName = t.TownName, CountyName = c.CountyName, DateInput = d.DateInput, DateEnd = f.DateEnd }
any help is appreciated.
thanks
- Edited by Pacoss Thursday, February 21, 2019 1:23 PM
Wednesday, February 20, 2019 10:17 AM
Answers
-
Hi Pacoss,
You can execute the internal subquery LINQ statement first.
var DocumentSelect = from doc in DOCUMENT.Document where DocumentTypeID = 1 where doc.Active = 1 group doc by FarmID select new {FarmID, MIN(DateInput)}
And then modify the LINQ like this:
join d in DocumentSelect on f.ID equals d.FarmID into DocJoin
Regards,
Kyle
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.Friday, February 22, 2019 8:58 AM
All replies
-
Hi Pacoss,
You can execute the internal subquery LINQ statement first.
var DocumentSelect = from doc in DOCUMENT.Document where DocumentTypeID = 1 where doc.Active = 1 group doc by FarmID select new {FarmID, MIN(DateInput)}
And then modify the LINQ like this:
join d in DocumentSelect on f.ID equals d.FarmID into DocJoin
Regards,
Kyle
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.Friday, February 22, 2019 8:58 AM -
Kyle,
thanks. it works.
Monday, February 25, 2019 2:03 PM