Joining 3 tables and using a left outer join with linq in EF Core 3.1.1

Question

I have 3 tables, Notices, Users, and Likes. I want to get all notices with user name and information if user likes this notice.

So far I have this code, but it returns one notice multiple times (one for each like):

return context.notices
        .GroupJoin(context.Users, notice => notice.CreatedBy, user => user.Id, (notice, users) => new { notice, users })
        .SelectMany(group => group.users.DefaultIfEmpty(), (group, user) =>
        new
        {
            group.notice,
            user
        })
        .GroupJoin(context.Likes, noticeDto => noticeDto.notice.Id, like => like.ItemId, (noticeDto, likes) => new { noticeDto, likes })
        .SelectMany(group => group.likes.DefaultIfEmpty(), (group, like) =>
        new NoticeDto
        {
            CreatedByName = (group.noticeDto.user == null ? "" : group.noticeDto.user.FullName),
            Id = group.noticeDto.notice.Id,
            Liked = like.CreatedBy == userId,
        });

I also tried this code.. but I am getting an error:

return context.notices
                .GroupJoin(context.Users, notice => notice.CreatedBy, user => user.Id, (notice, users) => new { notice, users })
                .SelectMany(group => group.users.DefaultIfEmpty(), (group, user) =>
                new
                {
                    group.notice,
                    user
                })
                .GroupJoin(context.Likes, noticeDto => noticeDto.notice.Id, like => like.ItemId, (noticeDto, likes) => new { noticeDto, likes })
                .Select((group) =>
                new NoticeDto
                {
                    CreatedByName = (group.noticeDto.user == null ? "" : group.noticeDto.user.FullName),
                    Id = group.noticeDto.notice.Id,                 
                    Liked = group.likes != null ? group.likes.Any(w => w.CreatedBy == userId) : false,
                });

This is the error I get:

Processing of the LINQ expression 'DbSet .LeftJoin( outer: DbSet .AsQueryable(), inner: notice => notice.CreatedBy, outerKeySelector: user => user.Id, innerKeySelector: (notice, user) => new { notice = notice, user = user }) .GroupJoin( outer: DbSet, inner: noticeDto => noticeDto.notice.Id, outerKeySelector: like => like.ItemId, innerKeySelector: (noticeDto, likes) => new { noticeDto = noticeDto, likes = likes })'
by 'NavigationExpandingExpressionVisitor' failed. This may indicate either a bug or a limitation in EF Core.

Can anyone help me achieve what I need?.. Thank you.

Notice

    public Guid Id { get; set; }
    public Guid CreatedBy { get; set; }

User

 public Guid Id { get; set; }
 public string FullName{ get; set; }

Like

    public Guid Id { get; set; }
    public Guid CreatedBy { get; set; }
    public Guid ItemId { get; set; }

Answer 1

Like's ItemId is Notice's Id? Notice's and Like's CreatedBy is User's Id?

If so, try this.

return context.notices.Include(x => x.Users)
                      .Include(x => x.Likes)
                      .Include("Likes.Users");

Database

Result

EDITED

As you do not have foreign key and relationship, then you can try this

var users = context.Users;
        
return context.notices.Select(x => new Notice() 
{
     Id = x.Id,
     CreatedBy = x.CreatedBy, 
     Users = users.Where(y => y.Id == x.CreatedBy).FirstOrDefault(), 
     Likes = context.Likes.Where(y => y.ItemId == x.Id)
                    .Select(y => new Likes()
                    {
                        Id = y.Id,
                        CreatedBy = y.CreatedBy,
                        ItemId = y.ItemId,
                        Users = users.Where(z => z.Id == y.CreatedBy).FirstOrDefault()
                    }).ToList()
});