EF Core select all columns in SQL when a DTO mapper is called on Select()

[MrDave1999]

The following LINQ:

back-end/src/Features/Users/UserRepository.cs

Lines 28 to 38 in 9a93908

	=> await Context.Set<User>()
	.Include(user => user.Person)
	.Where(user => user.UserName == username)
	.Select(user => new UserResetPasswordDto()
	{
	UserId = user.Id,
	UserName = user.UserName,
	Name = user.Person.Names,
	Password = user.Password
	})
	.FirstOrDefaultAsync();

EF Core translates it to:

SELECT `u`.`id` AS `UserId`, `u`.`username` AS `UserName`, `p`.`names` AS `Name`, `u`.`password` AS `Password`
FROM `users` AS `u`
INNER JOIN `persons` AS `p` ON `u`.`person_id` = `p`.`id`
WHERE `u`.`username` = @__username_0
LIMIT 1

In this case EF Core selects the columns associated to UserResetPasswordDto.

But when the mapper is called in the Select method:

.Select(user => user.MapToUserResetPasswordDto()) 

EF Core translates it to:

SELECT `u`.`id`, `u`.`created_at`, `u`.`password`, `u`.`person_id`, `u`.`refresh_token`, `u`.`refresh_token_expiry`, `u`.`updated_at`, `u`.`username`, `p`.`id`, `p`.`cell_phone`, `p`.`created_at`, `p`.`date_birth`, `p`.`document`, `p`.`email`, `p`.`gender_id`, `p`.`last_names`, `p`.`names`, `p`.`updated_at`
FROM `users` AS `u`
INNER JOIN `persons` AS `p` ON `u`.`person_id` = `p`.`id`
WHERE `u`.`username` = @__username_0
LIMIT 1

EF Core selects ALL columns. The mapper code is:

back-end/src/Features/Users/UserMapper.cs

Lines 64 to 71 in 9a93908

	public static UserResetPasswordDto MapToUserResetPasswordDto(this User user)
	=> new()
	{
	UserId = user.Id,
	UserName = user.UserName,
	Name = user.Person.Names,
	Password = user.Password
	};

There is a possible solution in this StackOverflow answer: https://stackoverflow.com/a/62138200 (must be tested)

This problem is also mentioned here: dotnet/efcore#24509

[MrDave1999]

I have published a NuGet package with the solution proposed by Ivan Stoev (see https://stackoverflow.com/a/62138200).

[MrDave1999]

In EF Core 7.0 added Interception to modify the LINQ expression tree, so the custom code should be simpler:

using Microsoft.EntityFrameworkCore.Query;
using Microsoft.EntityFrameworkCore.Diagnostics;

namespace Microsoft.EntityFrameworkCore
{
    public static class DelegateDecompilerDbContextOptionsBuilderExtensions
    {
        public static DbContextOptionsBuilder AddDelegateDecompiler(this DbContextOptionsBuilder optionsBuilder)
            => optionsBuilder.AddInterceptors(new DelegateDecompilerQueryPreprocessor());
    }
}

namespace Microsoft.EntityFrameworkCore.Query
{
    using System.Linq.Expressions;
    using DelegateDecompiler;
    public class DelegateDecompilerQueryPreprocessor : IQueryExpressionInterceptor
    {
        Expression IQueryExpressionInterceptor.QueryCompilationStarting(Expression queryExpression, QueryExpressionEventData eventData)
            => DecompileExpressionVisitor.Decompile(queryExpression);
    }
}

Reference: https://stackoverflow.com/a/62138200.