Using Any() in projected query warns about client side evaluation

[prochnowc]

Steps to reproduce

Code to reproduce:

    public class Customer
    {
        public int Id { get; set; }

        public ICollection<Address> Addresses { get; set; }

        public int CustomerTypeId { get; set; }

        public CustomerType CustomerType { get; set; }
    }

    public class CustomerDto
    {
        public ICollection<AddressDto> Addresses { get; set; }

        public string CustomerTypeName { get; set; }

        public string CustomerTypeAccessRuleName { get; set; }
    }

    public class Address
    {
        public int Id { get; set; }

        public int CustomerId { get; set; }

        public Customer Customer { get; set; }

        public string Street { get; set; }

        public string City { get; set; }
    }

    public class AddressDto
    {
        public string Street { get; set; }

        public string City { get; set; }
    }

    public class CustomerType
    {
        public int Id { get; set; }

        public string Name { get; set; }

        public int CustomerTypeAccessRuleId { get; set; }

        public CustomerTypeAccessRule CustomerTypeAccessRule { get; set; }
    }

    public class CustomerTypeAccessRule
    {
        public int Id { get; set; }

        public string Name { get; set; }
    }

    public class CrmDbContext : DbContext
    {
        public DbSet<Customer> Customers => Set<Customer>();

        public DbSet<Address> Address => Set<Address>();

        public DbSet<CustomerType> CustomerTypes => Set<CustomerType>();

        public DbSet<CustomerTypeAccessRule> CustomerTypeAccessRules => Set<CustomerTypeAccessRule>();

        public CrmDbContext(DbContextOptions<CrmDbContext> options)
            : base(options)
        {
        }
    }

    public class Program
    {
        static IServiceProvider ConfigureServices()
        {
            var services = new ServiceCollection();

            services.AddLogging();
            services.AddEntityFramework();
            services.AddEntityFrameworkSqlServer();
            services.AddDbContext<CrmDbContext>(
                o => { o.UseSqlServer("Server=(localdb)\\MSSQLLocalDB;Database=CrmDb;Trusted_Connection=True;"); });

            return services.BuildServiceProvider();
        }

        static IMapper CreateMapper()
        {
            var mapperConfig = new MapperConfiguration(
                c =>
                {
                    c.CreateMap<Customer, CustomerDto>()
                        .ForMember(d => d.CustomerTypeName, o => o.MapFrom(s => s.CustomerType.Name))
                        .ForMember(d => d.CustomerTypeAccessRuleName,
                            o => o.MapFrom(s => s.CustomerType.CustomerTypeAccessRule.Name));

                    c.CreateMap<Address, AddressDto>();
                });

            return mapperConfig.CreateMapper();
        }

        public static void Main(string[] args)
        {
            var serviceProvider = ConfigureServices();

            var loggerFactory = serviceProvider.GetRequiredService<ILoggerFactory>();
            loggerFactory.AddProvider(new ConsoleLoggerProvider((c, l) => true, true));

            var dbContext = serviceProvider.GetRequiredService<CrmDbContext>();
            if (dbContext.Database.EnsureCreated())
            {
                var customerTypeAccessRule = new CustomerTypeAccessRule {Name = "rule"};
                dbContext.CustomerTypeAccessRules.Add(customerTypeAccessRule);

                var customerType = new CustomerType {Name = "type", CustomerTypeAccessRule = customerTypeAccessRule};
                dbContext.CustomerTypes.Add(customerType);
                
                dbContext.Customers.AddRange(new Customer
                    {
                        CustomerType = customerType,
                        Addresses = new List<Address>
                        {
                            new Address {City = "Berlin", Street = "Alexanderplatz"},
                            new Address {City = "New York", Street = "Port St"}
                        }
                    },
                    new Customer
                    {
                        CustomerType = customerType,
                        Addresses = new List<Address>
                        {
                            new Address {City = "Berlin", Street = "Alexanderplatz"},
                            new Address {City = "Potsdam", Street = "Hauptstraße"},
                            new Address {City = "Brandenburg", Street = "Bahnhofstraße"}
                        }
                    });

                dbContext.SaveChanges();
            }

            var mapper = CreateMapper();

            dbContext.Customers
                .ProjectTo<CustomerDto>(mapper.ConfigurationProvider)
                .Where(c=> c.Addresses.Any(a => a.City == "Berlin"))
                .ToList();
        }
    }

Produces warnings:

dbug: Microsoft.EntityFrameworkCore.Query.Internal.SqlServerQueryCompilationContextFactory[2]
      => Microsoft.EntityFrameworkCore.Query.Internal.SqlServerQueryModelVisitor
      Compiling query model:
      'from Customer dto in DbSet<Customer>
      where
          (from AddressDto a in List<AddressDto> ToList(
              source:
                  from Address dto in dto.Addresses
                  select new AddressDto{
                      dto.CityCity = [dto].City,
                      dto.StreetStreet = [dto].Street
                  }

          )

          where a.City == "Berlin"
          select a)
          .Any()
      select new CustomerDto{
          dto.CustomerType.CustomerTypeAccessRule.NameCustomerTypeAccessRuleName = [dto].CustomerType.CustomerTypeAccessRule.Name,
          dto.CustomerType.NameCustomerTypeName = [dto].CustomerType.Name,
          List<AddressDto> ToList(
              source:
                  from Address dto in dto.Addresses
                  select new AddressDto{
                      dto.CityCity = [dto].City,
                      dto.StreetStreet = [dto].Street
                  }

          )
          Addresses = {from Address dto in [dto].Addresses select new AddressDto() {City = [dto].City, Street = [dto].Street}}.ToList()
      }
      '
dbug: Microsoft.EntityFrameworkCore.Query.Internal.SqlServerQueryCompilationContextFactory[3]
      => Microsoft.EntityFrameworkCore.Query.Internal.SqlServerQueryModelVisitor
      Optimized query model:
      'from Customer dto in DbSet<Customer>
      join CustomerType dto.CustomerType in DbSet<CustomerType>
      on Property(dto, "CustomerTypeId") equals Property(dto.CustomerType, "Id")
      join CustomerTypeAccessRule dto.CustomerType.CustomerTypeAccessRule in DbSet<CustomerTypeAccessRule>
      on Property(dto.CustomerType, "CustomerTypeAccessRuleId") equals Property(dto.CustomerType.CustomerTypeAccessRule, "Id")
      where
          (from AddressDto a in List<AddressDto> ToList(
              source:
                  from Address dto in DbSet<Address>
                  where Property(dto, "Id") == Property(dto, "CustomerId")
                  select new AddressDto{
                      dto.CityCity = [dto].City,
                      dto.StreetStreet = [dto].Street
                  }

          )

          where a.City == "Berlin"
          select a)
          .Any()
      select new CustomerDto{
          dto.CustomerType.CustomerTypeAccessRule.NameCustomerTypeAccessRuleName = [dto.CustomerType.CustomerTypeAccessRule].Name,
          dto.CustomerType.NameCustomerTypeName = [dto.CustomerType].Name,
          List<AddressDto> ToList(
              source:
                  from Address dto in DbSet<Address>
                  where Property(dto, "Id") == Property(dto, "CustomerId")
                  select new AddressDto{
                      dto.CityCity = [dto].City,
                      dto.StreetStreet = [dto].Street
                  }

          )
          Addresses = {from Address dto in value(Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1[EntityFrameworkClientSideJoin.Address]) where (Property([dto], "Id") == Property([dto], "CustomerId")) select new AddressDto() {City = [dto].City, Street = [dto].Street}}.ToList()
      }
      '
warn: Microsoft.EntityFrameworkCore.Query.Internal.SqlServerQueryCompilationContextFactory[8]
      => Microsoft.EntityFrameworkCore.Query.Internal.SqlServerQueryModelVisitor
      The LINQ expression '(Property([dto], "Id") == Property([dto], "CustomerId"))' could not be translated and will be evaluated locally. To configure this warning use the DbContextOptionsBuilder.ConfigureWarnings API (event id 'RelationalEventId.QueryClientEvaluationWarning'). ConfigureWarnings can be used when overriding the DbContext.OnConfiguring method or using AddDbContext on the application service provider.
warn: Microsoft.EntityFrameworkCore.Query.Internal.SqlServerQueryCompilationContextFactory[8]
      => Microsoft.EntityFrameworkCore.Query.Internal.SqlServerQueryModelVisitor
      The LINQ expression '([a].City == "Berlin")' could not be translated and will be evaluated locally. To configure this warning use the DbContextOptionsBuilder.ConfigureWarnings API (event id 'RelationalEventId.QueryClientEvaluationWarning'). ConfigureWarnings can be used when overriding the DbContext.OnConfiguring method or using AddDbContext on the application service provider.
warn: Microsoft.EntityFrameworkCore.Query.Internal.SqlServerQueryCompilationContextFactory[8]
      => Microsoft.EntityFrameworkCore.Query.Internal.SqlServerQueryModelVisitor
      The LINQ expression 'Any()' could not be translated and will be evaluated locally. To configure this warning use the DbContextOptionsBuilder.ConfigureWarnings API (event id 'RelationalEventId.QueryClientEvaluationWarning'). ConfigureWarnings can be used when overriding the DbContext.OnConfiguring method or using AddDbContext on the application service provider.
warn: Microsoft.EntityFrameworkCore.Query.Internal.SqlServerQueryCompilationContextFactory[8]
      => Microsoft.EntityFrameworkCore.Query.Internal.SqlServerQueryModelVisitor
      The LINQ expression '{from AddressDto a in {from Address dto in value(Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1[EntityFrameworkClientSideJoin.Address]) where (Property([dto], "Id") == Property([dto], "CustomerId")) select new AddressDto() {City = [dto].City, Street = [dto].Street}}.ToList() where ([a].City == "Berlin") select [a] => Any()}' could not be translated and will be evaluated locally. To configure this warning use the DbContextOptionsBuilder.ConfigureWarnings API (event id 'RelationalEventId.QueryClientEvaluationWarning'). ConfigureWarnings can be used when overriding the DbContext.OnConfiguring method or using AddDbContext on the application service provider.
warn: Microsoft.EntityFrameworkCore.Query.Internal.SqlServerQueryCompilationContextFactory[8]
      => Microsoft.EntityFrameworkCore.Query.Internal.SqlServerQueryModelVisitor
      The LINQ expression '(Property([dto], "Id") == Property([dto], "CustomerId"))' could not be translated and will be evaluated locally. To configure this warning use the DbContextOptionsBuilder.ConfigureWarnings API (event id 'RelationalEventId.QueryClientEvaluationWarning'). ConfigureWarnings can be used when overriding the DbContext.OnConfiguring method or using AddDbContext on the application service provider.
warn: Microsoft.EntityFrameworkCore.Query.Internal.SqlServerQueryCompilationContextFactory[8]
      => Microsoft.EntityFrameworkCore.Query.Internal.SqlServerQueryModelVisitor
      The LINQ expression '([a].City == "Berlin")' could not be translated and will be evaluated locally. To configure this warning use the DbContextOptionsBuilder.ConfigureWarnings API (event id 'RelationalEventId.QueryClientEvaluationWarning'). ConfigureWarnings can be used when overriding the DbContext.OnConfiguring method or using AddDbContext on the application service provider.
warn: Microsoft.EntityFrameworkCore.Query.Internal.SqlServerQueryCompilationContextFactory[8]
      => Microsoft.EntityFrameworkCore.Query.Internal.SqlServerQueryModelVisitor
      The LINQ expression 'Any()' could not be translated and will be evaluated locally. To configure this warning use the DbContextOptionsBuilder.ConfigureWarnings API (event id 'RelationalEventId.QueryClientEvaluationWarning'). ConfigureWarnings can be used when overriding the DbContext.OnConfiguring method or using AddDbContext on the application service provider.
dbug: Microsoft.EntityFrameworkCore.Query.Internal.SqlServerQueryCompilationContextFactory[5]
      => Microsoft.EntityFrameworkCore.Query.Internal.SqlServerQueryModelVisitor
      TRACKED: False
      (QueryContext queryContext) => IEnumerable<CustomerDto> _Select(
          source: IEnumerable<TransparentIdentifier<TransparentIdentifier<ValueBuffer, ValueBuffer>, ValueBuffer>> _Where(
              source: IEnumerable<TransparentIdentifier<TransparentIdentifier<ValueBuffer, ValueBuffer>, ValueBuffer>> _ShapedQuery(
                  queryContext: queryContext,
                  shaperCommandContext: SelectExpression:
                      SELECT [dto].[Id], [dto.CustomerType.CustomerTypeAccessRule].[Name], [dto.CustomerType].[Name]
                      FROM [Customers] AS [dto]
                      INNER JOIN [CustomerTypes] AS [dto.CustomerType] ON [dto].[CustomerTypeId] = [dto.CustomerType].[Id]
                      INNER JOIN [CustomerTypeAccessRules] AS [dto.CustomerType.CustomerTypeAccessRule] ON [dto.CustomerType].[CustomerTypeAccessRuleId] = [dto.CustomerType.CustomerTypeAccessRule].[Id]
                  ,
                  shaper: CompositeShaper`3
              )
              ,
              predicate: (TransparentIdentifier<TransparentIdentifier<ValueBuffer, ValueBuffer>, ValueBuffer> t1) => bool Any(
                  source: IEnumerable<AddressDto> _Where(
                      source: List<AddressDto> ToList(
                          source: IEnumerable<AddressDto> _Select(
                              source: IEnumerable<ValueBuffer> _Where(
                                  source: IEnumerable<ValueBuffer> _ShapedQuery(
                                      queryContext: queryContext,
                                      shaperCommandContext: SelectExpression:
                                          SELECT [dto1].[CustomerId], [dto1].[City], [dto1].[Street]
                                          FROM [Address] AS [dto1]
                                      ,
                                      shaper: ValueBufferShaper
                                  )
                                  ,
                                  predicate: (ValueBuffer dto) => try { (int) object t1.Outer.Outer.get_Item(0) } catch (Exception) { ... }  == try { (int) object dto.get_Item(0) } catch (Exception) { ... }
                              )
                              ,
                              selector: (ValueBuffer dto) => new AddressDto{
                                  try { (string) object dto.get_Item(1) } catch (Exception) { ... } City = try { ... }, 
                                  try { (string) object dto.get_Item(2) } catch (Exception) { ... } Street = try { ... }
                              }
                          )
                      )
                      ,
                      predicate: (AddressDto a) => a.City == "Berlin"
                  )
              )
          )
          ,
          selector: (TransparentIdentifier<TransparentIdentifier<ValueBuffer, ValueBuffer>, ValueBuffer> t1) => new CustomerDto{
              try { (string) object t1.Inner.get_Item(1) } catch (Exception) { ... } CustomerTypeAccessRuleName = try { ... },
              try { (string) object t1.Outer.Inner.get_Item(2) } catch (Exception) { ... } CustomerTypeName = try { ... },
              List<AddressDto> ToList(
                  source: IEnumerable<AddressDto> _Select(
                      source: IEnumerable<ValueBuffer> _InjectParameters(
                          queryContext: queryContext,
                          source: IEnumerable<ValueBuffer> _ShapedQuery(
                              queryContext: queryContext,
                              shaperCommandContext: SelectExpression:
                                  SELECT [dto2].[City], [dto2].[Street]
                                  FROM [Address] AS [dto2]
                                  WHERE @_outer_Id = [dto2].[CustomerId]
                              ,
                              shaper: ValueBufferShaper
                          )
                          ,
                          parameterNames: new string[]{ "_outer_Id" }
                          ,
                          parameterValues: new object[]{ (object) try { (Nullable<int>) object t1.Outer.Outer.get_Item(0) } catch (Exception) { ... }  }
                      )
                      ,
                      selector: (ValueBuffer dto) => new AddressDto{
                          try { (string) object dto.get_Item(0) } catch (Exception) { ... } City = try { ... },
                          try { (string) object dto.get_Item(1) } catch (Exception) { ... } Street = try { ... }
                      }
                  )
              )
              Addresses = _Select(_InjectParameters(queryContext, _ShapedQuery(queryContext, value(Internal.ShaperCommandContext), value(ExpressionVisitors.Internal.ValueBufferShaper)), new [] {"_outer_Id"}, new [] {Convert(try { ... })}), dto => new AddressDto() {City = try { ... }, Street = try { ... }}).ToList()
          }
      )

The issue

The Where(x => x.Relation.Any()) query warns about client side evaluation when using projected query.

Further technical details

EF Core version: 1.1.0-preview1
Operating system: Windows 10
Visual Studio version: VS 2015

Other details about my project setup: Query is projected using AutoMapper.

[maumar]

The problem here is that AutoMapper introduces ToList() inside ProjectTo method. ToList forces client evaluation on everything that happens after it. Essentially the query that is produced is as follows:

                ctx.Customers.Select(c => new CustomerDto
                {
                    CustomerTypeAccessRuleName = c.CustomerType.CustomerTypeAccessRule.Name,
                    CustomerTypeName = c.CustomerType.Name,
                    Addresses = c.Addresses.Select(a => new AddressDto { City = a.City, Street = a.Street }).ToList() // <- this ToList call is a problem
                }).Where(c => c.Addresses.Any(a => a.City == "Berlin")); // <- this is client-evaled due to ToList call on Addresses

Similar query but without AutoMapper:

               ctx.Customers.Select(c => new
                {
                    CustomerTypeAccessRuleName = c.CustomerType.CustomerTypeAccessRule.Name,
                    CustomerTypeName = c.CustomerType.Name,
                    Addresses = c.Addresses.Select(a => new { City = a.City, Street = a.Street })
                }).Where(c => c.Addresses.Any(a => a.City == "Berlin"));

produces the following SQL:

SELECT [c.CustomerType.CustomerTypeAccessRule].[Name], [c.CustomerType].[Name], [c].[Id]
FROM [Customers] AS [c]
INNER JOIN [CustomerTypes] AS [c.CustomerType] ON [c].[CustomerTypeId] = [c.CustomerType].[Id]
INNER JOIN [CustomerTypeAccessRules] AS [c.CustomerType.CustomerTypeAccessRule] ON [c.CustomerType].[CustomerTypeAccessRuleId] = [c.CustomerType.CustomerTypeAccessRule].[Id]
WHERE EXISTS (
    SELECT 1
    FROM (
        SELECT [a0].[City], [a0].[Street]
        FROM [Address] AS [a0]
        WHERE [c].[Id] = [a0].[CustomerId]
    ) AS [t]
    WHERE [t].[City] = N'Berlin')

[divega]

@anpete @maumar it seems to me that the presence of ToList() could be used as a hint to opt out from streaming for cases like this and in general for #4007. Thoughts?