加入表时，Linq-Lambda连接中断

本文关键字：Linq-Lambda 连接 中断 | 更新日期: 2023-09-27 17:57:33

我对lambda相当陌生。我正试图创建一个连接，从我的数据库中提取产品并连接一个作者表。但是我数据库中的一些产品没有作者，比如产品包。因此，查询不会提取这些记录。

有没有一种方法可以提取linqlambda中有或没有作者的所有产品的记录。类似于内联接还是右联接？这里是我的数据库/查询：

数据库

产品

+-----------------------------------------+--------+----+---------+
|                FullName                 | TypeId | Id |   Sku   |
+-----------------------------------------+--------+----+---------+
| The Matrix                              |      1 | 23 | MAT     |
| Lord Of The Rings                       |      1 | 22 | LOTR    |
| Package: Lord of the rings & The Matrix |      2 | 33 | LOTRMAT |
+-----------------------------------------+--------+----+---------+

AuthorAssignments

+--------+----+----------+
| TypeId | Id | AuthorId |
+--------+----+----------+
|      1 | 23 |        1 |
|      1 | 22 |        2 |
+--------+----+----------+

作者

+----------+------------------+
| AuthorId |      Author      |
+----------+------------------+
|        1 | The Wachowskis   |
|        2 | J. R. R. Tolkien |
+----------+------------------+

查询

        var allitems = _contentService.Products.Select(
            x => new {x.FullName, x.TypeId, x.Id, x.Sku})
            .Join(
                _contentService.AuthorAssignments,
                x => new {x.TypeId, x.Id},
                y => new {y.TypeId, y.Id},
                (x, y) =>
                    new
                    {
                        x.Sku,
                        x.FullName,
                        x.Id,
                        x.TypeId,
                        y.AuthorId
                    })
            .Join(
                _contentService.Authors,
                authId => authId.AuthorId ,
                auth => auth.Id,
                (authId, auth) =>
                    new
                    {
                        authId.Sku,
                        authId.FullName,
                        authId.Id,
                        authId.TypeId,
                        authId.Image,
                        auth.Author
                    });

这给了我这样的结果：

+-------------------+--------+----+------+------------------+
|     FullName      | TypeId | Id | Sku  |      Author      |
+-------------------+--------+----+------+------------------+
| The Matrix        |      1 | 23 | MAT  | The Wachowskis   |
| Lord Of The Rings |      1 | 22 | LOTR | J. R. R. Tolkien |
+-------------------+--------+----+------+------------------+

当这就是我试图实现的时候

+-----------------------------------------+----------+----+---------+------------------+
|                FullName                 |   TypeId | Id |   Sku   |      Author      |
+-----------------------------------------+----------+----+---------+------------------+
| The Matrix                              |        1 | 23 | MAT     | The Wachowskis   |
| Lord Of The Rings                       |        1 | 22 | LOTR    | J. R. R. Tolkien |
| Package: Lord of the rings & The Matrix |        2 | 33 | LOTRMAT | null             |
+-----------------------------------------+----------+----+---------+------------------+

看起来，由于包中不包含1个作者记录，它只是忽略了它。任何帮助都将不胜感激。

更新

对不起，我忘了提到我正在使用NHibernate。因此，不实现组联接：（

加入表时，Linq-Lambda连接中断

以下是lambda表达式方法语法中的查询：

var allitems = _contentService.Products
    .Select(x => new {x.FullName, x.TypeId, x.Id, x.Sku})
    .GroupJoin(_contentService.AuthorAssignments,
        p => p.Id,
        aa => aa.Id,
        (p, aa) => new 
        { 
            p.Sku, 
            p.FullName, 
            p.Id, 
            p.TypeId, 
            AuthorId = aa.Select(x => x.AuthorId).FirstOrDefault()
        })
    .GroupJoin(_contentService.Authors,
        p => p.AuthorId,
        a => a.Id,
        (p, a) => new 
        { 
            p.FullName, 
            p.TypeId, 
            p.Id,
            p.Sku, 
            Author = a.Select(x => x.Author).FirstOrDefault()
        });

输出：

+--------------------------------+--------+----+---------+------------------+
|            FULLNAME            | TYPEID | ID |   SKU   |      AUTHOR      |
+--------------------------------+--------+----+---------+------------------+
|                                |        |    |         |                  |
| The Matrix                     | 1      | 23 | MAT     | The Wachowskis   |
|                                |        |    |         |                  |
| Lord Of The Rings              | 1      | 22 | LOTR    | J. R. R. Tolkien |
|                                |        |    |         |                  |
| Package: Lord of the rings & T | 2      | 33 | LOTRMAT | null             |
| he Matrix                      |        |    |         |                  |
+--------------------------------+--------+----+---------+------------------+
相关文章：