transforms are not applied

[mhamri]

Hi, I'm aware of this package still is in preview and sorry to file an issue. I am not sure you've worked on this or not or you have any plan to support this, if you share your plan it helps a lot for me to plan my next set of actions.

Being said, I have a case that filters and aggregates are not applied.

URL:

http://localhost:5000/odata/graphMyAssignedActivities?$apply=filter(isLate eq true)/groupby((statusInfo/DisplayName,statusInfo/Color),aggregate(id with count distinct as activitiesCount))

My Current Usage that this query works:

[HttpGet]
[EnableQuery(MaxExpansionDepth = 8)]
public async Task<ActionResult<IQueryable<GraphActivityDto>>> Get([FromQuery] GraphFilter filter) {

    var activities = await _activityService.GetQueryableActivitiesByScope();

    var projected = activities.ProjectTo<GraphActivityDto>(_mapper.ConfigurationProvider);

    if (Request.Query.ContainsKey("$apply")) { //it's because of an issue in EFCore 3.0
        projected = projected.ToLinqToDB();
    }

    return Ok(projected);

}

Updated usage:

[HttpGet]
//[EnableQuery(MaxExpansionDepth = 8)]
public async Task<ActionResult<IQueryable<GraphActivityDto>>> Get(ODataQueryOptions<GraphActivityDto> OdataQueryOptions) {
    
    var activities = await _activityService.GetQueryableActivitiesByScope();
    
    var result = await activities.GetQueryAsync(_mapper, OdataQueryOptions, HandleNullPropagationOption.True);
    return Ok(result);

}

and this is generated Expression

EntityQueryable<Activity>.Select(
    dtoActivity => new GraphActivityDto
    {
        ActivityName = dtoActivity.ActivityName,
        ActivityStandard = (dtoActivity.ActivityStandard == null)
            ? null
            : new ActivityStandardBasicDto
            {
                ActivityType = dtoActivity.ActivityStandard.ActivityType,
                Id = dtoActivity.ActivityStandard.Id,
                Name = dtoActivity.ActivityStandard.Name
            },
        ActivityType = dtoActivity.ActivityStandard.ActivityType,
        ActualEndDate = dtoActivity.ActualEndDate,
        ActualStartDate = dtoActivity.ActualStartDate,
        Id = dtoActivity.Id,
        IsLate = (dtoActivity.PlannedStartDate.HasValue && !dtoActivity.ActualStartDate.HasValue)
            ? ((DateTimeOffset?)DateTimeOffset.Now.ToUniversalTime().AddDays(-1d)) > dtoActivity.PlannedStartDate
            : (dtoActivity.PlannedEndDate.HasValue && !dtoActivity.ActualEndDate.HasValue) && (((DateTimeOffset?)DateTimeOffset.Now.ToUniversalTime().AddDays(-1d)) > dtoActivity.PlannedEndDate),
        IsPromoting = dtoActivity.IsPromoting,
        PlannedEndDate = dtoActivity.PlannedEndDate,
        PlannedStartDate = dtoActivity.PlannedStartDate,
        ProjectId = dtoActivity.Project.Id,
        ProjectName = dtoActivity.Project.Name,
        ProjectPhaseId = dtoActivity.ProjectPhaseId,
        Roles = dtoActivity.Roles.Select(
            dtoActivityRoleAssignment => new ProjectRoleGraphDto
            {
                Id = dtoActivityRoleAssignment.Role.Id,
                Name = dtoActivityRoleAssignment.Role.UserGroup.Name,
                Users = dtoActivityRoleAssignment.Role.ProjectUsersRolesAssignments.Select(t => t.ProjectUser).Select(
                    dtoProjectUser => new ProjectUserGraphDto
                    {
                        Email = dtoProjectUser.User.Email,
                        Id = dtoProjectUser.User.Id,
                        Image = FileResolverExtension.Resolve(dtoProjectUser.User.Picture),
                        Name = dtoProjectUser.User.FullName
                    })
            }),
        StatusInfo = (dtoActivity.Status == null)
            ? null
            : new ActivityStatusDto
            {
                Abbreviation = dtoActivity.Status.Abbreviation,
                Color = dtoActivity.Status.Color,
                DisplayName = dtoActivity.Status.DisplayName,
                Editable = dtoActivity.Status.Editable,
                Id = dtoActivity.Status.Id,
                Name = dtoActivity.Status.Name,
                Order = dtoActivity.Status.Order
            },
        StatusSteps = dtoActivity.StatusSteps.Select(
            dtoActivityStatusStep => new ActivityApprovalGraphDto
            {
                ActivityId = dtoActivityStatusStep.ActivityId,
                ActivityStatusId = dtoActivityStatusStep.ActivityStatusId,
                ApprovalStep = (dtoActivityStatusStep.ApprovalStep == null)
                    ? null
                    : new ApprovalStepDto
                    {
                        Color = dtoActivityStatusStep.ApprovalStep.Color,
                        Done = dtoActivityStatusStep.ApprovalStep.Done,
                        Id = dtoActivityStatusStep.ApprovalStep.Id,
                        Name = dtoActivityStatusStep.ApprovalStep.Name,
                        Order = dtoActivityStatusStep.ApprovalStep.Order,
                        Role = dtoActivityStatusStep.ApprovalStep.Role,
                        Todo = dtoActivityStatusStep.ApprovalStep.Todo
                    },
                ApprovalStepId = dtoActivityStatusStep.ApprovalStepId,
                Id = dtoActivityStatusStep.Id,
                IsCurrentStatus = ((!dtoActivityStatusStep.ProjectUsersAssignments.Any() && !dtoActivityStatusStep.RoleAssignments.Any()) || dtoActivityStatusStep.RoleAssignments.Any(
                    ro => !ro.ProjectRole.ProjectUsersRolesAssignments.Any(
                        usr => dtoActivityStatusStep.Votes.Any(v => (v.ProjectUserId == usr.ProjectUserId) && (((int)v.Status) == 2))))) || (dtoActivityStatusStep.ProjectUsersAssignments.Any(
                    usr => !dtoActivityStatusStep.Votes.Any(v => (v.ProjectUserId == usr.ProjectUserId) && (((int)v.Status) == 2))) && (dtoActivityStatusStep.Activity.ActivityStatusId == dtoActivityStatusStep.ActivityStatusId)),
                RequestedAt = dtoActivityStatusStep.ProjectUsersAssignments.Any()
                    ? dtoActivityStatusStep.ProjectUsersAssignments.First().RequestedAt
                    : dtoActivityStatusStep.RoleAssignments.Any()
                        ? dtoActivityStatusStep.RoleAssignments.First().RequestedAt
                        : null,
                Roles = dtoActivityStatusStep.RoleAssignments.Select(
                    dtoActivityStatusStepRoleAssignment => new ProjectRoleGraphDto
                    {
                        Id = dtoActivityStatusStepRoleAssignment.ProjectRole.Id,
                        Name = dtoActivityStatusStepRoleAssignment.ProjectRole.UserGroup.Name,
                        Users = dtoActivityStatusStepRoleAssignment.ProjectRole.ProjectUsersRolesAssignments.Select(t => t.ProjectUser).Select(
                            dtoProjectUser => new ProjectUserGraphDto
                            {
                                Email = dtoProjectUser.User.Email,
                                Id = dtoProjectUser.User.Id,
                                Image = FileResolverExtension.Resolve(dtoProjectUser.User.Picture),
                                Name = dtoProjectUser.User.FullName
                            })
                    }),
                Users = dtoActivityStatusStep.ProjectUsersAssignments.Select(t => t.ProjectUser).Select(
                    dtoProjectUser => new ProjectUserGraphDto
                    {
                        Email = dtoProjectUser.User.Email,
                        Id = dtoProjectUser.User.Id,
                        Image = FileResolverExtension.Resolve(dtoProjectUser.User.Picture),
                        Name = dtoProjectUser.User.FullName
                    }),
                Votes = dtoActivityStatusStep.Votes.Select(
                    dtoActivityStatusStepVote => new ActivityStatusStepVoteGraphDto
                    {
                        ActivityStatusStepId = dtoActivityStatusStepVote.ActivityStatusStepId,
                        Justification = dtoActivityStatusStepVote.Justification,
                        ProjectUserId = dtoActivityStatusStepVote.ProjectUserId,
                        RespondedAt = dtoActivityStatusStepVote.RespondedAt,
                        Status = dtoActivityStatusStepVote.Status,
                        User = (dtoActivityStatusStepVote.ProjectUser == null)
                            ? null
                            : new ProjectUserGraphDto
                            {
                                Email = dtoActivityStatusStepVote.ProjectUser.User.Email,
                                Id = dtoActivityStatusStepVote.ProjectUser.User.Id,
                                Image = FileResolverExtension.Resolve(dtoActivityStatusStepVote.ProjectUser.User.Picture),
                                Name = dtoActivityStatusStepVote.ProjectUser.User.FullName
                            }
                    })
            }),
        Users = dtoActivity.Users.Select(u => u.ProjectUser).Select(
            dtoProjectUser => new ProjectUserGraphDto
            {
                Email = dtoProjectUser.User.Email,
                Id = dtoProjectUser.User.Id,
                Image = FileResolverExtension.Resolve(dtoProjectUser.User.Picture),
                Name = dtoProjectUser.User.FullName
            })
    })

as you can see, no group by or count distinct is added

[BlaiseD]

ODataQueryOptions does not support GroupBy and aggregates as far as I know - I don't think that's an option.

The tests have examples using $filter - (different syntax from your example).

$count=true is also supported without grouping though.

[mhamri]

@BlaiseD not quite correct,

this is what will be executed on sql server if I use [EnableQuery]

exec sp_executesql N'SELECT
    N''statusInfo'',
    N''color'',
    [selectParam].[Key_1],
    N''displayName'',
    [selectParam].[Key_2],
    (
        SELECT
            Count(*)
        FROM
            (
                SELECT DISTINCT
                    [$it].[Id]
                FROM
                    (
                        SELECT
                            IIF([it_1].[PlannedStartDate] IS NOT NULL AND [it_1].[ActualStartDate] IS NULL, IIF(DateAdd(day, -1, GetUtcDate()) > [it_1].[PlannedStartDate], 1, 0), IIF([it_1].[PlannedEndDate] IS NOT NULL AND [it_1].[ActualEndDate] IS NULL AND DateAdd(day, -1, GetUtcDate()) > [it_1].[PlannedEndDate], 1, 0)) as [IsLate],
                            [it].[Color],
                            [it].[DisplayName],
                            [it_1].[Id]
                        FROM
                            [ActivityModule_Activity] [it_1]
                                LEFT JOIN [ActivityStandardModule_ActivityStatus] [it] ON [it].[DeletedAt] IS NULL AND [it_1].[ActivityStatusId] = [it].[Id]
                        WHERE
                            [it_1].[DeletedAt] IS NULL
                    ) [$it]
                WHERE
                    [$it].[IsLate] = @TypedProperty AND (([selectParam].[Key_1] = [$it].[Color] OR [selectParam].[Key_1] IS NULL AND [$it].[Color] IS NULL) AND ([selectParam].[Key_2] = [$it].[DisplayName] OR [selectParam].[Key_2] IS NULL AND [$it].[DisplayName] IS NULL))
            ) [t1]
    )
FROM
    (
        SELECT
            IIF([it_3].[PlannedStartDate] IS NOT NULL AND [it_3].[ActualStartDate] IS NULL, IIF(DateAdd(day, -1, GetUtcDate()) > [it_3].[PlannedStartDate], 1, 0), IIF([it_3].[PlannedEndDate] IS NOT NULL AND [it_3].[ActualEndDate] IS NULL AND DateAdd(day, -1, GetUtcDate()) > [it_3].[PlannedEndDate], 1, 0)) as [IsLate],
            [it_2].[Color] as [Key_1],
            [it_2].[DisplayName] as [Key_2]
        FROM
            [ActivityModule_Activity] [it_3]
                LEFT JOIN [ActivityStandardModule_ActivityStatus] [it_2] ON [it_2].[DeletedAt] IS NULL AND [it_3].[ActivityStatusId] = [it_2].[Id]
        WHERE
            [it_3].[DeletedAt] IS NULL
    ) [selectParam]
WHERE
    [selectParam].[IsLate] = @TypedProperty
GROUP BY
    [selectParam].[Key_1],
    [selectParam].[Key_2]
',N'@TypedProperty bit',@TypedProperty=0

and if you check the OData Specification sub-section 3.10, groupby it's already supported,

ODataQueryOption collects the query arguments, but if you look at the code, you can see that the ApplyQuery is the one apply the aggregate and group-by.

so I'm pretty sure it's supported by Odata library, but it's handled in [EnableQuery]

appreciate it if you re-open the issue since it's not a wrong usage and it's a quite valid query that is already working in production.

[mhamri]

and one more thing, the filter out of the $apply is different from the normal $filter. one happens after the group by and one generally on the result

[BlaiseD]

That's a good point. The ODataQueryOptions page does not list it either. PRs are welcome to implement the Apply clause.

[ViliamVadocz]

I am trying to implement the Apply clause, but I have some difficulty understanding the code. Where would be the prefered place to ask questions regarding this project? Do you have a zulip, discord, slack, or other some platform?

[BlaiseD]

This is it. The library gets its data from OdataQueryOptions and builds expressions from that data. Take OrderBy as an example.

ok to ask questions here.

[BlaiseD]

Also added discussions tab.

[nathanvj]

Any update on this?

[BlaiseD]

PRs are welcome.

[leoerlandsson]

@ViliamVadocz Any progress here? Want to collaborate on this one?

[ViliamVadocz]

@ViliamVadocz Any progress here? Want to collaborate on this one?

No progress. I have not looked at this for more than a year. I am also not particularly interested in this project anymore.