title: Entity Framework Core - Very slow performance
body: <p>I have the following entities (I'll show the properties I'm working with because I don't want to make it larger than needed):</p>
  
  <p><strong>PROPERTY:</strong> Where a property can be child of another one and has a 1-1 relationship with <code>GeoLocation</code> and can have multiple <code>Multimedia</code> and <code>Operation</code></p>
  
  <pre><code>public partial class Property
  {
      public Property()
      {
          InverseParent = new HashSet&lt;Property&gt;();
          Multimedia = new HashSet&lt;Multimedia&gt;();
          Operation = new HashSet&lt;Operation&gt;();
      }
  
      public long Id { get; set; }
      public string GeneratedTitle { get; set; }
      public string Url { get; set; }
      public DateTime? DatePublished { get; set; }
      public byte StatusCode { get; set; }
      public byte Domain { get; set; }
      public long? ParentId { get; set; }
  
      public virtual Property Parent { get; set; }
      public virtual GeoLocation GeoLocation { get; set; }
      public virtual ICollection&lt;Property&gt; InverseParent { get; set; }
      public virtual ICollection&lt;Multimedia&gt; Multimedia { get; set; }
      public virtual ICollection&lt;Operation&gt; Operation { get; set; }
  }
  </code></pre>
  
  <p><strong>GEOLOCATION:</strong> As mentioned, it has a 1-1 relationship with <code>Property</code></p>
  
  <pre><code>public partial class GeoLocation
  {
      public int Id { get; set; }
      public double? Latitude { get; set; }
      public double? Longitude { get; set; }
      public long? PropertyId { get; set; }
  
      public virtual Property Property { get; set; }
  }
  </code></pre>
  
  <p><strong>MULTIMEDIA:</strong> it can hold multiple Images, with different sizes, for a single <code>Property</code>. The detail here is that <code>Order</code> specifies the order of the images to be shown in the client application, but it doesn't start always with 1. There're some cases where a <code>Property</code> has <code>Multimedia</code> files that starts with 3 or x.</p>
  
  <pre><code>public partial class Multimedia
  {
      public long Id { get; set; }
      public long? Order { get; set; }
      public string Resize360x266 { get; set; }
      public long? PropertyId { get; set; }
  
      public virtual Property Property { get; set; }
  }
  </code></pre>
  
  <p><strong>OPERATIONS:</strong> defines all the operations a <code>Property</code> can have, using <code>OperationType</code> to name this operation. (rent, sell, etc.)</p>
  
  <pre><code>public partial class Operation
  {
      public Operation()
      {
          Price = new HashSet&lt;Price&gt;();
      }
  
      public long Id { get; set; }
      public long? OperationTypeId { get; set; }
      public long? PropertyId { get; set; }
  
      public virtual OperationType OperationType { get; set; }
      public virtual Property Property { get; set; }
      public virtual ICollection&lt;Price&gt; Price { get; set; }
  }
  
  public partial class OperationType
  {
      public OperationType()
      {
          Operation = new HashSet&lt;Operation&gt;();
      }
  
      public long Id { get; set; }
      public string Name { get; set; }
  
      public virtual ICollection&lt;Operation&gt; Operation { get; set; }
  }
  </code></pre>
  
  <p><strong>PRICE:</strong> defines the price for each operation and the currency type. (i.e.: A property can have the rent option - <code>Operation</code> - for X amount in USD currency, but another price registered for the same <code>Operation</code> in case of use another currency type )</p>
  
  <pre><code>public partial class Price
  {
      public long Id { get; set; }
      public float? Amount { get; set; }
      public string CurrencyCode { get; set; }
      public long? OperationId { get; set; }
  
      public virtual Operation Operation { get; set; }
  }
  </code></pre>
  
  <p>Said that, I want to get all the records (actually are about 40K-50K), but only for a few properties. As I mentioned before, the <code>Multimedia</code> table can have a lot of records for each <code>Property</code>, but I only need the first one with the smaller <code>Order</code> value and sorted by <code>DatePublished</code>. After that, I need to convert the result into MapMarker object, which is as follows:</p>
  
  <pre><code>public class MapMarker : EstateBase
  {
      public long Price { get; set; }
      public int Category { get; set; }
      public List&lt;Tuple&lt;string, string, string&gt;&gt; Prices { get; set; }
  }
  </code></pre>
  
  <p>In order to achieve this, I made the following:</p>
  
  <pre><code>public async Task&lt;IEnumerable&lt;MapMarker&gt;&gt; GetGeolocatedPropertiesAsync(int quantity)
  {
      var properties = await GetAllProperties().AsNoTracking()
          .Include(g =&gt; g.GeoLocation)
          .Include(m =&gt; m.Multimedia)
          .Include(p =&gt; p.Operation).ThenInclude(o =&gt; o.Price)
          .Include(p =&gt; p.Operation).ThenInclude(o =&gt; o.OperationType)
          .Where(p =&gt; p.GeoLocation != null 
              &amp;&amp; !string.IsNullOrEmpty(p.GeoLocation.Address) 
              &amp;&amp; p.GeoLocation.Longitude != null 
              &amp;&amp; p.GeoLocation.Latitude != null 
              &amp;&amp; p.StatusCode == (byte)StatusCode.Online 
              &amp;&amp; p.Operation.Count &gt; 0)
          .OrderByDescending(p =&gt; p.ModificationDate)
          .Take(quantity)
          .Select(p =&gt; new {
              p.Id,
              p.Url,
              p.GeneratedTitle,
              p.GeoLocation.Address,
              p.GeoLocation.Latitude,
              p.GeoLocation.Longitude,
              p.Domain,
              p.Operation,
              p.Multimedia.OrderBy(m =&gt; m.Order).FirstOrDefault().Resize360x266
          })
          .ToListAsync();
  
      var mapMarkers = new List&lt;MapMarker&gt;();
  
      try
      {
          foreach (var property in properties)
          {
              var mapMarker = new MapMarker();
              mapMarker.Id = property.Id.ToString();
              mapMarker.Url = property.Url;
              mapMarker.Title = property.GeneratedTitle ?? string.Empty;
              mapMarker.Address = property.Address ?? string.Empty;
              mapMarker.Latitude = property.Latitude.ToString() ?? string.Empty;
              mapMarker.Longitude = property.Longitude.ToString() ?? string.Empty;
              mapMarker.Domain = ((Domain)Enum.ToObject(typeof(Domain), property.Domain)).ToString();
              mapMarker.Image = property.Resize360x266 ?? string.Empty;
              mapMarker.Prices = new List&lt;Tuple&lt;string, string, string&gt;&gt;();
              foreach (var operation in property.Operation)
              {
                  foreach (var price in operation.Price)
                  {
                      var singlePrice = new Tuple&lt;string, string, string&gt;(operation.OperationType.Name, price.CurrencyCode, price.Amount.ToString());
                      mapMarker.Prices.Add(singlePrice);
                  }
              }
              mapMarkers.Add(mapMarker);
          }
      }
      catch (Exception ex)
      {
  
          throw;
      }
  
      return mapMarkers;
  }
  </code></pre>
  
  <p>but the results take more than 14 minutes and this method could be called multiple times in a minute. I want to optimize it to return the results in the less time possible. I alreay tried removing <code>ToListAsync()</code>, but in the <code>foreach</code> loop it takes a lot of time too, and that makes all the sense.</p>
  
  <p>So, what do you think I can do here?
  Thanks in advance.</p>
  
  <p><strong>UPDATE:</strong>
  Here is <code>GetAllProperties()</code> method, I forgot to include this one.</p>
  
  <pre><code>private IQueryable&lt;Property&gt; GetAllProperties()
  {
      return _dbContext.Property.AsQueryable();
  }
  </code></pre>
  
  <p>And the SQL query that Entity Framework is making against SQL Server:</p>
  
  <pre><code>SELECT [p].[Id], [p].[Url], [p].[GeneratedTitle], [g].[Address], [g].[Latitude], [g].[Longitude], [p].[Domain], (
      SELECT TOP(1) [m].[Resize360x266]
      FROM [Multimedia] AS [m]
      WHERE [p].[Id] = [m].[PropertyId]
      ORDER BY [m].[Order]), [t].[Id], [t].[CreationDate], [t].[ModificationDate], [t].[OperationTypeId], [t].[PropertyId], [t].[Id0], [t].[CreationDate0], [t].[ModificationDate0], [t].[Name], [t].[Id1], [t].[Amount], [t].[CreationDate1], [t].[CurrencyCode], [t].[ModificationDate1], [t].[OperationId]
  FROM [Property] AS [p]
  LEFT JOIN [GeoLocation] AS [g] ON [p].[Id] = [g].[PropertyId]
  LEFT JOIN (
      SELECT [o].[Id], [o].[CreationDate], [o].[ModificationDate], [o].[OperationTypeId], [o].[PropertyId], [o0].[Id] AS [Id0], [o0].[CreationDate] AS [CreationDate0], [o0].[ModificationDate] AS [ModificationDate0], [o0].[Name], [p0].[Id] AS [Id1], [p0].[Amount], [p0].[CreationDate] AS [CreationDate1], [p0].[CurrencyCode], [p0].[ModificationDate] AS [ModificationDate1], [p0].[OperationId]
      FROM [Operation] AS [o]
      LEFT JOIN [OperationType] AS [o0] ON [o].[OperationTypeId] = [o0].[Id]
      LEFT JOIN [Price] AS [p0] ON [o].[Id] = [p0].[OperationId]
  ) AS [t] ON [p].[Id] = [t].[PropertyId]
  WHERE (((([g].[Id] IS NOT NULL AND ([g].[Address] IS NOT NULL AND (([g].[Address] &lt;&gt; N'') OR [g].[Address] IS NULL))) AND [g].[Longitude] IS NOT NULL) AND [g].[Latitude] IS NOT NULL) AND ([p].[StatusCode] = CAST(1 AS tinyint))) AND ((
      SELECT COUNT(*)
      FROM [Operation] AS [o1]
      WHERE [p].[Id] = [o1].[PropertyId]) &gt; 0)
  ORDER BY [p].[ModificationDate] DESC, [p].[Id], [t].[Id], [t].[Id1]
  </code></pre>
  
  <p><strong>UPDATE 2:</strong> As @Igor mentioned, this is the link of the Execution Plan Result: 
  <a href="https://www.brentozar.com/pastetheplan/?id=BJNz9KdQI" rel="nofollow noreferrer">https://www.brentozar.com/pastetheplan/?id=BJNz9KdQI</a></p>