title: EF Core Linq for distinct rows
body: <p>I need help on linq method based query. I have tables below.</p>
  <p><strong>ItemTable</strong></p>
  <pre><code>ItemId | Itemname
    1    | Item1
  </code></pre>
  <p><strong>StatusTable</strong></p>
  <pre><code>StatusId | Status
     S1    | NotOk
     S2    | Ok
     S3    | Unknown
  </code></pre>
  <p>I have a mapping table as below: It will have mappings like below with duplicated records. For simplicity, I did not include all the columns.</p>
  <p><strong>ItemStatus</strong></p>
  <pre><code>ItemId | StatusId
     1   | S1
     1   | S1
     1   | S2
  </code></pre>
  <p>Need help with linq method based for displaying the data as below</p>
  <pre><code>ItemName | Status
    Item1  | NotOK, OK
  </code></pre>
  <p>But when I try with linq (method based not query based), I am getting duplicates like below. I do not want duplicate records to be included.</p>
  <pre><code>ItemName | Status
     Item1 | NotOK, NotOk, OK
  </code></pre>
  <p>I could not post the linq as it was too complex. I simplified the scenario and posted here. Please suggest right linq method based approach  for this scenario</p>
  <p>I have given high level linq query (not so accurate, i just added for reference)</p>
  <pre><code>var dataQueryable = context.ItemTable
                  .Join(context.ItemStatus.Select(a =&gt; a.ItemId).Distinct(), item=&gt;item.ItemId, id=&gt;id,(item,id) =&gt;
                      new ReturnObject
                      {
                          ItemId = item.ItemId,
                          ItemName = item.ItemName,
                          Status = item.ItemStatus
                              .Where(e =&gt; e.ItemId == item.ItemId)
                              .Select(result =&gt; new ResultReturnObject()
                              {
                                  Status = result.status
                              })
                              .ToList()
                      })
                  .AsQueryable();
  </code></pre>