使用Mongo c#驱动程序在单个结构良好的查询中查询具有一对多关系的文档

Question

我会尽量简单地解释。

我们有5个非常简单的类。任何不以DTO后缀结尾的类都表示存在于mongo集合中的真正文档。

public class TruckSingleDriver
{
public string Id { get; set; }
public string DriverId { get; set; }
}
public class TruckSingleDriverDTO
{
public string Id { get; set; }
public Driver Driver { get; set; }
}

public class TruckManyDrivers
{
public string Id { get; set; }
public IEnumerable<string> DriversIds { get; set; }
}
public class TruckManyDriversDTO
{
public string Id { get; set; }
public IEnumerable<Driver> Drivers { get; set; }
}
public class Driver
{
public string Id { get; set; }
}

最简单的方法获得TruckSingleDriverDTO与一个查询将如下所示:

public TruckSingleDriverDTO GetTruckSingleDriverDTO(string truckId)
{
var truckCollection = mongo.GetDatabase("mydb").GetCollection<TruckSingleDriver>("Trucks");
var driverCollection = mongo.GetDatabase("mydb").GetCollection<Driver>("Drivers");
TruckSingleDriverDTO truckDTO = truckCollection.AsQueryable()
.Where(truck => truck.Id == truckId)
.Join(driverCollection, truck => truck.DriverId, driver => driver.Id,
(truck, driver) => new { Id = truck.Id, Driver = driver })
.ToEnumerable() //needed although it seems not
.Select(res => new TruckSingleDriverDTO() { Id = res.Id, Driver = res.Driver })
.Single();
return truckDTO;
}

我想要实现的是在单个中获得TruckManyDriversDTO提问，有办法做到吗?

public TruckManyDriversDTO GetTruckManyDriversDTO(string truckId)
{
var trucks = mongo.GetDatabase("mydb").GetCollection<TruckManyDrivers>("Trucks");
var drivers = mongo.GetDatabase("mydb").GetCollection<Driver>("Drivers");
/*
* here i need your help 
* keep in mind that i want it in a single query 
* below this, ill show the simple way to achieve it with 2 queries
*/
TruckManyDrivers truck = trucks.Find(t => t.Id == truckId).Single();
IEnumerable<Driver> driverList = drivers.Find(d => truck.DriversIds.Contains(d.Id)).ToEnumerable();
return new TruckManyDriversDTO() { Id = truck.Id, Drivers = driverList };
}

Answer 1

我从这个网站得到了帮助:https://www.csharpschool.com/blog/linq-join

我能想到的最好的解决方案:

public TruckManyDriversDTO GetTruckManyDriversDTO(string truckId)
{
var Trucks = mongo.GetDatabase("mydb").GetCollection<TruckManyDrivers>("Trucks").AsQueryable();
var Drivers = mongo.GetDatabase("mydb").GetCollection<Driver>("Drivers").AsQueryable();
var query = from truck in Trucks where truck.Id == truckId
let truckDrivers = from driver in Drivers
where truck.DriversIds.Contains(driver.Id) select driver
select new { Truck = truck, Drivers = truckDrivers };
TruckManyDriversDTO dto = query.Select(a => new TruckManyDriversDTO() { Id = a.Truck.Id, Drivers = a.Drivers } ).Single();
return dto;
}