C#:如何从表中获取嵌套数据

Question

我是C#的新手，我面临着下一个棘手的问题：

有三个JSON文件用于为三个数据表设定种子。为了便于理解，我将把我的数据称为带有表Manufacturers、CarType、CarModel的汽车。

因此，让我们假设有3个表：

第一个是主表(CarModel(。有如下列：Id, Name, MaxSpeed, ... id_of_manufacturer, id_of_car_type

Example of data:
['1', 'E34', '250', ... , '1', '1'] //BMW -> Sedan
['2', 'X6', '220', ... , '1', '2']  //BMW -> SUV
['3', 'Q7', '240', ... , '2', '2']  //Audi -> SUV

第二个表CarType，包含大部分常量行和下一列：Id, Type, ...

Example of data
[1, "Sedan", ...]
[2, "SUV", ...]

第三个表制造商，主要为常量行和下一列：Id, Company, ...

Example of data
[1, "BMW", ...]
[2, "AUDI", ...]

因此，表之间的关系如下：有一种CarModel；E34"；，在同一表中reffers到CarType(id_of_car_type("；Sedan"；以及制造商(id_of_Manufacturer("；宝马"；。

我的问题是，如何向数据库发出请求，这样响应将具有CarModel表中的所有数据，其中的数据将是这样的？

1, BMW, ...
1, Sedan, ...
1, E34, 250, ...
2, SUV, ...
2, X6, 220, ...
2, Audi, ...
2, SUV, ...
3, Q7, 240, ...

Answer 1

目前我的解决方案是这样的，但我认为它至少有性能问题。。。

namespace Cars.Data;
public class CarsRepository : ICarsRepository {
private readonly DataContext _context;
private readonly IMapper _mapper;
public CarsRepository(DataContext context, IMapper mapper) {
_mapper = mapper;
_context = context;
}
public async Task<ICollection<CarTypes>> GetCarTypesForManufacturer(int id)
{
var carTypes = await _context.CarModels
.Where(r => r.ManufacturerId == id)
.Select(s => new {s.CarTypeId})
.Distinct()
.Select(s => s.CarTypeId)
.ToListAsync();

return await _context.CarTypes.Where(p => carTypes.Contains(p.Id)).ToListAsync();
}
public async Task<ICollection<Cars>> GetAllCars() {
ICollection<Cars> result = new Collection<Cars>();
var manufacturers = await _context.CarManufacturers.ToListAsync();
foreach (var manufacturer in manufacturers) {
var carTypes = await GetCarTypesForManufacturer(manufacturer.Id);
ICollection<CarTypesWithCarModels> temp = new Collection<CarTypesWithCarModels>();
foreach (var carType in carTypes) {
var carModels = await _context.CarModels
.Where(r => r.ManufacturerId == manufacturer.Id && r.CarTypeId == carType.Id)
.ToListAsync();
temp.Add(new CarTypesWithCarModels {
Id = carType.Id,
Name = carType.Name,
CarModels = carModels
});
}
result.Add(new Cars {
Id = manufacturer.Id,
Name = manufacturer.Name,
CarTypes = temp
});
}
return result;
}
}