linq to entites 左外连接

发布于 2024-08-19 11:16:20 字数 351 浏览 5 评论 0原文

我正在努力 linq 到实体左外连接。我有两个实体(表):

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

I am struggling linq to entities left outer join. I have two entities (tables):

Listings 
{
    ListingID,
    MakeID (nullable)
}

Makes
{
    MakeID,
    Description
}

I want to write something like this in LINQ:

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

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(4

陌伤浅笑 2024-08-26 11:16:20

以下是实现左连接的解决方案。就其他资源而言,我真的建议尝试 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 };


    }

结果变量将包含:

  1. { ListingID = "Test 1", MakeID = 1, description = "Make 1" }
  2. { ListingID = "Test 2", MakeID = 1, description = "Make 1" }
  3. { ListingID = "No Make ", MakeID = null, 描述 = "NA" }
  4. { ListingID = "测试 3", MakeID = 3, 描述 = "Make 3" }
  5. { ListingID = "另一个 Makeless", MakeID = null, 描述 = "NA" }

Below is your solution to achieving a left join. In terms of other resources I really recommend trying out linq pad: http://www.linqpad.net/ It is a great learning tool for 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 };


    }

The result variable would contain:

  1. { ListingID = "Test 1", MakeID = 1, description = "Make 1" }
  2. { ListingID = "Test 2", MakeID = 1, description = "Make 1" }
  3. { ListingID = "No Make", MakeID = null, description = "NA" }
  4. { ListingID = "Test 3", MakeID = 3, description = "Make 3" }
  5. { ListingID = "Another Makeless", MakeID = null, description = "NA" }
对不⑦ 2024-08-26 11:16:20

任何告诉您使用 .DefaultIfEmpty() 作为 LINQ to Entities 中外部联接的一部分的人实际上都没有尝试过!根本不起作用 - 至少在 .NET 3.5 SP1 上是这样。

此博主告诉您如何实际上应该这样做。本质上,.NET 默认情况下在 LINQ to Entities 中执行外部联接,因此您应该省略 .DefaultIfEmpty()。对于多个外连接,您必须嵌套查询组以保持其上下文清晰。

Anybody who tells you to use .DefaultIfEmpty() as part of an outer join in LINQ to Entities hasn't actually tried it themselves! Tt simply does not work - at least as at .NET 3.5 SP1.

This blogger tells you how you should actually do it. Essentially, .NET does outer joins in LINQ to Entities by default, so you should leave out the .DefaultIfEmpty(). For multiple outer joins you have to nest the query groups to keep their context clear.

世界等同你 2024-08-26 11:16:20

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

这应该有帮助,这是我不久前发表的一篇博客文章,应该仍然相关,并且也可能有助于可测试性。

生成实体模型时还要确保外键就位,这将有助于设置依赖项。

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

This should help, it's a blog post I made a while ago, should still be relevant and might help with testability as well.

Also make sure your foreign keys are in place when you generate your entity model, it'll help setup your dependencies.

半窗疏影 2024-08-26 11:16:20

不是在开发机器前检查,但也许是这样的?

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

如果您遇到任何问题,请告诉我,我会检查我的工作电脑。

Not infront of a dev machine to check, but something like this perhaps?

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

If you have any trouble with it, let me know and i'll check on my work pc.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文