LINQ query join to get entries for left table where right table has no matching records

Question

So the equivalent of this query:

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

is this, in EntityFrameworkCore LINQ , using an IQueryable which has already selected car.Include(x => x.parts):

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

But how can I convert the following SQL to LINQ, so that it includes rows from the left car table that have no respective MemberId entries in the parts table?

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

Models:

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

A filtered Include does exactly what you want:

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

This doesn't generate the shorter join statement with a composite condition, but an outer join to a filtered subquery on Parts, to the same effect.

Answer 2

Try it like that:

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

Answer 3

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