none
problem with left join on select RRS feed

  • 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.

    • Marked as answer by Pacoss Monday, February 25, 2019 2:02 PM
    • Unmarked as answer by Pacoss Monday, February 25, 2019 2:02 PM
    • Marked as answer by Pacoss Monday, February 25, 2019 2:03 PM
    Friday, February 22, 2019 8:58 AM
    Moderator

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.

    • Marked as answer by Pacoss Monday, February 25, 2019 2:02 PM
    • Unmarked as answer by Pacoss Monday, February 25, 2019 2:02 PM
    • Marked as answer by Pacoss Monday, February 25, 2019 2:03 PM
    Friday, February 22, 2019 8:58 AM
    Moderator
  • Kyle,

    thanks. it works.

    Monday, February 25, 2019 2:03 PM