JOIN and LEFT JOIN equivalent in LINQ with method syntax

Question

I am converting an SQL query to LINQ, which creates a 1 to 1 left join, and it should be in the method syntax. I take off my hair trying to do it without a veil. I can do this in Lambda syntax. The following is an example of the query I'm trying to run. They are not the actual code. Will someone point out what I'm doing wrong?

SQL:

SELECT item.*, item_status.* FROM item LEFT JOIN item_status ON item.ID = item_status.itemID AND item_status.FLAGGED = true WHERE item.published_date > "2008-06-19" 

LINQ:

 var linq_query = ( from selected_item in item join selected_item_status in item_status on selected_item.ID equals item_status.itemID into joined from item_status in joined.DefaultIfEmpty() where item_status.FLAGGED = true select new {selected_item, selected_item_status}).ToList(); 

Answer 1

join ... into becomes GroupJoin and the second from becomes SelectMany :

 var linq_query = Item .GroupJoin( item_status.Where(x => x.selected_item_status.FLAGGED), // EDIT: Where clause moved here. selected_item => selected_item.ID, selected_item_status => selected_item_status.itemID, (selected_item, joined) => new { selected_item, statuses = joined.DefaultWithEmpty(), }) .SelectMany(x => x.statuses.Select(selected_item_status => new { x.selected_item, selected_item_status, })) // EDIT: Removed where clause. .ToList(); 

It seems that Where makes the left outer join unnecessary, as in any case, the filtered statuses will be filtered out.

EDIT: No, when viewing SQL, it looks like your LINQ query is a little wrong. It should be:

 var linq_query = ( from selected_item in item join selected_item_status in ( from status in item_status where status.FLAGGED select status) on selected_item.ID equals item_status.itemID into joined from item_status in joined.DefaultIfEmpty() select new {selected_item, selected_item_status}).ToList();