Filter RHS items in LINQ Group Join

Question

In LINQ, I need to left join (group join) 2 lists of objects using some criteria that are able to be expressed as equijoins (so I can use the On clause), and other criteria that are relative.

In SQL, the equivalent is something like this (using a ludicrous example, where we want to get a list for each person of the pets that they own that were born on or after the owner's wedding date; also note that my data is in objects, not in SQL):

select
  pers.Name, pers.Address, pers.Gender, pet.Name, pet.Species
from person as pers
left join pets as pet on pers.Name=pet.OwnerName and pet.Birthdate >= pers.WeddingDate

Here's what I've got so far - this obviously doesn't include the restriction that pets in the list should have been born on or after the owner's wedding date.

Dim res = (
    From p in Person
    Group Join pt in Pets
    On p.Name Equals pt.OwnerName
    Into PetList = Group
    Select p, PetList)

First I thought I could use the Where clause, but individual pets in the list aren't in scope at that point.

Then I thought perhaps the answer is a lambda function in the Pets list, something like

Group Join pt in Pets.Where(Function(pt) pt.Birthdate >= pers.WeddingDate)

But I don't know (i) how to get Person into the Lambda function scope, or (ii) if I do, will this work anyway.

So, my question really is, how can I filter the right-hand-side results of a LINQ Group Join based on non-equijoin criteria?

I know I can do some of the above in a Join (e.g. not a Group Join) context, but I need to (a) return persons without pets that match the criteria, and (b) return a single object per person, with the pets as a list.

I have a feeling the answer is something like this, but I don't know enough about LINQ to generalise from it.

score 0 · Accepted Answer · answered Jun 14 '15 at 10:41

The approach I'm taking in this case is to filter the collection within the Select clause, using a function I've had to add to the Pet model specifically to support this join filtering. Which of course undermines all that's great about LINQ (i.e. the ability to perform arbitrary queries on objects):

Dim res = (
    From p in Person
    Group Join pt In Pets
    On p.Name Equals pt.OwnerName
    Into PetList = Group
    Select p,
            thisPetList = PetList.Where(Function(x) x.MyCustomFunction(p.WeddingDate))
    )

The function I've added to the Pet model accepts a date, compares it to the Pet's birthdate, and returns True if the input date is earlier than Pet.Birthdate.

Urgh.

Filter RHS items in LINQ Group Join

1 Answers1