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

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; }
}

CodePudding user response：

try it like that:

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

CodePudding user response：

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