Can't translate property AS Nullable<double>

[TanielianVB]

Hi,
When filtering or ordering a property filled from a let variable I get the "could not be translated" error.

Steps to reproduce

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")
        .UseSqlServer(@"Data Source=(localDB)\MSSQLLocalDB;Initial Catalog=EFCoreTest;Integrated Security=True");

    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.

Further technical details

EF Core version: 3.1.2
Database provider: Microsoft.EntityFrameworkCore.SqlServer or Npgsql.EntityFrameworkCore.PostgreSQL both 3.1.2
Target framework: (e.g. .NET Framework 4.7.2
Operating system: Windows
IDE: Visual Studio 2019 16.4.5

[maumar]

verified this fails in 3.1.2 and works on current master. We generate the following sql:

SELECT (
    SELECT TOP(1) CONVERT(float, [p].[Value])
    FROM [Properties] AS [p]
    WHERE ([p].[Identifier] = N'Price') AND ([p].[ThingId] = [t].[Guid])
    ORDER BY CONVERT(float, [p].[Value]) DESC) AS [Value]
FROM [Things] AS [t]
ORDER BY (
    SELECT TOP(1) CONVERT(float, [p0].[Value])
    FROM [Properties] AS [p0]
    WHERE ([p0].[Identifier] = N'Price') AND ([p0].[ThingId] = [t].[Guid])
    ORDER BY CONVERT(float, [p0].[Value]) DESC)

[maumar]

@TanielianVB as a workaround, you can use hard cast to double? instead of soft, like so:

                var q = (from price in db.Properties
                         where price.Identifier == "Price"
                         orderby Convert.ToDouble(price.Value) descending
                         select new
                         {
                             price.ThingId,
                             Value = (double?)Convert.ToDouble(price.Value) //as double?
                         });

[maumar]

dupe of #5783

[maumar]

@TanielianVB the reason why the first query worked is that the cast was in the projection - EF allows client evaluation of projections. In the second query, you composed on top of it, and that caused an error. Any operation (where, distinct etc) would have had the same result

[TanielianVB]

Thank you!