Linq或EF-多重联接到具有筛选属性的非匿名对象中

Question

我正在使用EF，不幸的是includefiltered不是选项。所以我必须以某种方式重写我的代码，并从中创建非匿名对象。我决定重写它以加入，但它可以是任何有效的东西。

我有实体，简化版Car.Tires.Manufacturers。汽车可以有零对多的轮胎，轮胎可以有零到多的制造商

我想买一辆有特定id的车，而且只有特定制造商的轮胎。问题是，我的结果汽车的轮胎总是没有制造商。

我当前的代码是：

var car1 = (from c in this.dbContext.Cars
.Include(cr => cr.Tires)
.ThenInclude(crt => crt.Manufacturers)
join t in this.dbContext.Tires
.Include(ct => ct.Manufacturers)
on c.ID equals t.CarID into carTires
from t in carTires.DefaultIfEmpty()
join m in this.dbContext.Manufacturers on t.ManufacturerID equals m.ID into completeSet
from cs in completeSet.DefaultIfEmpty()
where (c.ID == someCarID ) // and later I will add filter for tire's manufacturer

select new Car
{
ID = c.ID,
Tires = c.Tires
}

如果我使用代码

var car2 = this.dbContext.Cars
.Include(c => c.Tires)
.ThenInclude(t => t.Manufacturers)
Where(c => c.ID == someCarID)

在Car2中有一些制造商。

为什么car1轮胎的制造商是无效的，以及如何修复它？

注：这是中等目标。我的最终目标是只为选定的制造商购买带轮胎的汽车。

Answer 1

尝试：

var manufacturerTires = dbContext.Tires.Where(t => t.ManufacturerID == someManufacturerID);
var carTires = dbContext.Cars.
Where(car => car.ID == someCarID)
.Join(manufacturerTires,
car => car.ID,
tire => tire.CarID,
(car, tire) => new { car, tire })
.ToList();

这应该返回一个匿名对象new { Car, Tire }

如果我们需要获得Car和Car.Tires的现有结构，我们可以在上面的查询末尾添加一个GroupBy，比如：

.GroupBy(c => c.car, c => c.tire, (car, tires) => new Car{ ID = car.ID, Tires = tires}); 
//this could be an expensive query as the GroupBy is on all the columns in Car table

Answer 2

试试这个：

var Cars=this.dbContext.Cars.Where(c => c.ID == someCarID).Select(s=> s.Tires).ToList();

现在你有轮胎与那里的制造商