title: Automapper generates a weird SQL query
body: <p>I'm using Automapper version 10.0 with EF Core 5 and .NET 5.</p>
  <p>It would seem that automapper causes a query that selects every property separately:</p>
  <pre class="lang-sql prettyprint-override"><code>      SELECT FALSE, a11.&quot;Avatar&quot;, ((
            SELECT a0.&quot;Id&quot;
            FROM &quot;AspNetUserRoles&quot; AS a
            INNER JOIN &quot;AspNetRoles&quot; AS a0 ON a.&quot;RoleId&quot; = a0.&quot;Id&quot;
            WHERE a11.&quot;Id&quot; = a.&quot;UserId&quot;
            ORDER BY a0.&quot;Order&quot; DESC
            LIMIT 1) IS NULL), (
            SELECT a2.&quot;Color&quot;
            FROM &quot;AspNetUserRoles&quot; AS a1
            INNER JOIN &quot;AspNetRoles&quot; AS a2 ON a1.&quot;RoleId&quot; = a2.&quot;Id&quot;
            WHERE a11.&quot;Id&quot; = a1.&quot;UserId&quot;
            ORDER BY a2.&quot;Order&quot; DESC
            LIMIT 1), (
            SELECT a4.&quot;Id&quot;
            FROM &quot;AspNetUserRoles&quot; AS a3
            INNER JOIN &quot;AspNetRoles&quot; AS a4 ON a3.&quot;RoleId&quot; = a4.&quot;Id&quot;
            WHERE a11.&quot;Id&quot; = a3.&quot;UserId&quot;
            ORDER BY a4.&quot;Order&quot; DESC
            LIMIT 1), (
            SELECT a6.&quot;IsStaff&quot;
            FROM &quot;AspNetUserRoles&quot; AS a5
            INNER JOIN &quot;AspNetRoles&quot; AS a6 ON a5.&quot;RoleId&quot; = a6.&quot;Id&quot;
            WHERE a11.&quot;Id&quot; = a5.&quot;UserId&quot;
            ORDER BY a6.&quot;Order&quot; DESC
            LIMIT 1), (
            SELECT a8.&quot;Name&quot;
            FROM &quot;AspNetUserRoles&quot; AS a7
            INNER JOIN &quot;AspNetRoles&quot; AS a8 ON a7.&quot;RoleId&quot; = a8.&quot;Id&quot;
            WHERE a11.&quot;Id&quot; = a7.&quot;UserId&quot;
            ORDER BY a8.&quot;Order&quot; DESC
            LIMIT 1), COALESCE((
            SELECT a10.&quot;Order&quot;
            FROM &quot;AspNetUserRoles&quot; AS a9
            INNER JOIN &quot;AspNetRoles&quot; AS a10 ON a9.&quot;RoleId&quot; = a10.&quot;Id&quot;
            WHERE a11.&quot;Id&quot; = a9.&quot;UserId&quot;
            ORDER BY a10.&quot;Order&quot; DESC
            LIMIT 1), 0), a11.&quot;Title&quot;, a11.&quot;UserName&quot;, t.&quot;Body&quot;, t.&quot;CommentsThreadId&quot;, t.&quot;DateTime&quot;, t.&quot;Id&quot;
        FROM (
            SELECT c.&quot;Id&quot;, c.&quot;AuthorId&quot;, c.&quot;Body&quot;, c.&quot;CommentsThreadId&quot;, c.&quot;DateTime&quot;
            FROM &quot;Comments&quot; AS c
            WHERE c.&quot;CommentsThreadId&quot; = @__threadId_0
            ORDER BY c.&quot;DateTime&quot; DESC
            LIMIT @__p_1 OFFSET 0
        ) AS t
        INNER JOIN &quot;AspNetUsers&quot; AS a11 ON t.&quot;AuthorId&quot; = a11.&quot;Id&quot;
        ORDER BY t.&quot;DateTime&quot; DESC
  </code></pre>
  <p>As you can see, pretty much every single property of <code>RoleDto</code> is being <code>SELECT</code>ed separately, instead of being selected just once and having its columns mapped using the <code>AS</code> keyword.</p>
  <p>Mappings are done to the following DTOs, property names being true to source entities:</p>
  <pre class="lang-cs prettyprint-override"><code>public class UserSimpleDto
  {
      public string UserName { get; set; }
      public string Avatar { get; set; }
      public string Title { get; set; }
      public RoleDto Role { get; set; }
  }
  </code></pre>
  <p>It seems the above and the below are the main culprits. <code>OgmaUser</code>, the source entity for the mapping of the above, contains a list of user roles, of which only one should be present in the target DTO.</p>
  <p><code>OgmaUser.Roles</code> is mapped using EF Core 5's many-to-many setup, but I did try to use an explicit join entity and the result was the exact same.</p>
  <pre class="lang-cs prettyprint-override"><code>public class RoleDto
  {
      public long Id { get; set; }
      public string Name { get; set; }
      public string? Color { get; set; }
      public bool IsStaff { get; set; }
      public int Order { get; set; }
  }
  </code></pre>
  <p>As you can see, each property of the above is being <code>SELECT ... AS ...</code> separately.</p>
  <pre class="lang-cs prettyprint-override"><code>public class CommentDto
  {
      public long Id { get; set; }
      public long CommentsThreadId { get; set; }
      public UserSimpleDto Author { get; set; }
      public DateTime DateTime { get; set; }
      public string Body { get; set; }
  }
  </code></pre>
  <p>I doubt this particular DTO has something to do with the result, since the issue occurs with other DTOs that contain <code>UserSimpleDto</code>, but I thought I should include it to present the fullest possible picture.</p>
  <p>And the mappings are as follows:</p>
  <pre class="lang-cs prettyprint-override"><code>CreateMap&lt;OgmaUser, UserSimpleDto&gt;()
      .ForMember(
          usd =&gt; usd.Role,
          opts =&gt; opts.MapFrom(u =&gt; u.Roles.OrderByDescending(r =&gt; r.Order).FirstOrDefault())
      );
  
  CreateMap&lt;OgmaRole, RoleDto&gt;();
  
  CreateMap&lt;Comment, CommentDto&gt;()
      .ForMember(
          cd =&gt; cd.Body,
          opts =&gt; opts.MapFrom(c =&gt; Markdown.ToHtml(c.Body, null))
      );
  </code></pre>
  <p>The query is generated from the following method:</p>
  <pre class="lang-cs prettyprint-override"><code>public async Task&lt;IEnumerable&lt;CommentDto&gt;&gt; GetPaginated(long threadId, int page)
  {
      return await _context.Comments
          .Where(c =&gt; c.CommentsThreadId == threadId)
          .OrderByDescending(c =&gt; c.DateTime)
          .ProjectTo&lt;CommentDto&gt;(_mapper.ConfigurationProvider)
          .Skip(Math.Max(0, page - 1) * _config.CommentsPerPage)
          .Take(_config.CommentsPerPage);
          .AsNoTracking()
          .ToListAsync();
  }
  </code></pre>
  <p>Stack complains that my question is mostly code, and I suppose I do understand that. I've seen myself many people just posting their code, expecting to have it fixed, or even posting their homework and asking for it to be solved.</p>
  <p>In this case, however, I believe that the code speaks louder than whatever explanation I could provide, so, true, non-code parts of this question are few and far between. That's the reason why I'm even writing those last paragraph, in fact, as SO will not let me submit my question otherwise.</p>