System.InvalidOperationException: 'The LINQ expression 'c' could not be translated

[tomrus88]

After upgrading my project from .NET 9 to .NET 10 I'm getting this exception

System.InvalidOperationException: 'The LINQ expression 'c' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.'
Minimal repro

csproj (broken)

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

  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>net10.0</TargetFramework>
    <ImplicitUsings>enable</ImplicitUsings>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="Microsoft.EntityFrameworkCore" Version="10.0.0" />
    <PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL.NodaTime" Version="10.0.0" />
  </ItemGroup>
</Project>

csproj (working) with .NET 10 RC1 packages code still works fine, just like it does with .NET 9

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

  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>net10.0</TargetFramework>
    <ImplicitUsings>enable</ImplicitUsings>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="Microsoft.EntityFrameworkCore" Version="10.0.0-rc.1.25451.107" />
    <PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL.NodaTime" Version="10.0.0-rc.1" />
  </ItemGroup>
</Project>

Program.cs

using Microsoft.EntityFrameworkCore;
using NodaTime;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace SqlTranslationBug
{
    public class CloseGameRequest
    {
        public int GameId { get; set; }
        public int ServerId { get; set; }
    }

    internal class Program
    {
        static async Task Main(string[] args)
        {
            await using var ctx = new MyDbContext();

            await ctx.Database.EnsureDeletedAsync();
            await ctx.Database.EnsureCreatedAsync();

            Game game = new Game { Name = "test", GameId = 1, ServerId = 2 };

            ctx.Characters.Add(new() { Id = 1, Name = "char1", Game = game, GameEnterTime = SystemClock.Instance.GetCurrentInstant() });
            ctx.Characters.Add(new() { Id = 2, Name = "char2", Game = game, GameEnterTime = SystemClock.Instance.GetCurrentInstant() });
            await ctx.SaveChangesAsync();

            CloseGameRequest request = new CloseGameRequest() { GameId = 1, ServerId = 2 };

            await ctx.Characters
                .Where(o => o.Game != null && o.Game.GameId == request.GameId && o.Game.ServerId == request.ServerId)
                .ExecuteUpdateAsync(p => p
                    .SetProperty(f => f.GameName, (string)null)
                    .SetProperty(f => f.PlayTime, o => o.PlayTime + (SystemClock.Instance.GetCurrentInstant() -
                                       (o.GameEnterTime ?? SystemClock.Instance.GetCurrentInstant())))
                    .SetProperty(f => f.GameEnterTime, (Instant?)null)
                    .SetProperty(f => f.LastSaveTime, SystemClock.Instance.GetCurrentInstant())
                );

            Console.WriteLine("Done");

            Console.ReadLine();
        }
    }

    public class MyDbContext : DbContext
    {
        public DbSet<Character> Characters { get; set; }
        public DbSet<Game> Games { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseNpgsql(@"Host=localhost:5432;Database=testing;Username=postgres;Password=qwerty;", o => o
                .SetPostgresVersion(14, 15)
                .UseNodaTime()
            );

            base.OnConfiguring(optionsBuilder);
        }
    }

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

        [MaxLength(64)]
        [Column(TypeName = "citext")]
        public string Name { get; set; }

        [ForeignKey("Game")]
        [MaxLength(60)]
        public string GameName { get; set; }
        public Duration PlayTime { get; set; } = Duration.Zero;
        public Instant? GameEnterTime { get; set; } = Instant.MinValue;
        public Instant LastSaveTime { get; set; } = Instant.MinValue;
        public virtual Game Game { get; set; }
    }

    public class Game
    {
        [MaxLength(60)]
        [Key]
        [Column(TypeName = "citext")]
        public string Name { get; set; }

        public int GameId { get; set; }
        public int ServerId { get; set; }
    }
}

[roji]

I cannot reproduce this. Your code above is likely incomplete - for example, it references the CloseGameRequest but does not include its definition. I put together a runnable sample based on your code, and when I run it, I can see the following SQL getting sent by EF:

UPDATE "Characters" AS c
SET "GameName" = NULL,
    "PlayTime" = c."PlayTime" + (NOW() - COALESCE(c."GameEnterTime", NOW())),
    "GameEnterTime" = NULL,
    "LastSaveTime" = @p1
FROM "Games" AS g
WHERE c."GameName" = g."Name" AND g."GameId" = @request_GameId AND g."ServerId" = @request_ServerId

However, the query fails with null value in column "GameName" of relation "Characters" violates not-null constraint, which seems like a bug in your code (your ExecuteUpdate sets GameName to null but the column isn't nullable).

Please submit a runnable, minimal console program or tweak the one just below to show the error.

Attempted repro

await using var context = new BlogContext();
await context.Database.EnsureDeletedAsync();
await context.Database.EnsureCreatedAsync();

Game game = new Game { Name = "test", GameId = 1, ServerId = 2 };

context.Characters.Add(new() { Id = 1, Name = "char1", Game = game, GameEnterTime = SystemClock.Instance.GetCurrentInstant() });
context.Characters.Add(new() { Id = 2, Name = "char2", Game = game, GameEnterTime = SystemClock.Instance.GetCurrentInstant() });
await context.SaveChangesAsync();

CloseGameRequest request = new CloseGameRequest() { GameId = 1, ServerId = 2 };

await context.Characters
    .Where(o => o.Game != null && o.Game.GameId == request.GameId && o.Game.ServerId == request.ServerId)
    .ExecuteUpdateAsync(p => p
        .SetProperty(f => f.GameName, (string)null)
        .SetProperty(f => f.PlayTime, o => o.PlayTime + (SystemClock.Instance.GetCurrentInstant() -
                            (o.GameEnterTime ?? SystemClock.Instance.GetCurrentInstant())))
        .SetProperty(f => f.GameEnterTime, (Instant?)null)
        .SetProperty(f => f.LastSaveTime, SystemClock.Instance.GetCurrentInstant()));

public class BlogContext : DbContext
{
    public DbSet<Character> Characters { get; set; }
    public DbSet<Game> Games { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            // .UseSqlServer(Environment.GetEnvironmentVariable("Test__SqlServer__DefaultConnection"))
            .UseNpgsql(Environment.GetEnvironmentVariable("Test__Npgsql__DefaultConnection"), o => o.SetPostgresVersion(14, 15).UseNodaTime())
            .LogTo(Console.WriteLine, LogLevel.Information)
            .EnableSensitiveDataLogging();
}

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

    [MaxLength(64)]
    [Column(TypeName = "citext")]
    public string Name { get; set; }

    [ForeignKey("Game")]
    [MaxLength(60)]
    public string GameName { get; set; }
    public Duration PlayTime { get; set; } = Duration.Zero;
    public Instant? GameEnterTime { get; set; } = Instant.MinValue;
    public Instant LastSaveTime { get; set; } = Instant.MinValue;
    public virtual Game Game { get; set; }
}

public class Game
{
    [MaxLength(60)]
    [Key]
    [Column(TypeName = "citext")]
    public string Name { get; set; }

    public int GameId { get; set; }
    public int ServerId { get; set; }
}

public class CloseGameRequest
{
    public int GameId { get; set; }
    public int ServerId { get; set; }
}

[tomrus88]

I've updated code above. Try again. Not sure how I posted it with missing part.

However, the query fails with null value in column "GameName" of relation "Characters" violates not-null constraint, which seems like a bug in your code (your ExecuteUpdate sets GameName to null but the column isn't nullable).

That only happens if you have
<Nullable>enable</Nullable>
in your project file. Make sure its not present. Or you can make GameName field nullable, it would still fail to translate to SQL.

[roji]

Thanks, I can see it now - will investigate.

Minimal repro

await using var context = new BlogContext();
await context.Database.EnsureDeletedAsync();
await context.Database.EnsureCreatedAsync();

await context.Characters
    .Where(o => o.Game!.ServerId == 2)
    .ExecuteUpdateAsync(p => p
        .SetProperty(f => f.GameEnterTime, (DateTime?)null)
        .SetProperty(f => f.LastSaveTime, DateTime.UtcNow));

public class BlogContext : DbContext
{
    public DbSet<Character> Characters { get; set; }
    public DbSet<Game> Games { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            // .UseSqlServer(Environment.GetEnvironmentVariable("Test__SqlServer__DefaultConnection"))
            .UseNpgsql(Environment.GetEnvironmentVariable("Test__Npgsql__DefaultConnection"))
            .LogTo(Console.WriteLine, LogLevel.Information)
            .EnableSensitiveDataLogging();
}

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

    public DateTime? GameEnterTime { get; set; }
    public DateTime LastSaveTime { get; set; }
    // If made non-nullable, error goes away
    public virtual Game? Game { get; set; }
}

public class Game
{
    public int GameId { get; set; }
    public int ServerId { get; set; }
}

[Zwergenpunk]

I have the same issue.
The error only is produced when you make 2 setProperty calls and have where clause with a navigation property.
if you remove one SetProperty or make the where clause simpler everything works.

(if you already knew that ignore me ;))