title: Returning one of each object based on list of Ids and other property using EF/Linq
body: <p>I have an SQL table with a list of objects and I am trying to return a list using a number of different criteria.</p>
  <p>Here is my object (same as the SQL table):</p>
  <pre class="lang-cs prettyprint-override"><code>class Photo{
          public int Id { get; set; }
          public string FileName { get; set; }
          public DateTime Uploaded { get; set; }
          public int? ProjectId { get; set; }
          public int GalleryOrder { get; set; }
  }  
  </code></pre>
  <p>In the SQL table, there are lots of photos uploaded over time that may or may not be tagged with <code>ProjectId</code>s. There are numerous photos tagged to each <code>ProjectId</code>. They are uploaded in batches, so that there may be numerous photos with the same <code>DateTime Uploaded</code> and then organized with <code>GalleryOrder</code>.</p>
  <p>Given a list of <code>ProjectId</code>s I am trying to return one representative photo for each project based on the following parameters:</p>
  <ul>
  <li>One Photo per ProjectId</li>
  <li>Most recent uploaded date</li>
  <li>Lowest gallery order</li>
  </ul>
  <p>I have some code that appears to be working with my test database of ~20 entries, but it pulls excess data and has numerous sorts. I'm not sure how to simplify and optimize it.</p>
  <p>Here is my current query:</p>
  <pre class="lang-cs prettyprint-override"><code>public async Task&lt;List&lt;Photo&gt;&gt; GetOneImageFilesPerProject(List&lt;int&gt; projectIds)
  {
      using var context = _contextFactory.CreateDbContext();
  
      var results = await context.Photos.Where(x =&gt; x.ProjectId != null 
                                        &amp;&amp; projectIds.Contains((int)x.ProjectId))
                                        .ToListAsync();
                                               
      results = results.OrderBy(x =&gt; x.ProjectId)
                       .ThenByDescending(x =&gt; x.Uploaded)
                       .ThenBy(x =&gt; x.GalleryOrder)
                       .GroupBy(x =&gt; x.ProjectId)
                       .Select(x =&gt; x.First())
                       .ToList();
  
      return results;
  }
  </code></pre>
  <p>The program compiles with the <code>OrderBy</code>, <code>GroupBy</code>, and <code>Select</code> calls added after the <code>.Where</code> call, but it seemed to hang at that point and never return the final list. That's why it's split into two processing steps.</p>
  <p>My other thought was to get the list from the Db and then go through with a foreach loop to build the final list. Not sure if that's faster than using <code>.GroupBy</code> and <code>.Select</code>. Either way seems a bit inelegant and brute force. If there's a straight SQL query that would be a better solution, I'm open to it!</p>
  <p>Is there some method that is more straightforward for returning a list with one unique item based on other criteria within the object list?</p>
  <p>For scale, this won't be a massive app, but it will be looking for ~10-20 projectIds at a time and each project might have ~40-50 photos tagged to it. Many cases will be a bit smaller (2-5 photos) but some larger, (100-200 photos). This function will be run by the users very frequently, as in several times a day, and the Photo list will be changing frequently.</p>
  <p>Edit: Using .NET 5 and EF Core 5</p>