// here it is working, but if I test same code on vs 2022 latest version it doesnt D:

// vs2022 proj code

//<Project Sdk="Microsoft.NET.Sdk">

//

//  <PropertyGroup>

//    <OutputType>Exe</OutputType>

//    <TargetFramework>net6.0</TargetFramework>

//  </PropertyGroup>

//

//  <ItemGroup>

//    <PackageReference Include="Microsoft.EntityFrameworkCore" Version="6.0.0" />

//    <PackageReference Include="Microsoft.EntityFrameworkCore.InMemory" Version="6.0.0" />

//  </ItemGroup>

//

//</Project>

​

​

using System.Collections.Generic;

using System.Linq;

using System;

using Microsoft.EntityFrameworkCore;

using System.ComponentModel.DataAnnotations;

​

namespace EFTest

{

    public class ApplicationDbContext : DbContext

    {

        public ApplicationDbContext(){ }

​

        public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)

            : base(options){ }

        public virtual DbSet<Ticket> Tickets { get; set; }

        public virtual DbSet<Message> Messages { get; set; }

​

        protected override void OnModelCreating(ModelBuilder modelBuilder)

        {

            modelBuilder.Entity<Ticket>(x =>

            {

                x.HasKey(y => y.Id);

                x.HasQueryFilter(y => y.IsDeleted == false);

            });

​

            modelBuilder.Entity<Message>(x =>

            {

                x.HasKey(y => y.Id);

                x.HasQueryFilter(y => y.IsDeleted == false);

            });

        }

​

        public class Ticket

        {

            [Required]

            public Guid Id { get; set; } = Guid.NewGuid();

​

            [Required]

            public bool IsDeleted { get; set; }

            public virtual ICollection<Message> Messages { get; set; } = new HashSet<Message>();

        }

​

        public class Message

        {

            [Required]

            public Guid Id { get; set; } = Guid.NewGuid();

​

            [Required]

            public DateTimeOffset InsertDate { get; set; } = DateTimeOffset.UtcNow;

​

            public Guid? TeamId { get; set; }

​

            [Required]

            public bool IsDeleted { get; set; }

        }

    }

​

    class Program

    {

        static void Main(string[] args)

        {

            Console.WriteLine("======================================================");

            Console.WriteLine("");

            var options = new DbContextOptionsBuilder<ApplicationDbContext>().UseInMemoryDatabase("Teeestasd").Options;

            // Use a separate instance of the context to verify correct data was saved to database

            using (var dbContext = new ApplicationDbContext(options))

            {

                dbContext.Database.EnsureCreated();

                ApplicationDbContext.Ticket ticket = new ApplicationDbContext.Ticket();

                ApplicationDbContext.Message message = new ApplicationDbContext.Message();

                ticket.Messages.Add(message);

                dbContext.Tickets.Add(ticket);

                dbContext.SaveChanges();

​

                var test1 = dbContext.Tickets

                    .Where(x => x.Messages.OrderByDescending(y => y.InsertDate).First().TeamId != Guid.Empty).ToList();

                // ok from vs2022 (testing on server profiler)

                //SELECT [t].[Id], [t].[IsDeleted]

                //FROM [Tickets] AS [t]

                //WHERE ([t].[IsDeleted] = CAST(0 AS bit)) AND (((

                //    SELECT TOP(1) [m].[TeamId]

                //FROM [Messages] AS [m]

                //WHERE ([m].[IsDeleted] = CAST(0 AS bit)) AND ([t].[Id] = [m].[TicketId])

                //ORDER BY [m].[InsertDate] DESC) <> '00000000-0000-0000-0000-000000000000') OR (

                //    SELECT TOP(1) [m].[TeamId]

                //FROM [Messages] AS [m]

                //WHERE ([m].[IsDeleted] = CAST(0 AS bit)) AND ([t].[Id] = [m].[TicketId])

                //ORDER BY [m].[InsertDate] DESC) IS NULL)

​

                var test2 = dbContext.Tickets

                    .Where(x => x.Messages.OrderByDescending(y => y.InsertDate).First().TeamId != null).ToList();

                // ok here but from vs2022 (testing on server profiler) doesnt work, output

                //SELECT [t].[Id], [t].[IsDeleted]

                //FROM [Tickets] AS [t]

                //WHERE ([t].[IsDeleted] = CAST(0 AS bit)) AND EXISTS (

                //    SELECT 1

                //FROM [Messages] AS [m]

                //WHERE ([m].[IsDeleted] = CAST(0 AS bit)) AND ([t].[Id] = [m].[TicketId]))

                Console.WriteLine($"test1: {test1.Count()}");

                Console.WriteLine($"test2: {test2.Count()}");

            }

        }

    }

}