title: rewrite a correlated subqueries in ef core 2.1
body: <p>Is there a way i can rewrite this query so it is not a correlated subqueries ?</p>
  <pre><code>var query = (from o in dbcontext.Orders
                               let lastStatus = o.OrderStatus.Where(x =&gt; x.OrderId == o.Id).OrderByDescending(x =&gt; x.CreatedDate).FirstOrDefault()
                               where lastStatus.OrderId != 1
                               select new { o.Name, lastStatus.Id }
                               ).ToList();
  </code></pre>
  <p>This resulted in:</p>
  <pre><code> SELECT [o].[Name], (
            SELECT TOP(1) [x0].[Id]
            FROM [OrderStatus] AS [x0]
            WHERE ([x0].[OrderId] = [o].[Id]) AND ([o].[Id] = [x0].[OrderId])
            ORDER BY [x0].[CreatedDate] DESC
        ) AS [Id]
        FROM [Orders] AS [o]
        WHERE (
            SELECT TOP(1) [x].[OrderId]
            FROM [OrderStatus] AS [x]
            WHERE ([x].[OrderId] = [o].[Id]) AND ([o].[Id] = [x].[OrderId])
            ORDER BY [x].[CreatedDate] DESC
        ) &lt;&gt; 1
  </code></pre>
  <p>I have tried to do a join on a subquery but EF 2.1 is doing weird things... not what I expected;</p>
  <pre><code>     var query = (from o in dbcontext.Orders
                       join lastStat in (from os in dbcontext.OrderStatus
                                         orderby os.CreatedDate descending
                                         select new { os }
                                         ) on o.Id equals lastStat.os.OrderId
                       where lastStat.os.StatusId != 1
                       select new { o.Name, lastStat.os.StatusId }).ToList();
  </code></pre>