How to do Multiple ThenInclude navigation props after One Include

Question

For a TestType I wanted to include both navigation props Schoolclass and Subject.

I could do a:

.Include(t => t.TestType)
 .ThenInclude(x => x.Subject)

but not a:

.Include(t => t.TestType)
.ThenInclude(x => x.Subject)
.ThenInclude(x => x.Schoolclass)

Thus I tried a little trick and that worked:

I included the TestType 2 times...

var test = await context.Tests.Where(t => t.SchoolyearId == schoolyearId)
                                          .Include(t => t.TestType)
                                          .ThenInclude(x => x.Subject)
                                           .Include(t => t.TestType)
                                          .ThenInclude(x => x.Schoolclass)
                                           .AsNoTracking()
                                          .ToListAsync();

Is that the official approach or is there a better one?

UPDATE

   public class TestType
    {
        public TestType()
        {
            Tests = new HashSet<Test>();
        }

        public int Id { get; set; }
        public string Name { get; set; }
        public int Weight { get; set; }
        public ISet<Test> Tests { get; set; }
        public Schoolyear Schoolyear { get; set; }  
        public Schoolclass Schoolclass { get; set; }   
        public Subject Subject { get; set; }
        public int SchoolyearId { get; set; }
    }

Answer 1

the best way is that you write before, With two .Include(t => t.TestType)

var test = await context.Tests.Where(t => t.SchoolyearId == schoolyearId)
                                      .Include(t => t.TestType)
                                      .ThenInclude(x => x.Subject)
                                       .Include(t => t.TestType)
                                      .ThenInclude(x => x.Schoolclass)
                                       .AsNoTracking()
                                      .ToListAsync();

If you see the query result in SQL Profiler, the query is that you pretend, without repeating the include to TestType (only 1 join with that table)

You have another way to do it, but i prefer the before way!

.Include("TestType.Subject") 
.Include("TestType.Schoolclass")  

Answer 2

If such type of includes is common in your codebase it may be useful to create a couple of extension methods like this:

        public static IQueryable<TEntity> IncludeMany<TEntity, TProperty>(
            [NotNull] this IQueryable<TEntity> source,
            [NotNull] Expression<Func<TEntity, TProperty>> navigationPropertyPath,
            [NotNull] params Expression<Func<TProperty, object>>[] nextProperties
            )
            where TEntity : class
        {
            foreach (var nextProperty in nextProperties)
            {
                source = source.Include(navigationPropertyPath)
                    .ThenInclude(nextProperty);
            }

            return source;
        }

        public static IQueryable<TEntity> IncludeMany<TEntity, TProperty>(
            [NotNull] this IQueryable<TEntity>source,
            [NotNull] Expression<Func<TEntity, IEnumerable<TProperty>>> navigationPropertyPath,
            [NotNull] params Expression<Func<TProperty, object>>[] nextProperties)
            where TEntity : class
        {
            foreach (var nextProperty in nextProperties)
            {
                source = source.Include(navigationPropertyPath)
                    .ThenInclude(nextProperty);
            }

            return source;
        }

With this, you'll be able to change your query to

var test = await context.Tests
    .Where(t => t.SchoolyearId == schoolyearId)
    .IncludeMany(t => t.TestType,
       x => x.Subject,
       x => x.Schoolclass
    )
    .AsNoTracking()
    .ToListAsync();

Although this does not support going more than 2 levels in depth.

Answer 3

This approach is the only one that exists in 1.1.0 :)

Better one is little different - do not load all objects in one query (DB will join 4 tables - Tests, TestType, Schoolyear, Schoolclass), because this may cause unnecessary data growth (when you will have a lot or records in tests).

If some of your tables (Schoolyear? or even TestType?) contains relatively small amount of records (comparing to "main" Tests table) - you can load TestTypes.Include(x => x.Subject).Include(x => x.Schoolclass) first, save them in some list, and then query only Tests without extra "Include". But as soon as you already have TestType with all required dependencies in memory - they may be accessed from memory.

Moreover, if some of your tables are changing rarely - use Caching to read them from DB once and re-read when necessary (remove appropriate cache entry in controllers that manage this tables).