LINQ to Entities 左外连接

Question

LINQ to Entities 左外连接

4

我正在苦恼于linq to entities左连接的问题。我有两个实体（表）：

Listings 
{
    ListingID,
    MakeID (nullable)
}

Makes
{
    MakeID,
    Description
}

我想用LINQ编写类似于以下内容的代码：

select listings.listingID
,listings.makeid
, IsNull(makes.Description, 'NA')
from listings
left outer join makes
on listings.makeid = makes.makeid

- Parminder

我还没有将这两个表连接起来。我能否在不连接它们的情况下选择数据？ - Parminder

4个回答

2

任何告诉你在LINQ to Entities中使用.DefaultIfEmpty()作为外连接的人都没有真正尝试过！它根本不起作用-至少在.NET 3.5 SP1上是如此。这位博主告诉你应该如何实际操作。基本上，.NET默认情况下在LINQ to Entities中执行外连接，因此您应该省略.DefaultIfEmpty()。对于多个外连接，您必须嵌套查询组以保持其上下文清晰。

- Tian van Heerden

0

我现在不在开发机前面，无法确认，但或许是这样的吧？

var x = from l in listings
    join m in makes on l.makeid equals m.makeid into g
    from ma in g.DefaultIfEmpty()
    select new 
    {
        l.listingID, 
        l.makeid, 
        (ma.Description == null ? "NA" : ma.Description)
    };

如果你有任何问题，请告诉我，我会在我的工作电脑上检查。

- Frank Tzanabetis

0

http://oddiandeveloper.blogspot.com/2008/12/testable-left-outer-join-in-linq-to.html

我之前写过一篇博客文章，也许会对你有帮助。这篇文章会涉及到测试性的问题，建议参考一下。

同时，在生成实体模型时，请确保外键已经设置好，这将有助于建立依赖关系。

- Odd

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Seravy · Accepted Answer

以下是实现左连接的解决方案。在其他资源方面，我非常推荐尝试使用Linq Pad：http://www.linqpad.net/ 它是学习Linq的绝佳工具。

// Listing class/container/table
public class Listing
{
    public string ListingID {get;set;}
    public Int32? MakeID {get;set;}
}

// Make class/container/table
public class Make
{
    public Int32 MakeID {get;set;}
    public string Description {get;set;}
}

public class Main
{
    public static void LinqMain()
    {
        // Populate the listing table with data
        List<Listing> listings = new List<Listing>()
        {
            new Listing() { ListingID = "Test 1", MakeID = 1 },
            new Listing() { ListingID = "Test 2", MakeID = 1 },
            new Listing() { ListingID = "No Make", MakeID = null },
            new Listing() { ListingID = "Test 3", MakeID = 3 },
            new Listing() { ListingID = "Another Makeless", MakeID = null }
        };

        // Populate the makes table with data
        List<Make> makes = new List<Make>()
        {
            new Make() { MakeID = 1, Description = "Make 1"},
            new Make() { MakeID = 2, Description = "Make 2"},
            new Make() { MakeID = 3, Description = "Make 3"},
            new Make() { MakeID = 4, Description = "Make 4"}
        };

        // Return the left join on Make Id
        var result = from l in listings

                     // These two lines are the left join. 
                     join leftm in makes on l.MakeID equals leftm.MakeID into leftm
                     from m in leftm.DefaultIfEmpty()

                     // To ensure the select does not get bogged down with too much logic use the let syntax
                     let description = m == null ? "NA" : m.Description

                     select new { l.ListingID, l.MakeID, description };


    }

结果变量将包含：

{ ListingID = "测试1", MakeID = 1, 描述 = "制造商1" }
{ ListingID = "测试2", MakeID = 1, 描述 = "制造商1" }
{ ListingID = "无制造商", MakeID = null, 描述 = "NA" }
{ ListingID = "测试3", MakeID = 3, 描述 = "制造商3" }
{ ListingID = "另一个无制造商", MakeID = null, 描述 = "NA" }