Multiple MIN, MAX,... () OVER(PARTITION BY...) statements in a select query not possible

[RubenDelange]

Lovely extension!

I do notice that it is not possible to do multiple MIN, MAX,... () OVER(PARTITION BY...) statements in a select query. Example: https://learn.microsoft.com/en-us/sql/t-sql/functions/max-transact-sql?view=sql-server-ver16#b-using-the-over-clause

Only the first one is actually translated into the correct SQL statement. The other ones are ignored.

[virzak]

@RubenDelange Thanks!

Do you mind pasting your LINQ here?

[RubenDelange]

It's something like this:

.Where(ol => ol.Order.State == "SomeState")
.Select(ol => new OrderLineByDepartmentDto
{
    DepartmentId = ol.Order.DepartmentId,
    DepartmentName = ol.Order.Department.Name,
    DepartmentCreatedDate = ol.Order.Department.CreatedDate,
    MinTotalPrice = EF.Functions.Min(ol.TotalPrice, EF.Functions.Over().PartitionBy(ol.Order.DepartmentId)),
    MinPrice = EF.Functions.Min(ol.Price, EF.Functions.Over().PartitionBy(ol.Order.DepartmentId)),
    MinNumberOfItems = EF.Functions.Min(ol.NumberOfItems, EF.Functions.Over().PartitionBy(ol.Order.DepartmentId)),
})
.Distinct()
.OrderByDescending(olbd => olbd.DepartmentCreatedDate)
.ToListAsync()

In this example, only the MinTotalPrice = ... is translated into SQL. If you swap around the MinXyz properties, only the first one is translated into SQL, the rest are ignored.

[virzak]

LINQ

var query = context.TestRows
.Select(r => new
{
    Min = EF.Functions.Min(r.Id, EF.Functions.Over().PartitionBy(r.Id / 10)),
    Max = EF.Functions.Max(r.Date, EF.Functions.Over().PartitionBy(r.Col1 / 10)),
    Avg = EF.Functions.Avg(r.Id, EF.Functions.Over().PartitionBy(r.Col1 / 500)),
})
.Distinct();

Translates into

SELECT DISTINCT MIN([t].[Id]) OVER(PARTITION BY [t].[Id] / 10 ) AS [Min], MAX([t].[Date]) OVER(PARTITION BY [t].[Col1] / 10 ) AS [Max], AVG([t].[Id]) OVER(PARTITION BY [t].[Col1] / 500 ) AS [Avg]
FROM [TestRows] AS [t]

```cs using Microsoft.EntityFrameworkCore; using System; using System.Collections.Immutable; using System.Linq; using Zomp.EFCore.WindowFunctions; using Zomp.EFCore.WindowFunctions.SqlServer; using var context = new TestDbContext(); await context.Database.EnsureCreatedAsync(); var query = context.TestRows .Select(r => new { Min = EF.Functions.Min(r.Id, EF.Functions.Over().PartitionBy(r.Id / 10)), Max = EF.Functions.Max(r.Date, EF.Functions.Over().PartitionBy(r.Col1 / 10)), Avg = EF.Functions.Avg(r.Id, EF.Functions.Over().PartitionBy(r.Col1 / 500)), }) .Distinct(); Console.WriteLine(query.ToQueryString()); (await query.ToListAsync()).ForEach(z => Console.WriteLine(z)); public record TestRow(int Id, int? Col1, Guid SomeGuid, DateTime Date, byte[] IdBytes) { public static ImmutableArray CreateTestRows() => ImmutableArray.CreateRange(new TestRow[] { new(2, null, new("ab988b94-a7d3-413d-92a0-8a03c47dd0f4"), new(2022, 01, 01), new byte[] { 0, 2 }), new(3, 10, new("41b1d4c5-e629-4a23-9514-47857e6c5ad2"), new(2022, 01, 02), new byte[] { 0, 3 }), new(5, -1, new("5a425c36-3a87-4d55-80a0-0f449897daa4"), new(2022, 01, 03), new byte[] { 0, 5 }), new(7, null, new("a3517ee2-e14c-4c63-8a12-353eee1c01f9"), new(2022, 01, 04), new byte[] { 0, 7 }), new(11, null, new("2ce77fd7-937d-4041-9b62-47b7d7a7ef09"), new(2022, 01, 05), new byte[] { 0, 11 }), new(13, -12, new("2b71c0d2-11ac-4f6c-af5e-5c470202e222"), new(2022, 01, 06), new byte[] { 0, 13 }), new(17, null, new("5c3330cd-a93b-4d6f-b31c-efb735315993"), new(2022, 01, 07), new byte[] { 0, 17 }), new(19, null, new("e1c5f8f1-18ca-423d-a35e-b9c54f6897d4"), new(2022, 01, 08), new byte[] { 0, 19 }), new(23, 1759, new("d288c0f2-b2a2-4eef-98cc-1f4726f1277c"), new(2022, 01, 09), new byte[] { 0, 23 }), }); } public class TestDbContext : DbContext { public DbSet TestRows { get; set; } = null!; protected override void OnModelCreating(ModelBuilder modelBuilder) { modelBuilder.Entity().Property(x => x.Id).ValueGeneratedNever(); modelBuilder.Entity().HasData(TestRow.CreateTestRows()); } protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) { optionsBuilder //.LogTo(Console.WriteLine).EnableSensitiveDataLogging() .UseSqlServer(FiddleHelper.GetConnectionStringSqlServer(), o => o.UseWindowFunctions()); } } ```

and fiddle

I suggest that if the problem persists you modify a fiddle or submit a PR with test case that clearly reproduces this issue.

[RubenDelange]

Yeah, it seems like only 1 combination of [WindowFunction ; PartitionBy] is translated into SQL. So if you have multiple EF.Functions.Min(columnDoesntMatter, EF.Functions.Over().PartitionBy(partitionKey), only the first one is translated.

Fiddle that reproduces the issue

[virzak]

@RubenDelange thanks for reporting this.

Version 1.0.2 is on NuGet and this should be fixed. Please reopen this issue if that's not the case.