LINQ查询连接获取左表的条目，右表没有匹配的记录

Question

那么等价于这个查询:

select * from car
left join parts ON car.Id = parts.carId
where parts.MemberId = 1

是这样的，在EntityFrameworkCore LINQ中，使用一个已经选择了car.Include(x => x.parts)的IQueryable:

queryable = queryable.Where(x => 
x.parts.Select(y => y.MemberId).Contains(1);

但是我如何将下面的SQL转换为LINQ，以便它包括左car表中没有MemberId条目的parts表中的行?

select * from car
left join parts ON car.Id = parts.CarId and parts.MemberId = 1

模型:

public class Car
{
public int Id { get; set; }
public virtual ICollection<Part> Parts { get; set; }
}
public class Parts
{
public int Id { get; set; }
public int CarId { get; set; }
public virtual Car { get; set; }
public int MemberId { get; set; }
}

Answer 1

经过筛选的Include完全符合您的要求:

var cars = context.Cars
.Include(c => c.Parts.Where(p => p.MemberId == 1));

这不会生成具有复合条件的较短连接语句，而是在Parts上生成与过滤子查询相同效果的外部连接。

Answer 2

queryable = queryable
.Where(x => x.parts.Select(y => y.MemberId).Contains(1) || !x.parts.Any());

Answer 3

试一下:

queryable = queryable.Include(x => x.parts).Where(x => 
x.parts.Any(y => y.MemberId == 1).ToList();