LINQ JOIN in .NET Core 3.1

Question

我已经用LINQ创建了一个join，我想知道是否有更好的方法来实现它

public async Task<IEnumerable<Prices>> GetTicketsPrices(db501Context _context,string Provider)
{
int ProviderKey = 0;
switch (Provider)
{
case "A":
ProviderKey = 1;
break;
case "B":
ProviderKey = 2;
break;
default:
ProviderKey = 0;
break;
}

var voucherPrices = await _context.VoucherPrices.Select(r => r).ToListAsync();
var Providers = await _context.VoucherProviders.Select(p => p).ToListAsync();
var voucherTypes = await _context.VoucherTypes.Select(t => t).ToListAsync();
var passengerType = await _context.VouchersPassengerTypes.Select(pt => pt).ToListAsync();
var newQueryObjectResult = from price in voucherPrices
join provider in Providers on price.Provider equals provider.Id
join type in voucherTypes on price.Type equals type.Id
join passenger in passengerType on price.PassengerType equals passenger.Id
where provider.Id == ProviderKey
select new Prices { Provider = provider.Name, PassengerType = passenger.Description,Type = type.Description,Price = price.Price};
return newQueryObjectResult;
}

目前，我已经从context获取了四次数据。每个模型的列表结果都分配给不同的变量。

这是最好的方法吗？除了使用存储过程之外，我可以只使用上下文一次来检索所有数据吗？

Answer 1

您可以尝试这样的方法：

var prices = await (
from price in _context.VoucherPrices
where provider.Id == ProviderKey
select new Prices
{
Provider = price.Provider.Name,
PassengerType = price.PassengerType.Description,
Type = price.Type.Description,
Price = price.Price
}
).ToListAsync();

这只是使用导航属性，而不是手动联接。您只需要小心是否有与外键绑定的记录(或者进行null检查(，例如：

Provider = price.Provider != default ? price.Provider.Name : default,
PassengerType = price.PassengerType != default ? price.PassengerType.Description : default,
Type = price.Type != default ? price.Type.Description : default