当我们在 EF Core 中使用 OData 和 Mapster 和 custom DTO 时.OdataQueryOp

Question

我们要做的是，附加到运营商的租户并非在所有情况下都来。

我的.NET Core项目中有OData和Mapster。我想使用ProjectToType将 OData 查询转换为 dto，我也在 mapster 中使用。但是，ProjectToType将重写 OData 查询生成的 EF Core 查询。它通过一个大的SQL查询进入数据库。它尝试处理内存中的所有事务，但我只想从数据库中获取一个属性。我在下面分享了我的项目细节和我期望的结果。

控制器：

[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类：

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
});
}
}

当我们将其发送为时，我们期望的查询

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')

而是 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')

我正在使用

• .NET Core 3.1
• OData 7.5.7
• 地图管理员 4.1.1

Answer 1

我看到两个问题：

1. opts.ApplyTo(..)reutrnsIQueryable，但您的代码忽略了结果
2. ApplyTo应用于IQueryable<Carrier>但应应用于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);
}