title: How to improve performance of an EF Core query which uses several Includes
body: <p>I've got this query, which I'll simplify for brevity:</p>
  
  <pre><code>public IQueryable&lt;User&gt; GetByIdAsync(Guid userId)
  {
      return MyContext
          .Users
          //Bunch of Includes
              //Most of which have a ThenInclude
                  //Followed by another ThenInclude
          .FirstOrDefaultAsync(u =&gt; u.Id == userId)
  }
  </code></pre>
  
  <p>When run for around 100 users, it takes over 15 seconds (running locally on my machine). Not great.</p>
  
  <p>I've tried using <code>AsNoTracking()</code>, as well as changing it to use a compiled query like so:</p>
  
  <pre><code>private static Func&lt;MyContext, Guid, Task&lt;User&gt;&gt; _getByIdAsync =
              EF.CompileAsyncQuery((MyContext context, Guid userId) =&gt;
                  context
                  .Users
                  //Same Includes as above
                  .Where(u =&gt; u.Id == userId)
                  .FirstOrDefault());
  
  public IQueryable&lt;User&gt; GetByIdAsync(Guid userId)
  {
      return await _getByIdAsync(userId);
  }
  </code></pre>
  
  <p>Still no difference.</p>
  
  <p>I've had a look at this answer for a relevant thread, which suggests using plain old SQL:</p>
  
  <blockquote>
    <p><a href="https://stackoverflow.com/a/16977218/9778248">https://stackoverflow.com/a/16977218/9778248</a></p>
  </blockquote>
  
  <p>And I've had a look at this answer, which mentions clustered indexes:</p>
  
  <blockquote>
    <p><a href="https://stackoverflow.com/a/55557237/9778248">https://stackoverflow.com/a/55557237/9778248</a></p>
  </blockquote>
  
  <p>I certainly can't exclude any of the <code>Includes</code> as the client depends on all this info. Redesigning is also not an option at this stage.</p>
  
  <p><strong>Questions</strong></p>
  
  <ol>
  <li>Are there any other options that can improve performance?</li>
  <li>I can't see any <strong>CLUSTERED</strong> or <strong>NONCLUSTERED</strong> tags in any of my child table indexes. Is this worth looking into and if so, can I be pointed to any documentation that explains how I can go about updating using EF (or without)?</li>
  </ol>