Can't translate let expression only when filtering or ordering by it

[TanielianVB]

Hi,
With the following tables:

CREATE TABLE public."Things"
(
    "Guid" bigint NOT NULL,
    CONSTRAINT "Things_pkey" PRIMARY KEY ("Guid")
)
CREATE TABLE public."Properties"
(
    "Guid" bigint NOT NULL,
    "ThingId" bigint NOT NULL,
    "Type" smallint NOT NULL,
    "Identifier" text COLLATE pg_catalog."default" NOT NULL,
    "Value" text COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT "Properties_pkey" PRIMARY KEY ("Guid"),
    CONSTRAINT "Things_fkey" FOREIGN KEY ("ThingId")
        REFERENCES public."Things" ("Guid") MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        NOT VALID
)

The following sample:

    public enum PropertyType : short
    {
        Bool,
        Byte,
        DateTime,
        Decimal,
        Double,
        Float,
        Int,
        Long,
        Short,
        String,
    }

    public class EFCoreTestContext : DbContext
    {
        public DbSet<Thing> Things { get; set; }
        public DbSet<Property> Properties { get; set; }

        public static readonly ILoggerFactory DebugLoggerFactory = LoggerFactory.Create(builder => { builder.AddFilter((category, level) => category == DbLoggerCategory.Database.Command.Name && level == LogLevel.Information).AddConsole(); });

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) => optionsBuilder
            .UseLoggerFactory(DebugLoggerFactory)
            .UseNpgsql("Host=localhost;Database=EFCoreTest;Username=test;Password=test");

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder
                .Entity<Thing>(eb =>
                {
                    eb.ToTable("Things");
                    eb.HasKey(e => e.Guid);
                })
                .Entity<Property>(eb =>
                {
                    eb.ToTable("Properties");
                    eb.HasKey(e => e.Guid);
                });
        }
    }

    public class Thing
    {
        public long Guid { get; set; }
    }

    public class Property
    {
        public long Guid { get; set; }

        public long ThingId { get; set; }

        public PropertyType Type { get; set; }

        public string Identifier { get; set; }

        public string Value { get; set; }
    }

    class Program
    {
        static void Main(string[] args)
        {
            using (var db = new EFCoreTestContext())
            {
                var q = (from price in db.Properties
                         where price.Identifier == "Price"
                         orderby Convert.ToDouble(price.Value) descending
                         select new
                         {
                             price.ThingId,
                             Value = Convert.ToDouble(price.Value) as double?
                         });

                var r = (from thing in db.Things
                         let value = q.Where(p => p.ThingId == thing.Guid).Select(p => p.Value).FirstOrDefault()
                         select new
                         {
                             Value = value
                         });

                var r1 = r.ToList();
                
                Console.WriteLine();
                
                try
                {
                    var r2 = r.OrderBy(e => e.Value).ToList();
                }
                catch (Exception ex)
                {
                    Console.ForegroundColor = ConsoleColor.Red;
                    Console.WriteLine(ex.Message);
                }
                
                Console.WriteLine();
                
                try
                {
                    var r3 = r.Where(e => e.Value > 0).ToList();
                }
                catch (Exception ex)
                {
                    Console.ForegroundColor = ConsoleColor.Red;
                    Console.WriteLine(ex.Message);
                }
            }
            
            Console.ReadKey();
        }
    }

I'm getting this output:

--info: Microsoft.EntityFrameworkCore.Database.Command[20101]
--Executed DbCommand (4ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT t1.c, t1.c0
FROM "Things" AS t
LEFT JOIN (
    SELECT t0.c, t0.c0, t0."Guid", t0."ThingId"
    FROM (
        SELECT CAST(p."Value" AS double precision) AS c, 1 AS c0, p."Guid", p."ThingId", ROW_NUMBER() OVER(PARTITION BY p."ThingId" ORDER BY CAST(p."Value" AS double precision) DESC) AS row
        FROM "Properties" AS p
        WHERE p."Identifier" = 'Price'
    ) AS t0
    WHERE t0.row <= 1
) AS t1 ON t."Guid" = t1."ThingId"

//The LINQ expression 'DbSet<Thing>
.OrderBy(t => DbSet<Property>
    .Where(p => p.Identifier == "Price")
    .OrderByDescending(p => Convert.ToDouble(p.Value))
    .Where(p => p.ThingId == t.Guid)
    .Select(p => (Convert.ToDouble(p.Value) as Nullable<double>))
    .FirstOrDefault())//' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

//The LINQ expression 'DbSet<Thing>
.Where(t => DbSet<Property>
    .Where(p => p.Identifier == "Price")
    .OrderByDescending(p => Convert.ToDouble(p.Value))
    .Where(p => p.ThingId == t.Guid)
    .Select(p => (Convert.ToDouble(p.Value) as Nullable<double>))
    .FirstOrDefault() > (Nullable<double>)0)//' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

I don't understand why it is not working if the value is correctly calculated on the database side.

[roji]

/cc @smitpatel

[roji]

Can you please move this to https://github.com/dotnet/efcore? Also, please specify which precise version of EF Core you're using.

[TanielianVB]

Ok. Thank you.

[TanielianVB]

Opened dotnet/efcore#20179