In Linq, how do I add type checking to 'include clauses' more than one level deep?

Question

This is the model:

Public Class Parent
    Public Property Name As String
    Public Property ID As Integer
    Public Property child1 As New List(Of Child1)
End Class

Public Class Child1
    Public Property ID As Integer
    Public Property Name As String
    Public Property Child2 As New List(Of Child2)
End Class

Public Class Child2
    Public Property ID As Integer
    Public Property Name As String
    Public Property Child3 As New List(Of Child3)
End Class

Public Class Child3
    Public Property ID As Integer
    Public Property Name As String
    Public Property Child4 As New List(Of Child4)
End Class

Public Class Child4
    Public Property ID As Integer
    Public Property Name As String
End Class

To include all tables in a query I have been using:

TheDataContext.Parent.Include("Child1").Include("Child1.Child2").Include("Child1.Child2.Child3").Include("Child1.Child2.Child3.Child4").Where(some condition).ToList()

So that's ridiculously messy and has no type checking. I did learn the following works if I only need to go one child table deep:

TheDataContext.Parent.Include(NameOf(Child1)).Where(some condition).ToList()

But if I try to apply the 'NameOf' technique on the next child level down like this

.Include(NameOf(Child1.Child2))...

That fails at runtime saying Parent does not declare a navigation property with the name Child1.Child2, which in retrospect makes sense, but I don't see how I would fix that. And if I use

.Include(NameOf(Parent.Child1.Child2))...

That fails in the editor saying reference to a non-shared member requires an object reference, which I understand, but I don't believe that's a solution, my model shouldn't be using Shared.

This SO Post suggests a lambda in the Include, with a Select that contains another lambda, which I understand in C#, but in vb.net the .include doesn't seem to even allow the first lambda. The following fails in the editor:

.include(function(x) x.Child1) or anything else in the lambda

Perhaps I'm missing an Imports, but short of that I simply don't know the syntax to do in vb.net that which seems so elegant in that SO post.

How do I convert over to type checking for all levels of children?

Added after applying suggestions and answers below:

Not disregarding Harald Coppoolse's excellent answer, this was how I converted the string-based .Include's to an .Include with type checking. While this was technically the answer to my question, as Harald's answer shows, an answer is not necessarily the right answer because there is likely an important question that hasn't been asked.

TheDataContext.Parent.Include(Function(a) a.child1.Select(Function(b) b.child2.Select(Function(c) c.child3.Select(Function(d) d.child4)))).ToList()

Answer 1

My answer is in C#, but the idea is also for VB. I guess you get the gist.

Use Select instead of Include. Select is completely type safe

One of the slower parts of a database query is the data transfer from the database management system to your local computer. It is wise to limit this data to only the data you actually plan to use.

Your use of Include transfers way more data than you use. For instance, your Child1 has an Id. All Child2 Children of Child1 have a foreign key Child1Id which equal the Id of Child1.

So if you select Child1 with Id 4, and this Child1 has 100 Children, all these Children will have a foreign key Child1Id that equal 4. Thus you are transferring the value of the primary key of Child1 101 times, while you already know that they will all have the same value.

If you use Select instead of Include you will be able to select only the properties you really plan to use. Another side effect of Select is that it is completely type safe, which would solve your problem.

In my experience when using Entity Framework is that I only use Include if I plan to change the fetched value. A DbContext can only change a value after it has been fetched (or you'll have to use workarounds).

So if you want all (or some) Parents with all their Children, GrandChildren etc, and you want it type safe, do as follows:

var parentsWithTheirDescendants = myDbContext.Parents
    .Where(parent => ...)
    .Select(parent => new
    {
        // select only the properties you plan to use
        Id = parent.Id,
        Name = parent.Name,

        Children = parent.Child1s
            .Where(child1 => ...)           // only if you don't want all Children
            .Select(child1 => new
            {
                  // again: select only the properties you plan to use
                Id = child1.Id,
                Name = child1.Name

                // not needed:
                // ParentId = child1.ParentId

                GrandChildren = child1.Child2s
                    .Where(child2 => ...)
                    .Select(child2 => new
                    {   // etc.
                    })
                }),
            });

This is completely type safe. It will be impossible to select any properties you don't have. Within limitations you even can create new properties:

    .Select(child4 => new
    {
         FullName = child4.FirstName + child4.LastName,
         Age = (int)(Today - child4.BirthDay).TotalYears,
    });

By the way, there is a type safe version of Queryable.Include Can't you use that in VB?

Summarized:

For queries use Select. Select only properties you plan to use
Only use Include if you plan to change / remove the fetched item