How to preserve SQL performance that I get with IQueryable with a IQueryable when using oData?

Question

I am using EF Core and oData. I've noticed that oData "magically" updates my query when my controller returns an IQueryable that returns an EF Core entity, but as soon as I try to return a DTO of that Entity, all data is requested in the SQL query.

Examples:

This API Action:

    [EnableQuery]
    public ActionResult<IEnumerable<ProjectEntity>> Get()
    {        
        return Ok(_db.Projects);
    }

Generates this query:

SELECT [s].[Id], [s].[EndDate], [s].[StartDate], [p].[CustomerId], [p].[Name]
FROM [ScheduleEvents] AS [s] INNER JOIN [Projects] AS [p] ON [s].[Id] = [p].[Id]

but if I return DTO's instead with this Action:

    [EnableQuery]
    public ActionResult<IEnumerable<ProjectDTO>> Get()
    {
        return Ok(_autoMapper.ProjectTo<ProjectDTO>(_db.Projects));
    }

Then the SQL is this:

SELECT CAST(0 AS bit), [c].[Id], [c].[Name], [p].[CustomerId], [s].[EndDate],
[s].[Id],[p].[Name], [s].[StartDate] FROM [ScheduleEvents] AS [s]
INNER JOIN [Projects] AS [p] ON [s].[Id] = [p].[Id] INNER JOIN [Customers] AS [c]
ON [p].[CustomerId] = [c].[Id]

OData magically knows how to add the join only when I use the $Expand syntax when using an Entity as the return type, but with a DTO the joins are made whether or not I requested them.

Is there something I can do in Automapper or oData to get the same SQL behavior with oData for both Entities and DTOs?

“Magic” generated by AM. It just load all navigation properties that you have mapped. — Svyatoslav Danyliv, Dec 21 '21 at 19:48
https://docs.automapper.org/en/latest/Queryable-Extensions.html#explicit-expansion — Lucian Bargaoanu, Dec 21 '21 at 19:55
understood, how can extend that mapping to my DTO optionally the way the EF core does with entities when working with oData. — Tom Crosman, Dec 21 '21 at 20:18

score 0 · Answer 1 · answered Feb 11 '22 at 13:55

Instead of .ProjectTo we need to use an additional package: AutoMapper.Extensions.ExpressionMapping that will give us access to the UseAsDataSource extension method.

UseAsDataSource
UseAsDataSource().For<DTO>() makes this translation clean by not having to explicitly map expressions. It also calls ProjectTo() for you as well, where applicable.

This changes your implementation to the following:

[EnableQuery]
public ActionResult<IQueryable<ProjectDTO>> Get()
{
    return Ok(_db.Projects.UseAsDataSource().For<ProjectDTO>());
}

Though the question title is not related, the performance issue and reasoning is discussed in these two similar posts:

How to preserve SQL performance that I get with IQueryable with a IQueryable when using oData?

1 Answers1