Efcore: Query: Prevent client evaluation when using certain result operators in complex query

[AsValeO]

Steps to reproduce

                        db.ProxyServerLoadResult.AsNoTracking()
                            .Include(x => x.ProxyServer)
                            .Where(x => !x.ProxyServer.BlockedByOperator)
                            .Where(x => x.SourceId == 1)
                            .OrderByDescending(x => x.Score)
                            .Take(100)
                            .Select(x => x.ProxyServer)
                            .ToListAsync();

The issue

I guess this warning is wrong. Am I right?

Exception message:

Warning as error exception for warning 'RelationalEventId.QueryClientEvaluationWarning': The LINQ expression 'join ProxyServer x.ProxyServer in value(Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1[RP.DataModel.ProxyServer]) on Property([x], "ProxyServerId") equals Property([x.ProxyServer], "ProxyServerId")' could not be translated and will be evaluated locally. To suppress this Exception use the DbContextOptionsBuilder.ConfigureWarnings API. ConfigureWarnings can be used when overriding the DbContext.OnConfiguring method or using AddDbContext on the application service provider.

Further technical details

EF Core version: 1.1.0
Operating system: Windows 10
Visual Studio version: VS2015U3

[maumar]

@AsValeO can you also post your model? Specifically code listings of all entities used and the contents of OnModelCreating method on the DbContext

[AsValeO]

ProxyServer.cs - entity

    public partial class ProxyServer
    {
        public ProxyServer()
        {
            BadUnit = new HashSet<BadUnit>();
            ProxyServerLoadResult = new HashSet<ProxyServerLoadResult>();
        }

        public long ProxyServerId { get; set; }
        public string Ip { get; set; }
        public int Port { get; set; }
        public DateTime Added { get; set; }
        public bool BlockedByOperator { get; set; }

        public virtual ICollection<BadUnit> BadUnit { get; set; }
        public virtual ICollection<ProxyServerLoadResult> ProxyServerLoadResult { get; set; }
    }
}

ProxyServerLoadResult.cs - entity

    public partial class ProxyServerLoadResult
    {
        public long ProxyServerLoadResultId { get; set; }
        public long ProxyServerId { get; set; }
        public int SourceId { get; set; }
        public long Score { get; set; }

        public virtual ProxyServer ProxyServer { get; set; }
        public virtual Source Source { get; set; }
    }

OnModelCreating

modelBuilder.Entity<ProxyServer>(entity =>
            {
                entity.HasIndex(e => new { e.Ip, e.Port })
                    .HasName("IX_ProxyServer_IP_Port");

                entity.Property(e => e.ProxyServerId).HasColumnName("ProxyServerID");

                entity.Property(e => e.Added).HasColumnType("datetime");

                entity.Property(e => e.Ip)
                    .IsRequired()
                    .HasColumnName("IP")
                    .HasMaxLength(15);
            });

            modelBuilder.Entity<ProxyServerLoadResult>(entity =>
            {
                entity.HasIndex(e => new { e.SourceId, e.Score })
                    .HasName("nci_wi_ProxyServerLoadResult_0F32BF20F9B2DBAE74BA214E700E391E");

                entity.HasIndex(e => new { e.Score, e.ProxyServerId, e.SourceId })
                    .HasName("nci_wi_ProxyServerLoadResult_D0FDB6C5A7576BE3B1183549EEE8F39B");

                entity.HasIndex(e => new { e.SourceId, e.ProxyServerLoadResultId, e.ProxyServerId })
                    .HasName("nci_wi_ProxyServerLoadResult_EC64396D111AEDB6FB4EFC9ECD3699AD");

                entity.Property(e => e.ProxyServerLoadResultId).HasColumnName("ProxyServerLoadResultID");

                entity.Property(e => e.ProxyServerId).HasColumnName("ProxyServerID");

                entity.Property(e => e.SourceId).HasColumnName("SourceID");

                entity.HasOne(d => d.ProxyServer)
                    .WithMany(p => p.ProxyServerLoadResult)
                    .HasForeignKey(d => d.ProxyServerId);

                entity.HasOne(d => d.Source)
                    .WithMany(p => p.ProxyServerLoadResult)
                    .HasForeignKey(d => d.SourceId)
                    .OnDelete(DeleteBehavior.Restrict);
            });

[maumar]

@AsValeO as a workaround try putting Take(100) at the end of the query like so:

await
                        db.ProxyServerLoadResult.AsNoTracking()
                            .Include(x => x.ProxyServer)
                            .Where(x => !x.ProxyServer.BlockedByOperator)
                            .Where(x => x.SourceId == 1)
                            .OrderByDescending(x => x.Score)
                            .Select(x => x.ProxyServer)
                            .Take(100)
                            .ToListAsync();

this produces a much simpler query from the EF perspective, that we are able to fully translate at the moment. If possible, you should always try to put Skip/Take/Distinct as the last operation when using EF Core (at least until we address the current limitations).

The reason is that those are what we call ResultOperators - they are always the last operation in a given query. If there are more operations following, we introduce subqueries, which complicates the translation significantly for some cases. E.g. the original intermediate model representation for your original query is:

from ProxyServerLoadResult x in 
    (from ProxyServerLoadResult x in DbSet<ProxyServerLoadResult>
    join ProxyServer x.ProxyServer in DbSet<ProxyServer>
    on Property(x, "ProxyServerId") equals Property(x.ProxyServer, "ProxyServerId")
    where !(x.ProxyServer.BlockedByOperator)
    where x.SourceId == 1
    order by x.Score desc
    select x)
    .Take(__p_0)
join ProxyServer x.ProxyServer in DbSet<ProxyServer>
on Property(x, "ProxyServerId") equals Property(x.ProxyServer, "ProxyServerId")
select x.ProxyServer

and the proposed workaround:

(from ProxyServerLoadResult x in DbSet<ProxyServerLoadResult>
join ProxyServer x.ProxyServer in DbSet<ProxyServer>
on Property(x, "ProxyServerId") equals Property(x.ProxyServer, "ProxyServerId")
where !(x.ProxyServer.BlockedByOperator)
where x.SourceId == 1
order by x.Score desc
select x.ProxyServer)
.Take(__p_0)

[AsValeO]

@maumar, I got it, thank you!

[maumar]

This is fixed in the current bits and produces the following query:

SELECT TOP(@__p_0) [x.ProxyServer].[ProxyServerID], [x.ProxyServer].[Added], [x.ProxyServer].[BlockedByOperator], [x.ProxyServer].[IP], [x.ProxyServer].[Port]
FROM [ProxyServerLoadResults] AS [x]
INNER JOIN [ProxyServers] AS [x.ProxyServer] ON [x].[ProxyServerID] = [x.ProxyServer].[ProxyServerID]
WHERE ([x.ProxyServer].[BlockedByOperator] = 0) AND ([x].[SourceID] = 1)
ORDER BY [x].[Score] DESC

[mguinness]

@maumar I think I've encountered this issue also. When you say fixed, do you mean this will be included in the Entity Framework Core 2.0 release?

EDIT: I see it was included in preview 1
https://github.com/aspnet/EntityFramework/releases/tag/rel%2F2.0.0-preview1