LINQ - IEnumerable.Join on Anonymous Result Set in VB.NET

Question

I've long since built a way around this, but it still keeps bugging me... it doesnt help that my grasp of dynamic LINQ queries is still shakey.

For the example:

Parent has fields (ParentKey, ParentField)
Child has fields (ChildKey, ParentKey, ChildField)
Pet has fields (PetKey, ChildKey, PetField)

Child has a foreign key reference to Parent on Child.ParentKey = Parent.ParentKey
Pet has a foreign key reference to Child on Pet.Childkey = Child.ChildKey

Simple enough eh?

Lets say I have LINQ like this...

Dim Q = FROM p in DataContext.Parent _
Join c In DataContext.Child On c.ParentKey = p.ParentKey

Consider this a "base query" on which I will perform other filtering actions. Now I want to join the Pet table like this:

Q = Q.Join(DataContext.Pet, _
Function(a) a.c.ChildKey, _
Function(p As Pet) p.ChildKey, _
Function(a, p As Pet) p.ChildKey = a.c.ChildKey)

The above Join call doesnt work. I sort of understand why it doesnt work, but hopefully it'll show you how I tried to accomplish this task.

After all this was done I would have appended a Select to finish the job.

Any ideas on a better way to do this? I tried it with the PredicateBuilder with little success. I might not know how to use it right but it felt like it wasnt gonna handle the joining.

Answer 1

Well, to avoid leaving this unanswered, heres my workaround.

The Join didnt work because the compiler throws a piffy message saying that in that in the last part of the join, either both parameters must be strongly typed, or anonymous.

Function(a, p As Pet) p.ChildKey = a.c.ChildKey) 

Naturally this isnt going to be the case, as my previous query is anonymous, and I'm trying to join it to a typed LINQ class.

I cant just change the type of the variable receiving the query either, because then I can only join on the type that I'm selecting. Like, if I wanna select the Parent based on the pet, then I can only perform my join this way if I'm joining the pet directly to the parent(to my knowledge at least), which can't happen as I have to go through the child to get to the pet.

My solution, was to re-query the same variable, and join on the strong type within the anonymous type.

Dim q = From anon in q _ 
Join p In DataContext.Pet On p.ChildKey = anon.c.ChildKey

That way, I can select the parent based on the pet by appending my where like this:

q = q.Where(Function(v) v.p.PetField = "Fido"

Then the select like this:

q = q.Select(Function(v) v.anon.p)

Ugly right? But it does work.