When we use OData and Mapster in EF Core with customDTO. OdataQueryOptions is getting overridden

Question

What we want to do is that the tenant attached to the carrier does not come under all circumstances.

I have OData and Mapster in my .NET Core project. I want to convert the OData query to dto with ProjectToType, which I also use in mapster. However, ProjectToType is overriding the EF Core query generated by the OData query. And it goes to the database with a big SQL query. It tries to handle all transactions in in-memory, but I only want to get one property from the database. I have shared the details of my project and the results I expected below.

Controller:

[Area("odata")]
[Route("[area]/v{v:apiVersion}/[controller]")]
public class CargoCarriersODataController : ODataControllerBase
{
    private readonly IGenericReadRepository<Guid> _genericReadRepository;

    public CargoCarriersODataController(IGenericReadRepository<Guid> genericReadRepository)
    {
        _genericReadRepository = genericReadRepository;
    }

    [HttpGet]
    [EnableQuery]
    [ProducesResponseType((int)HttpStatusCode.OK, Type = typeof(IQueryable<CargoCarriersODataDto>))]
    public async Task<ActionResult<IQueryable<CargoCarriersODataDto>>> Get(ODataQueryOptions<Carrier> opts)
    {
        var carriers = _genericReadRepository.GetAll<Carrier>();
        opts.ApplyTo(carriers);
        return Ok(carriers.ProjectToType<CargoCarriersODataDto>());
    }
}

DTO classes:

public class CargoCarriersODataDto
{
    public int RId { get; set; }
    public Guid TenantId { get; set; }
    public TenantDto Tenant { get; set; }
    public Guid CargoCarrierId { get; set; }
    public string Name { get; set; }
    public string EnabledLogoUrl { get; set; }
    public string DisabledLogoUrl { get; set; }
    public bool IsEnabled { get; set; }
    public bool DoesSupportSameDayShipment { get; set; }
}

public class TenantDto
{
    public int RId { get; set; }
    public string Name { get; set; }
    public string CompanyName { get; set; }
    public string PhoneNumber { get; set; }
    public string CompanyEmail { get; set; }
}

public class CargoCarriersDtoCustomMap : IRegister
{
    public void Register(TypeAdapterConfig config)
    {
        config.ForType<Carrier, CargoCarriersODataDto>()
            .Map(dest => dest.RId, src => src.RId)
            .Map(dest => dest.TenantId, src => src.TenantId)
            .Map(dest => dest.CargoCarrierId, src => src.CargoCarrierId)
            .Map(dest => dest.Name, src => src.Name)
            .Map(dest => dest.EnabledLogoUrl, src => src.EnabledLogoUrl)
            .Map(dest => dest.DisabledLogoUrl, src => src.DisabledLogoUrl)
            .Map(dest => dest.IsEnabled, src => src.IsEnabled)
            .Map(dest => dest.DoesSupportSameDayShipment, src => src.DoesSupportSameDayShipment)
            .Map(dest => dest.Tenant, src => src.Tenant);
    }
}

public class TenantDtoCustomMap : IRegister
{
    public void Register(TypeAdapterConfig config)
    {
        config.ForType<Tenant, TenantDto>()
        .Ignore(dest => dest, src => new TenantDto
        {
            RId = src.RId,
            CompanyEmail = src.CompanyEmail,
            Name = src.Name,
            CompanyName = src.CompanyName,
            PhoneNumber = src.PhoneNumber
        });
    }
}

The query we expect when we send it as

http://localhost:8076/odata/v1/CargoCarriersOData?$select=name

SELECT N'Name' AS [Name], [c].[Name] AS [Value], N'Id' AS [Name], [c].[Id] AS [Value]
FROM [Carriers] AS [c]
WHERE [c].[DeletedAt] IS NULL AND ([c].[TenantId] = 'fa9f75b5-c518-40af-8731-020815551704')

but the query generated by OData

SELECT [c].[RId], [c].[TenantId], CAST(0 AS bit), [t0].[RId], [t0].[Name], [t0].[CompanyName], [t0].[PhoneNumber], [t0].[CompanyEmail], [c].[CargoCarrierId], [c].[Name], [c].[EnabledLogoUrl], [c].[DisabledLogoUrl], [c].[IsEnabled], [c].[DoesSupportSameDayShipment]
FROM [Carriers] AS [c]
INNER JOIN (
SELECT [t].[Id], [t].[CompanyEmail], [t].[CompanyName], [t].[CreatedAt], [t].[CreatedBy], [t].[DeletedAt], [t].[DeletedBy], [t].[Name], [t].[PhoneNumber], [t].[RId], [t].[StorageAccountConnectionString], [t].[UpdatedAt], [t].[UpdatedBy], [t].[Version]
FROM [Tenants] AS [t]
WHERE [t].[DeletedAt] IS NULL
) AS [t0] ON [c].[TenantId] = [t0].[Id]
WHERE [c].[DeletedAt] IS NULL AND ([c].[TenantId] = 'fa9f75b5-c518-40af-8731-020815551704')

I am using

.NET Core 3.1
OData 7.5.7
Mapster 4.1.1

Svyatoslav Danyliv · Answer 1 · 2022-05-26T13:45:56.067

I see two problems:

opts.ApplyTo(..) reutrns IQueryable, but your code ignores result
ApplyTo applied to IQueryable<Carrier> but should be applied to IQueryable<CargoCarriersODataDto>

[HttpGet]
[EnableQuery]
[ProducesResponseType((int)HttpStatusCode.OK, Type = typeof(IQueryable<CargoCarriersODataDto>))]
public async Task<ActionResult<IQueryable<CargoCarriersODataDto>>> Get(ODataQueryOptions<Carrier> opts)
{
    var carriers = _genericReadRepository.GetAll<Carrier>();
    var projected = carriers.ProjectToType<CargoCarriersODataDto>();
    projected = opts.ApplyTo(projected);
    return Ok(projected);
}

Thank you for your answer but the result did not change – Doğancan Koç May 27 '22 at 06:27 — Doğancan Koç, May 27 '22 at 06:27

When we use OData and Mapster in EF Core with customDTO. OdataQueryOptions is getting overridden

1 Answers1