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

Answer 1

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 [e-mail].

Answer 2

Kyle,

thanks. it works.