efcore: Include( x => x.Child.OrderBy ) does not work in Postgresql

I expect the following query to sort the child document collection by Created descending

.Include(x => x.Documents.OrderByDescending( d => d.Created));

but no order by is added to the generated SQL

SELECT t.start_date, t.end_date, t.content, t.audience, t.status, t.primary_button_name, t.primary_button_action, t.secondary_button_name, t.secondary_button_action, t.created, t.created_by, t.last_updated, t.last_updated_by, t.id, t0."Id", t0."DocumentType", t0."FileName", t0."MediaUrl", t0."FileSize", t.include_in_enterprise, t.header, t.sub_header
FROM (
    SELECT a.start_date, a.end_date, a.content, a.audience, a.status, a.primary_button_name, a.primary_button_action, a.secondary_button_name, a.secondary_button_action, a.created, a.created_by, a.last_updated, a.last_updated_by, a.include_in_enterprise, a.id, a.header, a.sub_header
    FROM master.announcements AS a
    WHERE a.is_active AND (a.id = @__id_0)
    LIMIT 2
) AS t
LEFT JOIN (
    SELECT a0.id AS "Id", a0.document_type AS "DocumentType", a0.file_name AS "FileName", a0.media_url AS "MediaUrl", a0.file_size AS "FileSize", a0.announcement_id
    FROM master.announcement_documents AS a0
    WHERE a0.is_active
) AS t0 ON t.id = t0.announcement_id
ORDER BY t.id

About this issue

Original URL
State: closed
Created 2 years ago
Comments: 17 (8 by maintainers)

Most upvoted comments

OK - I’m indeed no expert on Automapper.

In any case, as there doesn’t seem to be an EF issue here, I’m going to go ahead and close this. If you think something needs are attention, please post back here and we’ll revisit.

roji on Dec 29, 2022

Seems like the issue is resolved but some further discussion may still be of interest to others reading…

I’d also recommend considering whether slimming down projected properties is a good enough reason to involve AM in a project

of course, more complicated mapping/projection scenarios may indeed justify AM

FWIW the opposite is often advised by Jimmy Bogard and co 😀

AutoMapper is at its best when it’s simply automatically mapping properties of the same name (e.g. from an entity to a DTO with a subset of properties). The more manual mapping configuration is required, the more it’s encouraged to avoid AM and write your own projection.

stevendarby on Dec 29, 2022

I found it a little unintuitive at first as well and I totally understand why you are using ProjectTo. AM considers projection and mapping two different things while at the same time using the same system to configure both of them. It’s unfortunate, especially if you want to mix projections and nornal mapping, or if you want to order/filter your child collections (beware, filtering the collection suffers the same caveats and is when you’d likely need a parameterized map/projection)

All in, it completely rewrites all the Select expressions that EF builds up and only cares about the mapping configuration, filters on the root expression and nothing more. I can’t find it now, but at the time I recall reading an AM issue describing the same problem to which the maintainer responded “working as intended”

pinkfloydx33 on Dec 27, 2022

@pinkfloydx33 I can confirm you are correct -

I removed

.Include(x => x.Documents.OrderByDescending( d => d.Created));

From the Spec, and added

    CreateMap<Announcement, AnnouncementDetailDto>()
        .ForMember(
            dest => dest.Documents,
            opt => opt.MapFrom(
                src => src.Documents.OrderByDescending(c => c.Created))
        );

To the mapping… and now they are sorted correctly

jxwaters on Dec 27, 2022

Note that ProjectTo just builds a select expression. You would see the same with Select so it’s not unique to AutoMapper / ProjectTo.

stevendarby on Dec 27, 2022

@jxwaters Make sure the Include is actually being used by the final query; check your logs for warnings.

ajcvickers on Dec 23, 2022