Linq selects all columns in SQL query when using projection-CodePudding

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"

CodePudding user response：

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.

CodePudding user response：

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();

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();