Repro for https://github.com/dotnet/efcore/issues/26593

EfCoreBug26593.cs

using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace EfCoreBug
{
    class Program
    {
        public static readonly ILoggerFactory MyLoggerFactory
            = LoggerFactory.Create(builder =>
            {
                builder.AddConsole();
            });

        static async Task Main(string[] args)
        {
            using (var context = new ClientContext())
            {
                context.Database.EnsureDeleted();
                context.Database.EnsureCreated();

                var currentUserId = 1;

                var currentUserGroupIds = context.Memberships
                    .Where(m => m.UserId == currentUserId)
                    .Select(m => m.GroupId);

                var hasMembership = context.Memberships
                    // note: commenting out this line makes it work in SQL Provider
                    .Where(m => currentUserGroupIds.Contains(m.GroupId))
                    .Select(m => m.User);

                var users = await context.Users
                    .Select(u => new
                    {
                        // throws exception in SQL Provider
                        HasAccess = hasMembership.Contains(u)
                        // workaround for SQL Provider
                        // HasAccess = hasMembership.Any(m => m == u)
                    })
                    .ToListAsync();
            }
        }

        public class User
        {
            public int Id { get; set; }

            public ICollection<Membership> Memberships { get; set; }
        }

        public class Group
        {
            public int Id { get; set; }

            public ICollection<Membership> Memberships { get; set; }
        }

        public class Membership
        {
            public int Id { get; set; }
            public User User { get; set; }
            public int UserId { get; set; }
            public Group Group { get; set; }
            public int GroupId { get; set; }
        }

        class ClientContext : DbContext
        {
            protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
            {
                // TO USE SQL
                optionsBuilder
                    .UseLoggerFactory(MyLoggerFactory)
                    .UseSqlServer("Server=(localdb)\\mssqllocaldb;Database=ContainsInProjection;Trusted_Connection=True;MultipleActiveResultSets=true;Connect Timeout=30")
                    .EnableSensitiveDataLogging(false);
                // TO USE INMEMORY
                //optionsBuilder
                //   .UseLoggerFactory(MyLoggerFactory)
                //   .UseInMemoryDatabase(Guid.NewGuid().ToString());
            }

            protected override void OnModelCreating(ModelBuilder builder)
            {
                base.OnModelCreating(builder);

                builder.Entity<User>().HasData(new User()
                {
                    Id = 1,
                });
                builder.Entity<Group>().HasData(new Group()
                {
                    Id = 1,
                });
                builder.Entity<Membership>().HasData(new Membership()
                {
                    Id = 1,
                    UserId = 1,
                    GroupId = 1,
                });
            }

            public DbSet<User> Users { get; set; }
            public DbSet<Group> Groups { get; set; }
            public DbSet<Membership> Memberships { get; set; }
        }
    }
}

Project.csproj

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

  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>net6.0</TargetFramework>
    <LangVersion>latest</LangVersion>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="Microsoft.EntityFrameworkCore" Version="6.0.0" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.InMemory" Version="6.0.0" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="6.0.0" />
    <PackageReference Include="Microsoft.Extensions.Logging.Console" Version="6.0.0" />
  </ItemGroup>

</Project>