sql - 具有方法语法的 LINQ 中的 JOIN 和 LEFT JOIN 等效

Question

我正在将一个 SQL 查询转换为 LINQ，它创建一个具有 1 对 1 映射的左连接，并且它必须在方法语法中。我一直在拔头发，试图做到这一点。我可以在 Lambda 语法中做到这一点。下面是我尝试运行的示例查询。它们不是实际的代码。有人会指出我做错了什么吗？

查询语句:

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"

林克:

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变成 GroupJoin第二个 from变成 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();

它看起来像 Where使左外连接变得不必要，因为无论如何都会过滤掉空状态。

编辑:不，在查看 SQL 时，您的 LINQ 查询看起来有点不正确。它应该是:

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();