This project reproduces a potential bug in EF Core 10.0.1 when using SqlQueryRaw followed by GroupBy and LINQ projection.

• .NET 10
• PostgreSQL server running on localhost:5432
• Database user: postgres/admin

• Microsoft.EntityFrameworkCore 10.0.1
• Npgsql.EntityFrameworkCore.PostgreSQL 10.0.0

The application uses the following connection string:

Server=localhost; Port=5432; Database=efcorerepro; Userid=postgres; Password=admin;

Note: The database is automatically created and seeded when the application runs.

The bug occurs when:

1. Using Database.SqlQueryRaw<T>() with a CTE query
2. The CTE includes a dynamically injected operator using string.Format (e.g., WHERE space_type {0} @space)
3. Applying GroupBy on the query result
4. Applying a Select projection with OrderBy().First() inside

• User: Basic user entity with Id, Name, TenantId
• UserRole: User roles with RoleType, SpaceType, and IsInherited flag

1. SqlQueryRaw: Executes a CTE that joins Users and UserRoles, filtering by SpaceType
2. GroupBy: Groups results by User properties (Id, Name, TenantId)
3. Projection: Selects the first RoleType (ordered) for each user

• User.cs / UserRole.cs: Database entities
• UserRoleInheritance.cs: DTO for raw SQL query results
• UserData.cs: DTO for projected query results
• AppDbContext.cs: EF Core DbContext with Npgsql
• UserService.cs: Contains the problematic query methods
• Program.cs: Application entry point with seed data and test scenarios

cd EFCoreBugRepro
dotnet run

The application will:

1. Drop and recreate the database
2. Seed 4 users with 8 user roles
3. Execute 3 test scenarios with different parameters
4. Display the generated SQL and results/errors

Filters roles where space_type = 2

Filters roles where space_type >= 2

Filters roles where space_type = 1

Expected: The query should execute successfully and return aggregated user data with their primary role.

Actual: [To be documented after running - may throw an exception or produce incorrect SQL]

The application creates:

• 4 users (Alice, Bob, Charlie, Diana)
• 8 user roles with different SpaceType values:
  • SpaceType 1 = Global
  • SpaceType 2 = Tenant
  • SpaceType 3 = Client

Each user has multiple roles at different space levels to trigger the GroupBy aggregation.

This is a simplified reproduction of a real-world scenario where:

• A recursive CTE calculates role inheritance hierarchy
• The query filters by permission space type using dynamic operators
• Results are grouped by user and projected to show the primary role
• The issue appears when EF Core tries to translate the LINQ operations after the raw SQL query