Unwanted materialization of intermediate query in some cases

[ac10n]

In the code below, if we uncomment the line

 .Where(x => x.LastOrder != null)

We'll see materialization of LastOrders. Even worse, that happens in a select N+1 manner, one by one in queries like:

exec sp_executesql N'SELECT TOP(1) [o].[Id], [o].[Date], [o].[PersonId], [o].[Price]
FROM [Orders] AS [o]
WHERE @_outer_Id = [o].[PersonId]
ORDER BY [o].[Date] DESC',N'@_outer_Id int',@_outer_Id=1

Steps to reproduce

using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Linq;

namespace EfCoreBugs
{
  class Program
  {
    static void Main(string[] args)
    {
      using (var dbContext = new MyDbContext())
      {
        var data = dbContext.People
          .Select(x => new
          {
            Person = x,
            LastOrder = x.Orders.OrderByDescending(o => o.Date).FirstOrDefault()
          })
          //.Where(x => x.LastOrder != null)
          .Select(x => new
          {
            x.Person.Name,
            x.LastOrder.Price,
            x.LastOrder.Date
          }).ToList();
        Console.WriteLine(data.Count);
      }
      Console.ReadLine();
    }

    public class MyDbContext : DbContext
    {
      protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
      {
        optionsBuilder.UseSqlServer(@"Server=.;Database=EfCoreBugs;Trusted_Connection=True;MultipleActiveResultSets=True;");
      }
      public DbSet<Person> People { get; set; }
      public DbSet<Order> Orders { get; set; }
    }

    public class Person
    {
      public int Id { get; set; }
      public string Name { get; set; }

      public ICollection<Order> Orders { get; set; }
    }

    public class Order
    {
      public int Id { get; set; }
      public DateTime Date { get; set; }
      public decimal Price { get; set; }

      public int PersonId { get; set; }
      public Person Person { get; set; }
    }
  }
}

Further technical details

EF Core version: 2.1.4
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: Win10
IDE: Vs2017 15.8.9

[smitpatel]

#10001 Would help this scenario to be condensed in single query rather than N+1.
That being said, 3.0 query changes may make the materialization unnecessary and reduces the columns brought back.

[maumar]

This works in the new pipeline. We simplify the intermediate projection and access the properties directly. Also entity we convert the x.LastOrder != null to key equality.

equivalent query on northwind:

ctx.Customers
                    .Select(c => new
                    {
                        Person = c,
                        LastOrder = c.Orders.OrderByDescending(o => o.OrderDate).FirstOrDefault()
                    }
                    ).Where(x => x.LastOrder != null)
                    .Select(x => new { x.Person.CustomerID, x.LastOrder.OrderID })

produces the following sql:

SELECT [c].[CustomerID], (SELECT TOP(1) [o].[OrderID]
    FROM [Orders] AS [o]
    WHERE [c].[CustomerID] = [o].[CustomerID]
    ORDER BY [o].[OrderDate] DESC)
FROM [Customers] AS [c]
WHERE (SELECT TOP(1) [o0].[OrderID]
    FROM [Orders] AS [o0]
    WHERE [c].[CustomerID] = [o0].[CustomerID]
    ORDER BY [o0].[OrderDate] DESC) IS NOT NULL