HasQueryFilter generates incorrect sql, causing "multi-part identifier could not be bound"

[ngbernard]

I have two classes, Plant and UserPlant. UserPlant is a lookup table that ties Users from another data source to Plant (so I'm not mapping User). Both inherit from a base class that provides some properties common to my data objects.

public class MyDataObject
    {
        public long? Id { get; set; }
        
        public DateTime? Created { get; set; }
        
        public DateTime? Edited { get; set; }

        public bool? Active { get; set; }
    }

public class Plant: MyDataObject
    {
        public string Name { get; set; }

        public List<UserPlant> UserPlants { get; set; }
    }

public class UserPlant: MyDataObject  
    {
        public long PlantId { get; set; }

        public long UserId { get; set; }

        public Plant Plant { get; set; }
    }

My mappings look like this:

modelBuilder.Entity<Plant>().HasKey(x=>x.Id);

modelBuilder.Entity<UserPlant>().HasKey(x => new { x.UserId, x.PlantId });
modelBuilder.Entity<UserPlant>().HasOne(x => x.Plant).WithMany(x => x.UserPlants).HasForeignKey(x=>x.PlantId);
modelBuilder.Entity<UserPlant>().HasQueryFilter(x => x.Active.HasValue && x.Active.Value);

When I run

_myDbContext.Plant.Include(p => p.UserPlants)

I get

Exception message:
 'The multi-part identifier "t.Id" could not be bound.'

This is the generated sql:

SELECT [p].[Id], [p].[Active], [p].[Created], [p].[Edited], [p].[Name]
FROM [Plant] AS [p]
ORDER BY [p].[Id]

SELECT [u].[UserId], [u].[PlantId], [u].[Active], [u].[Created], [u].[Edited], [u].[Id]
FROM [UserPlant] AS [u]
ORDER BY [t].[Id]

The 2nd query seems to be the problem, referencing an alias 't' that doesn't exist. However, if I remove the HasQueryFilter mapping, it works. What am I doing wrong?

Further technical details

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

[smitpatel]

@ngbernard - Thanks for reporting this. There is nothing wrong you are doing. This seem to be bug in EFCore. The important point here is x.Active.HasValue && x.Active.Value is not server translatable hence the collection include query gets client based join and things go wrong afterwards in query execution.

As a work-around can you try setting query as x.Active == true and see if it works?

[ngbernard]

@smitpatel I tried it and that did work, thanks! I also just found another workaround: if I also add a similar HasQueryFilter to Plant, like so

modelBuilder.Entity<Plant>().HasQueryFilter(x => x.Active.HasValue && x.Active.Value);

for some reason EF produces correct sql. I meant to do that anyway so that's what I'm going with, but I'll keep your solution in mind for other cases.

[maumar]

relinq now translates nullableProperty.HasValue to: nullableProperty != null, which we can fully translate. Closing