Blazor CSLA 7.0 Help on how to write Lamda two left outer joins

[jhuerta3]

Hello all,

I was hoping someone could help me with writing the EF expression below. Im trying to perform two left outer joins to get the name of the user who created the notes and put the item on hold hold for the BookChange. It works when I only use one .GroupJoin expression but when I add the other one I get an error.

public Task<List<BookChangeEntity>> GetAsync(string pPchgDate, string pEngID)
{
    var data = _context.BookChanges
        .GroupJoin(_context.LookUpUsers, u1 => u1.NotesBy, u => u.UserName, (u1, u) => new { u1, u })
        .GroupJoin(_context.LookUpUsers, u2 => u2.u1.HoldBy, h => h.UserName, (u2, h) => new { u2, h })
        .Where
           (x =>
           (x.u2.u1.PchgDate == PchgDate && x.u2.u1.EngID == pEngID)
           )
        .Select(s => new BookChangeEntity
        {
            BookChangeID = s.u2.u1.BookChangeID
        ,
            PchgDate = s.u2.u1.PchgDate
        ,
            EngID = s.u2.u1.EngID
        ,
            HoldBy = s.u2.u1.HoldBy
        ,
            HoldByFullName = s.h.FullName
        ,
            HoldDate = s.u2.u1.HoldDate
        ,
            Notes = s.u2.u1.Notes
        ,
            NotesBy = s.u2.u1.NotesBy
        ,
            NotesByFullName = s.u2.u.FullName
        ,
            NotesDate = s.u2.u1.NotesDate

        }).ToList();

    if (data != null)
        return Task.FromResult(data);
    else
        throw new KeyNotFoundException($"tPchgDate {pPchgDate}");
}

[kcabral817]

Here is copilot's suggestion:
public Task<List> GetAsync(string pchgDate, string engID)
{
var data = _context.BookChanges
.Join(_context.LookUpUsers, u1 => u1.NotesBy, u => u.UserName, (u1, u) => new { u1, u })
.Join(_context.LookUpUsers, u2 => u2.u1.HoldBy, h => h.UserName, (u2, h) => new { u2, h })
.Where(x => x.u2.u1.PchgDate == pchgDate && x.u2.u1.EngID == engID)
.Select(s => new BookChangeEntity
{
BookChangeID = s.u2.u1.BookChangeID,
PchgDate = s.u2.u1.PchgDate,
EngID = s.u2.u1.EngID,
HoldBy = s.u2.u1.HoldBy,
HoldByFullName = s.h.FullName,
HoldDate = s.u2.u1.HoldDate,
Notes = s.u2.u1.Notes,
NotesBy = s.u2.u1.NotesBy,
NotesByFullName = s.u2.u.FullName,
NotesDate = s.u2.u1.NotesDate
}).ToList();

return data != null ? Task.FromResult(data) : Task.FromResult(new List<BookChangeEntity>());

}

[jhuerta3]

Hello @kcabral817 first of all thank you for responding. Yes this works but the ".Join" syntax turns to "Inner join". I was looking for "outer join" . The outer join works for me only one If I add one line
.GroupJoin(_context.LookUpUsers, u1 => u1.NotesBy, u => u.UserName, (u1, u) => new { u1, u })

If I try to add the other line it does not work.

[jhuerta3]

I found the solution. I will post tomorrow. Dang it took me a while to find this.

[Chicagoan2016]

@jhuerta3 I am glad you found the solution, while reading this I realized one of the reasons I didn't learn EF was my (extreme) laziness, outer join in SQL is easy for someone like me lol

[jhuerta3]

Hello All,
Here is the solution to Lamda multiple outer join syntax. This has two joins to get who enter the notes and who put the record on hold.

public Task GetAsync(string pchgDate, string pEngID)
{
var data = _context.BookChanges
.GroupJoin(_context.LookUpUsers, p => p.NotesBy, n => n.UserName, (p, n) => new { p, n })
.SelectMany(p => p.n.DefaultIfEmpty(), (p, n) => new { p.p, n })
.GroupJoin(_context.LookUpUsers, pn => pn.p.HoldBy, h => h.UserName, (pn, h) => new { pn, h })
.SelectMany(pnh => pnh.h.DefaultIfEmpty(), (pnh, h) => new { pnh,h})
.Where(x => x.pnh.pn.p.PchgDate == pPchgDate && x.pnh.pn.p.EngID == pEngID)
.Select(s => new BookChangeEntity
{
BookChangeID = s.pnh.pn.p.BookChangeID,
PchgDate = s.pnh.pn.p.PchgDate,
EngID = s.pnh.pn.p.EngID,
HoldBy = s.pnh.pn.p.HoldBy,
HoldByFullName = s.h.FullName,
HoldDate = s.pnh.pn.p.HoldDate,
Notes = s.pnh.pn.p.Notes,
NotesBy = s.pnh.pn.p.NotesBy,
NotesByFullName = s.pnh.pn.n.FullName,
NotesDate = s.pnh.pn.p.NotesDate
}).ToList();