title: Joining 3 tables and using a left outer join with linq in EF Core 3.1.1
body: <p>I have 3 tables, <code>Notices</code>, <code>Users</code>, and <code>Likes</code>. I want to get all notices with user name and information if user likes this notice.</p>
  <p>So far I have this code, but it returns one notice multiple times (one for each like):</p>
  <pre><code>return context.notices
          .GroupJoin(context.Users, notice =&gt; notice.CreatedBy, user =&gt; user.Id, (notice, users) =&gt; new { notice, users })
          .SelectMany(group =&gt; group.users.DefaultIfEmpty(), (group, user) =&gt;
          new
          {
              group.notice,
              user
          })
          .GroupJoin(context.Likes, noticeDto =&gt; noticeDto.notice.Id, like =&gt; like.ItemId, (noticeDto, likes) =&gt; new { noticeDto, likes })
          .SelectMany(group =&gt; group.likes.DefaultIfEmpty(), (group, like) =&gt;
          new NoticeDto
          {
              CreatedByName = (group.noticeDto.user == null ? &quot;&quot; : group.noticeDto.user.FullName),
              Id = group.noticeDto.notice.Id,
              Liked = like.CreatedBy == userId,
          });
  </code></pre>
  <p>I also tried this code.. but I am getting an error:</p>
  <pre><code>return context.notices
                  .GroupJoin(context.Users, notice =&gt; notice.CreatedBy, user =&gt; user.Id, (notice, users) =&gt; new { notice, users })
                  .SelectMany(group =&gt; group.users.DefaultIfEmpty(), (group, user) =&gt;
                  new
                  {
                      group.notice,
                      user
                  })
                  .GroupJoin(context.Likes, noticeDto =&gt; noticeDto.notice.Id, like =&gt; like.ItemId, (noticeDto, likes) =&gt; new { noticeDto, likes })
                  .Select((group) =&gt;
                  new NoticeDto
                  {
                      CreatedByName = (group.noticeDto.user == null ? &quot;&quot; : group.noticeDto.user.FullName),
                      Id = group.noticeDto.notice.Id,                 
                      Liked = group.likes != null ? group.likes.Any(w =&gt; w.CreatedBy == userId) : false,
                  });
  </code></pre>
  <p>This is the error I get:</p>
  <blockquote>
  <p>Processing of the LINQ expression 'DbSet
  .LeftJoin(
  outer: DbSet
  .AsQueryable(),
  inner: notice =&gt; notice.CreatedBy,
  outerKeySelector: user =&gt; user.Id,
  innerKeySelector: (notice, user) =&gt; new {
  notice = notice,
  user = user
  })
  .GroupJoin(
  outer: DbSet,
  inner: noticeDto =&gt; noticeDto.notice.Id,
  outerKeySelector: like =&gt; like.ItemId,
  innerKeySelector: (noticeDto, likes) =&gt; new {
  noticeDto = noticeDto,
  likes = likes
  })'<br />
  by 'NavigationExpandingExpressionVisitor' failed. This may indicate either a bug or a limitation in EF Core.</p>
  </blockquote>
  <p>Can anyone help me achieve what I need?.. Thank you.</p>
  <p><strong>Notice</strong></p>
  <pre><code>    public Guid Id { get; set; }
      public Guid CreatedBy { get; set; }
  </code></pre>
  <p><strong>User</strong></p>
  <pre><code> public Guid Id { get; set; }
   public string FullName{ get; set; }
  </code></pre>
  <p><strong>Like</strong></p>
  <pre><code>    public Guid Id { get; set; }
      public Guid CreatedBy { get; set; }
      public Guid ItemId { get; set; }
  </code></pre>