What's the right way to select and combine objects with LINQ?

Question

Here's a situation I run into frequently: I've got Parent and Child objects, and the Child object has a Parent property. I want to run a query that gets the child objects and joins each one to the correct parent object:

Dim db = New DataContextEx()

get the children, along with the corresponding parent
Dim Children = From x In db.ChildTable
                Join y In db.ParentTable
                On x.ParentId Equals y.Id
                Execute x.Parent = y       <-- pseudocode
                Select x   

The pseudocode shows what I want to accomplish: to return the child object x but with the code after the (fake) Execute statement executed. I can think of a lot of ways to accomplish the end goal, but they all have a lot more lines of code and/or creation of temporary objects or functions that I find inelegant. (Note this is VB.NET syntax, but it's not a VB syntax question, since AFAIK C# would have the same problem.)

So what would be the cleanest way to do what I'm trying to do?

Edit: People have asked about what ORM I'm using, but this is really a plain vanilla LINQ question; I'm not trying to convert this into logic to be run on the server, I just want some syntactic sugar to run the code client side after the query has been run on the server.

Answer 1

You could use an anonymous type when projecting results. C# example:

var items = from x In db.ChildTable
            join y In db.ParentTable on x.ParentId equals y.Id
            select new { Child =x , Parent=y };

Then assign the parent properties.

foreach(var item in items)
{
    item.Child.Parent = item.Parent;
}

return items.Select(item => item.Child);

Also you may want to use some ORM solutions for this instead of rolling your own.

Answer 2

You can do it like this:

Add a method to do your logic to Child class that returns this or Me in vb

class Child
{
    public Child GetWithAssignedParent(Parent y)
    {
        this.Parent = y;
        return this;
    }
}

use this method in your select query to do the logic and return the updated instances of Child class:

var children = from x In db.ChildTable
join y In db.ParentTable on x.ParentId equals y.Id
select x.GetWithAssignedParent(y);

or use Func<>

var children = from x in children
join y in parents on x.ParentId equals y.Id
select
   new Func<Child>(() =>
   {
      x.Parent = y;
      return x;
   }).Invoke();

Answer 3

These suggestions, especially @Paul's, were very helpful, but my final version is different enough that I'm going to write it up as my own answer.

I implemented the following totally general extension function:

<Extension()> _
Public Function Apply(Of T)(ByVal Enumerable As IEnumerable(Of T), ByVal action As Action(Of T)) As IEnumerable(Of T)
    For Each item In Enumerable
        action(item)
    Next

    Return Enumerable
End Function

This is the same as ForEach, except that it returns the incoming sequence, and I think the name Apply makes it clear that there are side effects. Then I can write my query as:

Dim Children = (
            From x In db.ChildTable
            Join y In db.ParentTable
            On x.ParentId Equals y.Id
            ).
            Apply(Sub(item) item.x.Parent = item.y).
            Select(Function(item) item.x)

It would of course be better if there were a way to have custom query operators, so I wouldn't have to use the lambda syntax, but even so this seems very clean and reusable. Thanks again for all the help.