grouping and flatening 1:M:M:1 related data in Entity Framework Core/LINQ

[MontyGvMC]

Hello together.
I get exceptions when I try to query 1:M:M:1 related data and especially when I try to flatten and group them.
This happens in a .NET Core web api.

It happens in the following scenario:
I have a database where tasks are assigned to persons. Each task can only be assigned to one person. For each task the person needs access to
zero, one or more rooms.

In the PersonController I try to return a list of rooms to which the person needs access to. The result should be flatened (only the rooms and not the tasks) and grouped (without duplicates).

Unfortunatly in any way I try to do it I ran into unwanted resluts or exceptions (more on the exceptions later).
I also tried to get some help on Stackoverflow: http://stackoverflow.com/questions/41615953/grouping-and-flatening-1mm1-related-data-in-entity-framework-core-linq/

For the scenario there is the following database:

CREATE TABLE [dbo].[Person] (
    [Id]        INT           IDENTITY (1, 1) NOT NULL,
    [Firstname] NVARCHAR (30) NOT NULL,
    [Lastname]  NVARCHAR (30) NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);

CREATE TABLE [dbo].[Task] (
    [Id]       INT           IDENTITY (1, 1) NOT NULL,
    [PersonId] INT           NOT NULL,
    [Name]     NVARCHAR (50) NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [FK_Task_Person] FOREIGN KEY ([PersonId]) REFERENCES [dbo].[Person] ([Id])
);

CREATE TABLE [dbo].[Room] (
    [Id]   INT           IDENTITY (1, 1) NOT NULL,
    [Name] NVARCHAR (50) NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);

CREATE TABLE [dbo].[TaskRoom] (
    [Id]     INT IDENTITY (1, 1) NOT NULL,
    [TaskId] INT NOT NULL,
    [RoomId] INT NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [FK_TaskRoom_Room] FOREIGN KEY ([RoomId]) REFERENCES [dbo].[Room] ([Id]),
    CONSTRAINT [FK_TaskRoom_Task] FOREIGN KEY ([TaskId]) REFERENCES [dbo].[Task] ([Id])
);

INSERT INTO [Person] (Firstname,Lastname) VALUES ('First1', 'Last1');

INSERT INTO [Room] (Name) VALUES ('test room 1');
INSERT INTO [Room] (Name) VALUES ('test room 2');
INSERT INTO [Room] (Name) VALUES ('test room 3');
INSERT INTO [Room] (Name) VALUES ('test room 4');

INSERT INTO [Task] (PersonId, Name) VALUES (1, 'Task 1');
INSERT INTO [Task] (PersonId, Name) VALUES (1, 'Task 2');
INSERT INTO [Task] (PersonId, Name) VALUES (1, 'Task 3');
INSERT INTO [Task] (PersonId, Name) VALUES (1, 'Task 4');

INSERT INTO [TaskRoom] (TaskId, RoomId) VALUES (1, 1);
INSERT INTO [TaskRoom] (TaskId, RoomId) VALUES (2, 2);
INSERT INTO [TaskRoom] (TaskId, RoomId) VALUES (2, 3);
INSERT INTO [TaskRoom] (TaskId, RoomId) VALUES (3, 3);
-- Task 4 exists but has not yet a room assigned

The according controller:

namespace PersonExample.Controllers
{
    [Route("api/[controller]")]
    public class PersonController : Controller
    {

        private readonly PersonDBContext _dbContext;
        private readonly ILogger<PersonRepository> _logger;

        public PersonController(PersonDBContext dbContext, ILogger<PersonRepository> logger)
        {
            _dbContext = dbContext;
            _logger = logger;
        }

        [HttpGet("RoomAccess/{personId:int}")]
        public IActionResult RoomAccess(int personId)
        {
            _logger.LogDebug(string.Format("{0}.RoomAccess(person id: {1})", GetType().Name, personId));

            var result = _dbContext.Person
                .Include(p => p.Task).ThenInclude(t => t.TaskRoom).ThenInclude(tr => tr.Room)
                .Where(p => p.Id == personId)
                .Select(person => new
                    {
                        person.Id,
                        person.Firstname,
                        person.Lastname,

                        rooms = person.Task.SelectMany(ta => ta.TaskRoom.Select(tr => new { id = tr.Room.Id, name = tr.Room.Name })).Distinct()
                    }
                )
                .FirstOrDefault();

            if (result == null) 
            {
                return NotFound(string.Format("person id: {0}", personId));
            }

            return Ok(result);
        }
    
    }
}

Further technical details

EF Core version:
"Microsoft.EntityFrameworkCore.SqlServer": "1.1.0",
"Microsoft.EntityFrameworkCore.SqlServer.Design": "1.1.0",
"Microsoft.EntityFrameworkCore.Tools": "1.1.0-preview4-final"

Database Provider: (e.g. Microsoft.EntityFrameworkCore.SqlServer) (local mssql db)
Operating system: Win7 x64
IDE: Visual Studio 2015

System.ArgumentOutOfRangeException was unhandled by user code
  HResult=-2146233086
  Message=Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index
  ParamName=index
  Source=System.Private.CoreLib
  StackTrace:
       at System.ThrowHelper.ThrowArgumentOutOfRangeException(ExceptionArgument argument, ExceptionResource resource)
       at System.Collections.ObjectModel.Collection`1.RemoveAt(Int32 index)
       at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.Internal.NavigationRewritingExpressionVisitor.RewriteSelectManyNavigationsIntoJoins(QuerySourceReferenceExpression outerQuerySourceReferenceExpression, IEnumerable`1 navigations, AdditionalFromClause additionalFromClauseBeingProcessed)
       at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.Internal.NavigationRewritingExpressionVisitor.<>c__DisplayClass20_0.<VisitMember>b__0(IEnumerable`1 ps, IQuerySource qs)
       at Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor.BindPropertyExpressionCore[TResult](Expression propertyExpression, IQuerySource querySource, Func`3 propertyBinder)
       at Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor.BindNavigationPathPropertyExpression[TResult](Expression propertyExpression, Func`3 propertyBinder)
       at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.Internal.NavigationRewritingExpressionVisitor.VisitMember(MemberExpression node)
       at Remotion.Linq.Clauses.FromClauseBase.TransformExpressions(Func`2 transformation)
       at Remotion.Linq.QueryModelVisitorBase.VisitQueryModel(QueryModel queryModel)
       at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.Internal.NavigationRewritingExpressionVisitor.Rewrite(QueryModel queryModel, QueryModel parentQueryModel)
       at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.Internal.NavigationRewritingExpressionVisitor.VisitSubQuery(SubQueryExpression expression)
       at Remotion.Linq.Clauses.FromClauseBase.TransformExpressions(Func`2 transformation)
       at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.Internal.NavigationRewritingExpressionVisitor.NavigationRewritingQueryModelVisitor.VisitAdditionalFromClause(AdditionalFromClause fromClause, QueryModel queryModel, Int32 index)
       at Remotion.Linq.QueryModelVisitorBase.VisitBodyClauses(ObservableCollection`1 bodyClauses, QueryModel queryModel)
       at Remotion.Linq.QueryModelVisitorBase.VisitQueryModel(QueryModel queryModel)
       at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.Internal.NavigationRewritingExpressionVisitor.Rewrite(QueryModel queryModel, QueryModel parentQueryModel)
       at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.Internal.NavigationRewritingExpressionVisitor.VisitSubQuery(SubQueryExpression expression)
       at System.Linq.Expressions.ExpressionVisitor.VisitAndConvert[T](ReadOnlyCollection`1 nodes, String callerName)
       at Remotion.Linq.Parsing.RelinqExpressionVisitor.VisitNew(NewExpression expression)
       at Remotion.Linq.Clauses.SelectClause.TransformExpressions(Func`2 transformation)
       at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.Internal.NavigationRewritingExpressionVisitor.NavigationRewritingQueryModelVisitor.VisitSelectClause(SelectClause selectClause, QueryModel queryModel)
       at Remotion.Linq.QueryModelVisitorBase.VisitQueryModel(QueryModel queryModel)
       at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.Internal.NavigationRewritingExpressionVisitor.Rewrite(QueryModel queryModel, QueryModel parentQueryModel)
       at Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor.OptimizeQueryModel(QueryModel queryModel)
       at Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor.CreateQueryExecutor[TResult](QueryModel queryModel)
       at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](Expression query, INodeTypeProvider nodeTypeProvider, IDatabase database, ILogger logger, Type contextType)
       at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass19_0`1.<CompileQuery>b__0()
       at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQueryCore[TFunc](Object cacheKey, Func`1 compiler)
       at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query)
       at PersonExample.Controllers.PersonController.RoomAccess(Int32 personId)
       at lambda_method(Closure , Object , Object[] )
       at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.<InvokeActionMethodAsync>d__27.MoveNext()
  InnerException: 

Any way to get this working?
Also with those nuget versions:
"Microsoft.EntityFrameworkCore.Tools": "1.1.0-preview4-final",
"Microsoft.EntityFrameworkCore.SqlServer": "1.0.1",
"Microsoft.EntityFrameworkCore.SqlServer.Design": "1.0.1",
"Microsoft.EntityFrameworkCore": "1.1.0"

[MontyGvMC]

The good news: it works using the following code (added a .ToList() after the Where)

        var result = _dbContext.Person
                .Include(p => p.Task).ThenInclude(t => t.TaskRoom).ThenInclude(tr => tr.Room)
                .Where(p => p.Id == personId)
                .ToList()
                .Select(person => new
                    {
                        person.Id,
                        person.Firstname,
                        person.Lastname,
                        rooms = person.Task.SelectMany(ta => ta.TaskRoom.Select(tr => new { id = tr.Room.Id, name = tr.Room.Name })).Distinct()
                    }
                )
                .FirstOrDefault();

The bad news: I don't understand the difference enough to get it working in my complex "real life" scenario where the query is aproximatly 100 lines big (collecting data from 15 database tables) and there it results in an exception

System.NullReferenceException was unhandled by user code
  HResult=-2147467261
  Message=Object reference not set to an instance of an object.
  Source=PersonExampleProject
  StackTrace:
       at PersonExampleProject.Controllers.PersonController.<>c.<RoomAccess>b__6_8(TaskRoom sm)
       at System.Linq.Enumerable.SelectEnumerableIterator`2.MoveNext()
       at System.Linq.Enumerable.<SelectManyIterator>d__159`2.MoveNext()
       at System.Linq.Enumerable.DistinctIterator`1.MoveNext()
       at Newtonsoft.Json.Serialization.JsonSerializerInternalWriter.SerializeList(JsonWriter writer, IEnumerable values, JsonArrayContract contract, JsonProperty member, JsonContainerContract collectionContract, JsonProperty containerProperty)
       at Newtonsoft.Json.Serialization.JsonSerializerInternalWriter.SerializeObject(JsonWriter writer, Object value, JsonObjectContract contract, JsonProperty member, JsonContainerContract collectionContract, JsonProperty containerProperty)
  InnerException: 

The code line causing the problem seems to be

tr => new { id = tr.Room.Id, name = tr.Room.Name }

My current project.json (with all included package versions) looks like:

{
  "dependencies": {
    "Microsoft.NETCore.App": "1.1.0",

    "Microsoft.ApplicationInsights.AspNetCore": "2.0.0-beta1",
    
    "Microsoft.AspNetCore.Mvc": "1.1.0",
    "Microsoft.AspNetCore.Routing": "1.1.0",
    "Microsoft.AspNetCore.Server.IISIntegration": "1.1.0",
    "Microsoft.AspNetCore.Server.Kestrel": "1.1.0",
    "Microsoft.AspNetCore.StaticFiles": "1.1.0",

    "Microsoft.Extensions.Configuration.EnvironmentVariables": "1.1.0",
    "Microsoft.Extensions.Configuration.FileExtensions": "1.1.0",
    "Microsoft.Extensions.Configuration.Json": "1.1.0",
    "Microsoft.Extensions.Logging": "1.1.0",
    "Microsoft.Extensions.Logging.Console": "1.1.0",
    "Microsoft.Extensions.Logging.Debug": "1.1.0",
    "Microsoft.Extensions.Options.ConfigurationExtensions": "1.1.0",

    "Microsoft.EntityFrameworkCore": "1.1.0",
    "Microsoft.EntityFrameworkCore.Tools": "1.1.0-preview4-final",
    "Microsoft.EntityFrameworkCore.SqlServer": "1.1.0",
    "Microsoft.EntityFrameworkCore.SqlServer.Design": "1.1.0",

    "NLog.Extensions.Logging": "1.0.0-*",
    "Swashbuckle": "6.0.0-beta902"
  },


  "runtimes": {
    "win7-x64": {},
    "win10-x64": {},
    "ubuntu.14.04-x64": {}
  },

  "tools": {
    "Microsoft.EntityFrameworkCore.Tools": "1.1.0-preview4-final",
    "Microsoft.AspNetCore.Razor.Tools": "1.1.0-preview4-final",
    "Microsoft.AspNetCore.Server.IISIntegration.Tools": "1.1.0-preview4-final"
  },

  "frameworks": {
    "netcoreapp1.0": {
      "imports": [
        "dotnet5.6"
      ]
    }
  },

  "buildOptions": {
    "emitEntryPoint": true,
    "preserveCompilationContext": true,
    "xmlDoc": true
  },

  "runtimeOptions": {
    "configProperties": {
      "System.GC.Server": true
    }
  },

  "publishOptions": {
    "include": [
      "wwwroot",
      "logs",
      "Views",
      "Areas/**/Views",
      "appsettings.json",
      "web.config",
      "nlog.config"
    ]
  },

  "scripts": {
    "postpublish": [ "dotnet publish-iis --publish-folder %publish:OutputPath% --framework %publish:FullTargetFramework%" ]
  }
}

Can anyone please explain me what .ToList() does in the background making the query work but the json serializer fail?

[MontyGvMC]

well, in my real life example I missed the Include() (s) and ThenIncludes().
Now there it works too.
(Without the includes the taskroom and room was empty which caused the exception above I guess)

The code for the flattened and ordered query is

[HttpGet("RoomAccess/{personId:int}")]
        public IActionResult RoomAccess(int personId)
        {
            _logger.LogDebug(string.Format("{0}.RoomAccess(person id: {1})", GetType().Name, personId));

            var result = _dbContext.Person
                .Include(p => p.Task).ThenInclude(t => t.TaskRoom).ThenInclude(tr => tr.Room)
                .Where(p => p.Id == personId)
                .ToList()
                .Select(person => new
                    {
                        person.Id,
                        person.Firstname,
                        person.Lastname,

                        rooms = person.Task.SelectMany(ta => ta.TaskRoom.Select(
                            tr => new { id = tr.Room.Id, name = tr.Room.Name })
                        )
                        .Distinct()
                        .OrderBy(adt => adt.name)
                    }
                )
                .FirstOrDefault();


            if (result == null) 
            {
                return NotFound(string.Format("person id: {0}", personId));
            }

            return Ok(result);
        }

Thx for the help

but still don't know what .ToList() now causes internally.

Does it mean it queries all the fields from the tables, creates the list of objects in the memory of the controller and then filters the data (aka select the return fields) within my web api?

[ole1986]

@MontyGvMC to answer your question, yes! So, ToList() causes EF to request the SQL server and receive the data. Rest is done in memory in your application