Query: Nullable foreign-key can lead to client evaluation

[axelheer]

Using nullable foreign-keys seems to lead to client evaluation more often.

Exception message: Warning as error exception for warning 'RelationalEventId.QueryClientEvaluationWarning': The LINQ expression '(Property([parent.Partner], "Id") == Property([child], "ParentId"))' 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.
Stack trace:
   at Microsoft.EntityFrameworkCore.Internal.InterceptingLogger`1.Log[TState](LogLevel logLevel, EventId eventId, TState state, Exception exception, Func`3 formatter)
   at Microsoft.EntityFrameworkCore.Infrastructure.SensitiveDataLogger`1.Microsoft.Extensions.Logging.ILogger.Log[TState](LogLevel logLevel, EventId eventId, TState state, Exception exception, Func`3 formatter)
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalLoggerExtensions.LogWarning(ILogger logger, RelationalEventId eventId, Func`1 formatter)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor.VisitWhereClause(WhereClause whereClause, QueryModel queryModel, Int32 index)
   at Remotion.Linq.QueryModelVisitorBase.VisitBodyClauses(ObservableCollection`1 bodyClauses, QueryModel queryModel)
   at Remotion.Linq.QueryModelVisitorBase.VisitQueryModel(QueryModel queryModel)
   at Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor.VisitQueryModel(QueryModel queryModel)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor.VisitQueryModel(QueryModel queryModel)
   at Microsoft.EntityFrameworkCore.Query.Internal.SqlServerQueryModelVisitor.VisitQueryModel(QueryModel queryModel)
   at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.RelationalEntityQueryableExpressionVisitor.VisitSubQuery(SubQueryExpression expression)
   at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.ExpressionVisitorBase.Visit(Expression node)
   at System.Linq.Expressions.ExpressionVisitor.VisitConditional(ConditionalExpression node)
   at System.Linq.Expressions.ConditionalExpression.Accept(ExpressionVisitor visitor)
   at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.ExpressionVisitorBase.Visit(Expression node)
   at System.Linq.Expressions.ExpressionVisitor.VisitAndConvert[T](ReadOnlyCollection`1 nodes, String callerName)
   at Remotion.Linq.Parsing.RelinqExpressionVisitor.VisitNew(NewExpression expression)
   at System.Linq.Expressions.NewExpression.Accept(ExpressionVisitor visitor)
   at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.ExpressionVisitorBase.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor.ReplaceClauseReferences(Expression expression, IQuerySource querySource, Boolean inProjection)
   at Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor.VisitSelectClause(SelectClause selectClause, QueryModel queryModel)
   at Remotion.Linq.QueryModelVisitorBase.VisitQueryModel(QueryModel queryModel)
   at Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor.VisitQueryModel(QueryModel queryModel)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor.VisitQueryModel(QueryModel queryModel)
   at Microsoft.EntityFrameworkCore.Query.Internal.SqlServerQueryModelVisitor.VisitQueryModel(QueryModel queryModel)
   at Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor.CreateQueryExecutor[TResult](QueryModel queryModel)

Steps to reproduce

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Data.SqlClient;
using System.Linq;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Infrastructure;
using Microsoft.EntityFrameworkCore.Metadata;

// --- Models ------------------------------------------------------------------

public class Parent
{
    [Key]
    public Guid Id { get; set; }

    [Required]
    public string Name { get; set; }

    // HINT: making this non-nullable leads to translateable expression
    public Guid? PartnerId { get; set; }

    public Parent Partner { get; set; }

    public ISet<ParentChild> Children { get; } = new HashSet<ParentChild>();
}

public class ParentChild
{
    [Key]
    public Guid Id { get; set; }

    public Guid ParentId { get; set; }

    public Parent Parent { get; set; }

    public Guid ChildId { get; set; }

    public Child Child { get; set; }
}

public class Child
{
    [Key]
    public Guid Id { get; set; }

    [Required]
    public string Name { get; set; }

    public ISet<ParentChild> Parents { get; } = new HashSet<ParentChild>();
}

// --- Context -----------------------------------------------------------------

public class ModelContext : DbContext
{
    public DbSet<Parent> Parents { get; set; }

    public DbSet<Child> Children { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // HINT: necessary for non-nullable FK
        // modelBuilder.Entity<Parent>()
        //             .HasOne(parent => parent.Partner)
        //             .WithOne()
        //             .OnDelete(DeleteBehavior.Restrict);
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        var connectionStringBuilder = new SqlConnectionStringBuilder()
        {
            DataSource = "(localdb)\\MSSQLLocalDB",
            InitialCatalog = "EFCore",
            IntegratedSecurity = true
        };

        optionsBuilder.UseSqlServer(connectionStringBuilder.ConnectionString);

        optionsBuilder.ConfigureWarnings(warnings =>
            warnings.Throw(RelationalEventId.QueryClientEvaluationWarning)
        );
    }
}

// --- Test --------------------------------------------------------------------

public class Program
{
    public static void Main(string[] args)
    {
        // Set up database
        using (var context = new ModelContext())
        {
            context.Database.EnsureDeleted();
            context.Database.EnsureCreated();
        }

        // Create query
        using (var context = new ModelContext())
        {
            var query = from parent in context.Parents
                        let hasPartnerAsParent = parent.Partner.Children.Any(child => child.Child.Name == "Narf")
                        where parent.Children.Any(child => child.Child.Name == "Narf")
                        select new
                        {
                            Id = parent.Id,
                            Name = parent.Name,
                            Partner = hasPartnerAsParent ? parent.Partner.Name : null,
                            Children = from child in parent.Children
                                    select new
                                    {
                                        Id = child.Id,
                                        Name = child.Child.Name
                                    }
                        };

            // BOOM: doesn't translate
            query.ToList();
        }
    }
}

Further technical details

EF Core version: 1.1.0
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: Windows 7
IDE: Visual Studio 2015

[axelheer]

Configuring Log instead of Throw unveils two warnings:

warn: Microsoft.EntityFrameworkCore.Query.Internal.SqlServerQueryCompilationContextFactory[8]
      The LINQ expression '(Property([parent.Partner], "Id") == Property([child], "ParentId"))' could not be translated and will be evaluated locally. To configure this warning usethe 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]
      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.
info: Microsoft.EntityFrameworkCore.Storage.IRelationalCommandBuilderFactory[1]
      Executed DbCommand (19ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT [parent].[Id], [parent].[Name], [parent].[PartnerId], [parent.Partner].[Id], [parent.Partner].[Name], [parent.Partner].[PartnerId]
      FROM [Parents] AS [parent]
      LEFT JOIN [Parents] AS [parent.Partner] ON [parent].[PartnerId] = [parent.Partner].[Id]
      WHERE EXISTS (
          SELECT 1
          FROM [ParentChild] AS [child]
          INNER JOIN [Children] AS [child.Child] ON [child].[ChildId] = [child.Child].[Id]
          WHERE ([child.Child].[Name] = N'Narf') AND ([parent].[Id] = [child].[ParentId]))
      ORDER BY [parent].[PartnerId]

I'm analyzing client evaluations of a project and this seems to be a common pattern:

• The LINQ expression '(Convert(Property([parent.Child], "Id")) == Property([other], "ChildId"))' could not be translated and will be evaluated locally.
• The LINQ expression 'Any()' or 'FirstOrDefault()' or 'Sum()' could not be translated and will be evaluated locally.

Maybe the second ones are only subsequent errors, but they should disappear too, of course. 🤔

BTW, I know the sample doesn't make much sense, I'm just trying to reproduce these warnings. Let me know, if you need more elaborated code...

[axelheer]

This code produces more warnings (just to make sure):

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Data.SqlClient;
using System.Linq;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Infrastructure;
using Microsoft.EntityFrameworkCore.Metadata;
using Microsoft.Extensions.Logging;

// --- Models ------------------------------------------------------------------

public class Parent
{
    [Key]
    public Guid Id { get; set; }

    [Required]
    public string Name { get; set; }

    public ParentInfo Info { get; set; }

    public ISet<ParentChild> Children { get; } = new HashSet<ParentChild>();
}

public class ParentInfo
{
    [Key]
    public Guid Id { get; set; }

    [Required]
    public Guid? ParentId { get; set; }

    public Parent Parent { get; set; }

    public Guid? PartnerId { get; set; }

    public Parent Partner { get; set; }

    [Required]
    public string Name { get; set; }
}

public class ParentChild
{
    [Key]
    public Guid Id { get; set; }

    [Required]
    public Guid? ParentId { get; set; }

    public Parent Parent { get; set; }


    [Required]
    public Guid? ChildId { get; set; }

    public Child Child { get; set; }
}

public class Child
{
    [Key]
    public Guid Id { get; set; }

    [Required]
    public string Name { get; set; }

    public ISet<ParentChild> Parents { get; } = new HashSet<ParentChild>();
}

// --- Context -----------------------------------------------------------------

public class ModelContext : DbContext
{
    public DbSet<Parent> Parents { get; set; }

    public DbSet<Child> Children { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Parent>()
                    .HasOne(parent => parent.Info)
                    .WithOne(info => info.Parent)
                    .OnDelete(DeleteBehavior.Restrict);
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        var connectionStringBuilder = new SqlConnectionStringBuilder()
        {
            DataSource = "(localdb)\\MSSQLLocalDB",
            InitialCatalog = "EFCore",
            IntegratedSecurity = true
        };

        optionsBuilder.UseSqlServer(connectionStringBuilder.ConnectionString);

        optionsBuilder.ConfigureWarnings(warnings =>
            warnings.Log(RelationalEventId.QueryClientEvaluationWarning)
        );
    }
}

// --- Test --------------------------------------------------------------------

public class Program
{
    public static void Main(string[] args)
    {
        // Set up database
        using (var context = new ModelContext())
        {
            context.Database.EnsureDeleted();
            context.Database.EnsureCreated();
        }

        // Create query
        using (var context = new ModelContext())
        {
            context.GetService<ILoggerFactory>().AddConsole();

            var childId = Guid.NewGuid();

            var query = from parent in context.Parents
                        let hasPartner = parent.Info.Partner.Children.Any(child => child.ChildId == childId)
                        where parent.Children.Any(child => child.ChildId == childId)
                        select new
                        {
                            Id = parent.Id,
                            Name = parent.Name,
                            Info = parent.Info.Parent,
                            Someone = (from child in parent.Info.Partner.Children
                                       where child.Id != childId
                                       select child.Child.Name).FirstOrDefault(),
                            Partner = hasPartner ? parent.Info.Partner.Name : null
                        };

            query.ToList();
        }
    }
}

[pmiddleton]

Like #7922 this is now working in the dev branch. No client side evaluation is performed in the sample code provided.

[maumar]

Also verified that it works fine in the dev bits, we create the following queries:

First repro:

SELECT [parent.Partner].[Id], [parent.Partner].[Name], [parent.Partner].[PartnerId], [parent].[Id], [parent].[Name], CASE
	WHEN (
		SELECT CASE
			WHEN EXISTS (
				SELECT 1
				FROM [ParentChild] AS [child1]
				INNER JOIN [Children] AS [child.Child1] ON [child1].[ChildId] = [child.Child1].[Id]
				WHERE ([child.Child1].[Name] = N'Narf') AND ([parent.Partner].[Id] = [child1].[ParentId]))
			THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT)
		END
	) = 1
	THEN [parent.Partner].[Name] ELSE NULL
END
FROM [Parents] AS [parent]
LEFT JOIN [Parents] AS [parent.Partner] ON [parent].[PartnerId] = [parent.Partner].[Id]
WHERE EXISTS (
	SELECT 1
	FROM [ParentChild] AS [child]
	INNER JOIN [Children] AS [child.Child] ON [child].[ChildId] = [child.Child].[Id]
	WHERE ([child.Child].[Name] = N'Narf') AND ([parent].[Id] = [child].[ParentId]))

Second repro:

SELECT [parent.Info.Parent].[Id], [parent.Info.Parent].[Name], [parent.Info.Partner].[Id], [parent.Info.Partner].[Name], [parent].[Id], [parent].[Name], (
	SELECT TOP(1) [child.Child0].[Name]
	FROM [ParentChild] AS [child2]
	INNER JOIN [Children] AS [child.Child0] ON [child2].[ChildId] = [child.Child0].[Id]
	WHERE ([child2].[Id] <> @__childId_2) AND ([parent.Info.Partner].[Id] = [child2].[ParentId])
), CASE
	WHEN (
		SELECT CASE
			WHEN EXISTS (
				SELECT 1
				FROM [ParentChild] AS [child3]
				WHERE ([child3].[ChildId] = @__childId_0) AND ([parent.Info.Partner].[Id] = [child3].[ParentId]))
			THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT)
		END
	) = 1
	THEN [parent.Info.Partner].[Name] ELSE NULL
END
FROM [Parents] AS [parent]
LEFT JOIN [ParentInfo] AS [parent.Info] ON [parent].[Id] = [parent.Info].[ParentId]
LEFT JOIN [Parents] AS [parent.Info.Parent] ON [parent.Info].[ParentId] = [parent.Info.Parent].[Id]
LEFT JOIN [Parents] AS [parent.Info.Partner] ON [parent.Info].[PartnerId] = [parent.Info.Partner].[Id]
WHERE EXISTS (
	SELECT 1
	FROM [ParentChild] AS [child]
	WHERE ([child].[ChildId] = @__childId_1) AND ([parent].[Id] = [child].[ParentId]))