Can not load related data with Include or ThenInclude or Select/Many with ONE query

c# entity-framework-core

Question

1 Test has 1 TestType assigned.

I need to load all TestType entities with relation to a Test OR not and Schoolclass , Subject and Assigned Pupils to a test (PupilsTests)

The .SelectMany or .Select fails here during runtime.

I tried different combinations of Include and ThenInclude but no chance to get the Tests WITH the PupilsTests.

I can only get the PupilsTests with the Tests when I start my query with the context.Tests

but that has the consequence that I get only the TestTypes assigned to a Test - innerjoin - but I need all TestTypes and their Tests and their PupilsTests and I want it in ONE query.

var testtypes = await context.TestTypes
                   .Include(x => x.Schoolclass)
                   .Include(x => x.Subject)
                   .Include(x => x.Tests.SelectMany(z => z.PupilsTests))
              .Where(t => t.SchoolyearId == schoolyearId)
              .AsNotTracking()
              .ToListAsync();



        public class TestType
{
    public TestType()
    {
        Tests = new HashSet<Test>();
    }
    // Props removed for clarity
    public int Id { 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; }
}

  public class Test
    {
        public Test()
        {
            PupilsTests = new HashSet<PupilTest>();
        }
        // Props removed for clarity
        public int Id { get; set; }           
        public ISet<PupilTest> PupilsTests { get; set; }
        public TestType TestType { get; set; }


    }
1
2
12/13/2016 8:34:43 PM

Accepted Answer

The EF Core equivalent syntax of EF6

.Include(x => x.Tests.SelectMany(z => z.PupilsTests))

is

.Include(x => x.Tests).ThenInclude(x => x.PupilsTests)

Note that there seems to be a VS Intellisense issue inside the ThenInclude, so just type the above and it will compile successfully and work.

Also please note that EF Core processes collection includes differently (does not use single query as in EF6), so the above would generate and execute 3 SQL queries.

Update: The VS Intellisense issue now is specifically mentioned in the EF Core documentation under Including multiple levels section:

Note!

Current versions of Visual Studio offer incorrect code completion options and can cause correct expressions to be flagged with syntax errors when using the ThenInclude method after a collection navigation property. This is a symptom of an IntelliSense bug tracked at https://github.com/dotnet/roslyn/issues/8237. It is safe to ignore these spurious syntax errors as long as the code is correct and can be compiled successfully.

5
8/1/2018 12:33:21 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow