CODEWITHSUNDEEP
c#mysqllinqjoinlambda
NeoSketo
NeoSketo

Reputation: 525

Linq Lambda join breaks when joining a table

I am fairly new to lambda. I am trying to create a join that pulls products from my db and joins a table of authors. But some products from my db doesn't have authors such as product packages. Because of this, the query doesn't pull those records.

Is there a way to pull records of all products that either have or don't have authors in linq lambda. Similar to inner joins or right joins? Here my db/query:

DATABASE

Products

+-----------------------------------------+--------+----+---------+
|                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 |
+--------+----+----------+

Authors

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

QUERY

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

This gives me a result like this:

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

when this is what i am trying to accomplish

+-----------------------------------------+----------+----+---------+------------------+
|                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             |
+-----------------------------------------+----------+----+---------+------------------+

Looks like that since the package doesnt contain 1 author record it just ignored it. Any help is greatly appreciated.

UPDATE

Sorry, i forgot to mention that i am using NHibernate. Therefore groupjoins are not implemented :(

Upvotes: 0

Views: 335

Answers (1)

Xiaoy312
Xiaoy312

Reputation: 14477

Here is the query in lambda expression method syntax : 

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

Output : 

+--------------------------------+--------+----+---------+------------------+
|            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                      |        |    |         |                  |
+--------------------------------+--------+----+---------+------------------+

Upvotes: 1

Related Questions