JOIN and LEFT JOIN equivalent in LINQ with Method Syntax

entity-framework, linq, sql

Solution

The `join ... into` becomes a `GroupJoin` and the second `from` becomes a `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 looks like the `Where` makes the left outer join unnecessary, as null statuses will be filtered out anyway.

EDIT: No, upon reviewing the SQL it looks like your LINQ query is slightly incorrect. 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();

Problem

I am converting a SQL query to LINQ that creates a left join with 1-to-1 mapping, and it has to be in Method Syntax. I have been pulling off my hair trying to accomplish this to no veil. I can do it in Lambda Syntax. Below is the example query I am trying to run. They are not actual code. Would someone point out what I am 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(); ```

Original source