Bug report: When Substring is used in GroupBy, the query is not created correctly 6.0.2

[KAJOOSH]

This problem only occurs in version 6.0.2 and does not occur in version 6.0.1.

Repro

ConsoleApp.csproj:

<Project Sdk="Microsoft.NET.Sdk">
  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>net6.0</TargetFramework>
    <ImplicitUsings>enable</ImplicitUsings>
  </PropertyGroup>
  <ItemGroup>
    <PackageReference Include="Microsoft.AspNetCore.Identity.EntityFrameworkCore" Version="6.0.2" />
    <PackageReference Include="Microsoft.EntityFrameworkCore" Version="6.0.2" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Abstractions" Version="6.0.2" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Analyzers" Version="6.0.2" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="6.0.2">
      <PrivateAssets>all</PrivateAssets>
      <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
    </PackageReference>
    <PackageReference Include="Microsoft.EntityFrameworkCore.Relational" Version="6.0.2" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="6.0.2" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="6.0.2">
      <PrivateAssets>all</PrivateAssets>
      <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
    </PackageReference>
  </ItemGroup>
  <ItemGroup>
    <Folder Include="Migrations\" />
  </ItemGroup>
</Project>

ApplicationDbContext.cs:

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.ChangeTracking;
using Microsoft.EntityFrameworkCore.Metadata;
using Microsoft.Extensions.Options;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace ConsoleApp1.Data
{
    public class ApplicationDbContext : DbContext
    {
        public virtual DbSet<Model.DepartmentStaff> CustomerSystemStaff { get; set; }
        public virtual DbSet<Model.Department> Departments { get; set; }
        public virtual DbSet<Model.TicketTask> TicketTasks { get; set; }
        public ApplicationDbContext() : base(GetOptions())
        {
        }
        public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
            : base(options)
        {
        }
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder
               .UseSqlServer("Data Source=.;Initial Catalog=test;User ID=sa;Password=100+200");

            optionsBuilder
             .UseSqlServer(o =>
                 o.UseQuerySplittingBehavior(QuerySplittingBehavior.SplitQuery)
             );
        }
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);
            modelBuilder.Entity<Model.Department>()
            .HasMany<Model.DepartmentStaff>(g => g._DepartmentStaff)
            .WithOne(s => s.Department)
            .HasForeignKey(s => s.DepartmentID)
            .OnDelete(DeleteBehavior.Cascade);

            modelBuilder.Entity<Model.Department>()
            .HasMany<Model.TicketTask>(g => g._TicketTaskActDepartment)
            .WithOne(s => s.ActDepartment)
            .HasForeignKey(s => s.ActDepartmentID)
            .OnDelete(DeleteBehavior.NoAction);

            modelBuilder.Entity<Model.ApplicationUser>()
            .HasMany<Model.Department>(g => g._DepartmentUserManager)
            .WithOne(s => s.UserManager)
            .HasForeignKey(s => s.UserManagerID)
            .OnDelete(DeleteBehavior.NoAction);

            modelBuilder.Entity<Model.ApplicationUser>()
            .HasMany<Model.DepartmentStaff>(g => g._DepartmentStaffUser)
            .WithOne(s => s.User)
            .HasForeignKey(s => s.UserID)
            .OnDelete(DeleteBehavior.NoAction);

            modelBuilder.Entity<Model.ApplicationUser>()
            .HasMany<Model.TicketTask>(g => g._TicketTaskActUser)
            .WithOne(s => s.ActUser)
            .HasForeignKey(s => s.ActUserID)
            .OnDelete(DeleteBehavior.NoAction);

            modelBuilder.Entity<Model.Department>()
            .HasMany<Model.Department>(g => g._Children)
            .WithOne(s => s.Parent)
            .HasForeignKey(s => s.ParentId)
            .OnDelete(DeleteBehavior.NoAction);

            modelBuilder.Entity<Model.TicketTask>().Property(m => m.InsertedDatePersian).HasComputedColumnSql("(format([InsertedDate],'yyyy/MM/dd HH:mm:ss','fa'))").HasColumnName("InsertedDatePersian");
            modelBuilder.Entity<Model.TicketTask>().Property(m => m.DoneDatePersian).HasComputedColumnSql("(format([DoneDate],'yyyy/MM/dd HH:mm:ss','fa'))").HasColumnName("DoneDatePersian");

        }
        private static DbContextOptions<ApplicationDbContext> GetOptions()
        {
            return SqlServerDbContextOptionsExtensions.UseSqlServer(new DbContextOptionsBuilder<ApplicationDbContext>(), "Data Source=.;Initial Catalog=test;User ID=sa;Password=100+200").Options;
        }
    }
}

ApplicationUser.cs:

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace Model
{
    public class ApplicationUser 
    {

        public ApplicationUser()
        {

            this._DepartmentUserManager = new HashSet<Model.Department>();

            this._DepartmentStaffUser = new HashSet<Model.DepartmentStaff>();
            this._TicketTaskActUser = new HashSet<Model.TicketTask>();

        }

        public virtual ICollection<Model.Department> _DepartmentUserManager { get; set; }
        public virtual ICollection<Model.DepartmentStaff> _DepartmentStaffUser { get; set; }
        public virtual ICollection<Model.TicketTask> _TicketTaskActUser { get; set; }

        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int ID { get; set; }

    }
}

Department.cs:

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;
using System.Text;
using System.Text.Json.Serialization;
using System.Threading.Tasks;

namespace Model
{
    [Table("Department")]
    public class Department
    {

        public Department()
        {
            this._DepartmentStaff = new HashSet<Model.DepartmentStaff>();
            this._TicketTaskActDepartment = new HashSet<Model.TicketTask>();
            this._Children = new HashSet<Model.Department>();

        }

        public virtual ICollection<Model.DepartmentStaff> _DepartmentStaff { get; set; }
        public virtual ICollection<Model.TicketTask> _TicketTaskActDepartment { get; set; }
        public virtual ICollection<Model.Department> _Children { get; set; }

        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int ID { get; set; }
        public string Title { get; set; }

        public int? UserManagerID { get; set; }
        public virtual Model.ApplicationUser UserManager { get; set; }

        public int? ParentId { get; set; }
        public virtual Model.Department Parent { get; set; }

    }
}

DepartmentStaff.cs:

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Text;

namespace Model
{
    [Table("DepartmentStaff")]
    public class DepartmentStaff
    {

        public int ID { get; set; }

        public int DepartmentID { get; set; }
        public virtual Model.Department Department { get; set; }

        public int UserID { get; set; }
        public virtual Model.ApplicationUser User { get; set; }

    }
}

Task.cs:

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Text;

namespace Model
{
    [Table("TicketTask")]
    public class TicketTask
    {

        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int ID { get; set; }

        public int TicketID { get; set; }
        //public virtual Model.Ticket Ticket { get; set; }

        [Column(TypeName = "DateTime")]
        public DateTime InsertedDate { get; set; }

        public int? ActDepartmentID { get; set; }
        public virtual Model.Department ActDepartment { get; set; }

        public int? ActUserID { get; set; }
        public virtual Model.ApplicationUser ActUser { get; set; }

        public bool isDone { get; set; }

        [Column(TypeName = "DateTime")]
        public DateTime? DoneDate { get; set; }

        [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
        public string InsertedDatePersian { get; }

        [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
        public string DoneDatePersian { get; }

    }
}

Program.cs:

using Microsoft.EntityFrameworkCore;

var _context = new  ConsoleApp1.Data.ApplicationDbContext();

var query = _context
    .TicketTasks
    .Where(x => x.isDone == false && (x.ActDepartment._DepartmentStaff.Any(c => c.UserID == 4) || x.ActUserID == 4))
    .GroupBy(x => x.InsertedDatePersian.Substring(0, 10))
    .Select(g => new { Date = g.Key, Count = g.Count() })
    .AsQueryable();

var queryString = query.ToQueryString();

var r = await query.ToListAsync();

Error

Column 'TicketTask.InsertedDatePersian' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Query String

Query created in version 6.0.1:

SELECT SUBSTRING([t].[InsertedDatePersian], 0 + 1, 10) AS [Date], COUNT(*) AS [Count]
FROM [TicketTask] AS [t]
LEFT JOIN [Department] AS [d] ON [t].[ActDepartmentID] = [d].[ID]
WHERE ([t].[isDone] = CAST(0 AS bit)) AND (EXISTS (
    SELECT 1
    FROM [DepartmentStaff] AS [d0]
    WHERE ([d].[ID] IS NOT NULL AND ([d].[ID] = [d0].[DepartmentID])) AND ([d0].[UserID] = 4)) OR ([t].[ActUserID] = 4))
GROUP BY SUBSTRING([t].[InsertedDatePersian], 0 + 1, 10)

Query created in version 6.0.2:

SELECT SUBSTRING([t].[InsertedDatePersian], 0 + 1, 10) AS [Date], (
    SELECT COUNT(*)
    FROM [TicketTask] AS [t0]
    LEFT JOIN [Department] AS [d1] ON [t0].[ActDepartmentID] = [d1].[ID]
    WHERE (([t0].[isDone] = CAST(0 AS bit)) AND (EXISTS (
        SELECT 1
        FROM [DepartmentStaff] AS [d2]
        WHERE (([d1].[ID] IS NOT NULL) AND ([d1].[ID] = [d2].[DepartmentID])) AND ([d2].[UserID] = 4)) OR ([t0].[ActUserID] = 4))) AND ((SUBSTRING([t].[InsertedDatePersian], 0 + 1, 10) = SUBSTRING([t0].[InsertedDatePersian], 0 + 1, 10)) OR (([t].[InsertedDatePersian] IS NULL) AND ([t0].[InsertedDatePersian] IS NULL)))) AS [Count]
FROM [TicketTask] AS [t]
LEFT JOIN [Department] AS [d] ON [t].[ActDepartmentID] = [d].[ID]
WHERE ([t].[isDone] = CAST(0 AS bit)) AND (EXISTS (
    SELECT 1
    FROM [DepartmentStaff] AS [d0]
    WHERE (([d].[ID] IS NOT NULL) AND ([d].[ID] = [d0].[DepartmentID])) AND ([d0].[UserID] = 4)) OR ([t].[ActUserID] = 4))
GROUP BY SUBSTRING([t].[InsertedDatePersian], 0 + 1, 10)

Include provider and version information

EF Core version: Database provider: (e.g. Microsoft.EntityFrameworkCore.SqlServer) Target framework: (e.g. .NET 6.0) Operating system: IDE: (e.g. Visual Studio 2022 17.0.6)

[KAJOOSH]

A sample project was also attached for faster tracking: ConsoleApp_Test.zip

[ajcvickers]

@maumar Can you take a look at this? In particular, whether or not it can be quirked back to the 6.0.1 behavior.

[maumar]

27102 is the culprit and the quirk makes the query work again.

AppContext.SetSwitch("Microsoft.EntityFrameworkCore.Issue27102", true);

@KAJOOSH

[maumar]

problem: in #27102 we started matching group correlation predicate with predicate exactly when trying to lift it. The problem is that here the predicate is Exisits(subquery). They are both essentially identical, but for SelectExpression we (deliberately) perform reference comparison, so the check doesn't pass and we don't go into the lifting logic, even though we could/should.

[KAJOOSH]

@maumar Thanks for the review. Interestingly, I had a similar problem with #27102 and it was fixed with version 6.0.2! These two problems did not exist in .net 5 .. And now I need both and I can not disable #27102

[KAJOOSH]

@maumar I have a suggestion If it can be recognized that the blindness of the "context" is new, maybe this issue will be resolved?

[ajcvickers]

@smitpatel to look at workarounds.

[smitpatel]

                var query = db
    .TicketTasks
    .Where(x => x.isDone == false && (x.ActDepartment._DepartmentStaff.Any(c => c.UserID == 4) || x.ActUserID == 4))
    .Select(x => x.InsertedDatePersian.Substring(0, 10))
    .Skip(0)
    .GroupBy(x => x)
    .Select(g => new { Date = g.Key, Count = g.Count() })
    .AsQueryable();

Select appropriate Grouping Key columns and required columns on which aggregate is being applied in projection, then apply Skip(0) to cause a subquery. This will make sure that complex columns are pushed inside subquery and lifting works to aggregate operator.

[smitpatel]

Same as #27266