Translate Min/Max over inline collection via LEAST/GREATEST #32332
Description
Given the following query:
_ = context.Blogs
.Select(b => new[] { b.Updated, b.Updated2 }.Max())
.ToList();Full code
await using var context = new BlogContext();
await context.Database.EnsureDeletedAsync();
await context.Database.EnsureCreatedAsync();
_ = context.Blogs
.Select(b => new[] { b.Updated, b.Updated2 }.Max())
.ToList();
public class BlogContext : DbContext
{
public DbSet<Blog> Blogs { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
=> optionsBuilder
.UseSqlServer(@"Server=localhost;Database=test;User=SA;Password=Abcd5678;Connect Timeout=60;ConnectRetryCount=0;Encrypt=false")
.LogTo(Console.WriteLine, LogLevel.Information)
.EnableSensitiveDataLogging();
}
public class Blog
{
public int Id { get; set; }
public DateTime Updated { get; set; }
public DateTime Updated2 { get; set; }
public List<Post> Posts { get; set; }
}
public class Post
{
public int Id { get; set; }
public DateTime Updated { get; set; }
public Blog Blog { get; set; }
}EF 8.0 generates the following SQL:
SELECT (
SELECT MAX([v].[Value])
FROM (VALUES ([b].[Updated]), ([b].[Updated2])) AS [v]([Value]))
FROM [Blogs] AS [b]We should recognize Min/Max over an inline collection, and just translate to LEAST/GREATEST:
SELECT GREATEST([b].[Updated], [b].[Updated2])
FROM [Blogs] AS [b]Note that #31681 already tracks adding EF.Functions.{Least,Greatest} for users to invoke directly; this issue tracks changing the Min/Max translation.