Linq selects all columns in SQL query when using projection

Question

Not sure if I put the proper title for my problem.

In simple words, I'm trying to extract a projection from the query into a static class, but when I do that, EF queries for all columns.

await dbQuery
          .Select(pl => new PackingListDto
                            {
                                Id = pl.Id,
                                Name = pl.Name
                            })
          .AsNoTracking()
          .ToListAsync();

await dbQuery
          .Select(pl => pl.AsDto())
          .AsNoTracking()
          .ToListAsync();

In the 2nd query, the mapping is extracted into this static class:

internal static class Extensions
{
    public static PackingListDto AsDto(this PackingListReadModel readModel)
        => new()
        {
            Id = readModel.Id,
            Name = readModel.Name
        };
}

Which for me should generate exactly the same SQL query. However the queries generated are:

SELECT p."Id", p."Name"
FROM packing."PackingLists" AS p

and

SELECT p."Id", p."Localization", p."Name", p."Version", p0."Id", p0."IsPacked", p0."Name", p0."PackingListId", p0."Quantity"
FROM packing."PackingLists" AS p
LEFT JOIN packing."PackingItems" AS p0 ON p."Id" = p0."PackingListId"
ORDER BY p."Id"

The whole code for the project can be found here, just simplified the DTO.

https://github.com/devmentors/PackIT

Any ideas?

Thanks

PS: I'm not sure if that changes anything, but using postgresql as the database.

UPDATE Tried to use LinqKit as suggested in comment.

            await dbQuery
          .Select(pl => LinqKitExample.AsDto(pl))
          .AsExpandable()
          .ToListAsync();

And the class:

    internal static class LinqKitExample
{
    [Expandable(nameof(AsDtoImpl))]
    public static ExampleDto AsDto(PackingListReadModel model)
    {
        _asDtoImpl ??= AsDtoImpl.Compile();
        return _asDtoImpl(model);
    }

    private static Func<PackingListReadModel, ExampleDto> _asDtoImpl;

    private static Expression<Func<PackingListReadModel, ExampleDto>> AsDtoImpl =>
        model => new ExampleDto
        {
            Id = model.Id,
            Name = model.Name,
        };
}

However that still selected all fields from the database. The log from console

info: Microsoft.EntityFrameworkCore.Infrastructure[10403] Entity Framework Core 6.0.7 initialized 'ReadDbContext' using provider 'Npgsql.EntityFrameworkCore.PostgreSQL:6.0.6+6fa8f3c27a7c241a66e72a6c09e0b252509215d0' with options: LINQKitExpandable info: Microsoft.EntityFrameworkCore.Database.Command[20101] Executed DbCommand (4ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] SELECT p."Id", p."Localization", p."Name", p."Version", p0."Id", p0."IsPacked", p0."Name", p0."PackingListId", p0."Quantity" FROM packing."PackingLists" AS p LEFT JOIN packing."PackingItems" AS p0 ON p."Id" = p0."PackingListId" ORDER BY p."Id"

Answer 1

Your pl => pl.AsDto() statement is delegate. EF Core provider can not translate it to SQL.

Method must return Expression.

internal static class Extensions
{
    public static Expression<Func<PackingList, PackingListDto>> ToDto()
        => packingList => new PackingListDto
        {
            Id = packingList.Id,
            Name = packingList.Name
        };
}

Use it like this:

await dbQuery
    .Select(Extensions.ToDto())
    .AsNoTracking()
    .ToListAsync();

You must use the method exactly like this: Select(Extensions.ToDto()).
You must not use it like this: Select(x => Extensions.ToDto()).

The method Extensions.ToDto() returns Expression that can be translated by EF into SQL.
The lambda x => Extensions.ToDto() converts it into a delegate that EF doesn't know what to do with.

---

Other approach. Method returns IQueryable.

internal static class Extensions
{
    public static IQueryable<PackingListDto> ToDto(this IQueryable<PackingList> packingLists)
        => packingLists.Select(packingList => new PackingListDto
        {
            Id = packingList.Id,
            Name = packingList.Name
        });
}

Use it like this:

await dbQuery
    .ToDto()
    .AsNoTracking()
    .ToListAsync();

Answer 2

First - you don't map your query result to a static class, you map it to a (just) class using a static method extension.

Second - Postgres (or any DB engine for this case) knows nothing about method AsDto and how to translate it to SQL. Thus it gives up and fetches everything to be processed on the client within your method's body later.