CODEWITHSUNDEEP
c#linqasp.net-coreodataasp.net-apicontroller
Tom Crosman
Tom Crosman

Reputation: 1285

How to preserve SQL performance that I get with IQueryable<Entity> with a IQueryable<DTO> when using oData?

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?

Upvotes: 1

Views: 525

Answers (1)

Chris Schaller
Chris Schaller

Reputation: 16554

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:

Upvotes: 1

Related Questions