0

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; }
}
Gert Arnold
  • 105,341
  • 31
  • 202
  • 291
jamheadart
  • 5,047
  • 4
  • 32
  • 63
  • Please specify what Linq provider you're executing your IQueryable against, e.g. Entity Framework – Jonas Høgh Jun 23 '22 at 11:20
  • updated q to include EF core – jamheadart Jun 23 '22 at 11:30
  • LINQ Query is built from Model. Show your classes and navigation properties. – Svyatoslav Danyliv Jun 23 '22 at 11:36
  • This is just a basic left outer join. It should be answered in several of the related questions, e.g https://stackoverflow.com/questions/1122942/linq-to-sql-left-outer-join-with-multiple-join-conditions?rq=1 – Jonas Høgh Jun 23 '22 at 11:41
  • Is it a basic left outer join? Because when I run the first query in SQL with `left outer join` I don't get the unmatched entries from the left table, it only brings matched entries. – jamheadart Jun 23 '22 at 11:44
  • Yes, the second query is a left outer join with two conditions in the ON clause. One condition in the ON clause and another in the WHERE clause behaves differently, but that is beside the point. See the linked question for how to handle multiple join conditions in ON clause with Linq – Jonas Høgh Jun 23 '22 at 12:24
  • Thanks. That link shows it's a real fanny on, I'm just breaking it into two separate lines, not only is that GroupJoin syntax OTT it didn't actually give me the default empty as expected. – jamheadart Jun 23 '22 at 13:12

3 Answers3

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.

Gert Arnold
  • 105,341
  • 31
  • 202
  • 291
  • 1
    Yup, this is correct! But I forgot to state in my question that I'm using 2.1 for this project :( – jamheadart Jun 25 '22 at 09:40
  • Ah, too bad, it requires ef-core 5+ (as you'll probably realize). For lower versions there's a work-around: https://stackoverflow.com/a/16801205/861716 – Gert Arnold Jun 25 '22 at 10:18
0

Try it like that:

queryable = queryable.Include(x => x.parts).Where(x => 
  x.parts.Any(y => y.MemberId == 1).ToList();
Sunderam Dubey
  • 1
  • 11
  • 20
  • 40
Lars
  • 97
  • 7
  • Nope, this is giving me less than I want - this is giving me ONLY matching records for MemberId, I want all of those AND records from `Car` where there are no matching entries for `Parts` – jamheadart Jun 23 '22 at 11:32
0
queryable = queryable
    .Where(x => x.parts.Select(y => y.MemberId).Contains(1) || !x.parts.Any());
Firo
  • 30,626
  • 4
  • 55
  • 94