DefaultIfEmpty throwing ArgumentOutOfRangeException

[glennfordd]

If I call on DefaultIfEmpty (to make a left join), EF throws an ArgumentOutOfRangeException but removing it fixes the issue though not producing the desired result. The code is working fine on EF6.

Querying directly to the "Join" class produce a left join to that class and an inner join to the Organisation table (should be left join as well).

Scenario
The list should display all users regardless if it has an organisation or not.

DbSets:

 public class User
 {
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string PhoneNumber { get; set; }
    public string EmailAddress { get; set; }
    public List<OrganisationUser> OrganisationUser { get; set; }
 }

 public class Organisation
 {
    public int Id { get; set; }   
    public string Name { get; set; }
    public List<OrganisationUser> OrganisationUser { get; set; }
 }

 public class OrganisationUser
 {
    public int OrganisationId { get; set; }
    public Organisation Organisation { get; set; }

    public int UserId { get; set; }
    public User User { get; set; }
 }

DbContext:

 public DbSet<User> Users { get; set; }
 public DbSet<Organisation> Organisations { get; set; }

Query:

var users = (from user in _context.Users 
             from organisation in _context.Organisations.Where(o => o.Users.Any(u => u.UserId == user.Id)).DefaultIfEmpty()
             select new ViewModels.UserIndex()
             {
                  Id = user.Id,
                  FirstName = user.FirstName,
                  LastName = user.LastName,
                  Organisation = organisation.Name
             }).ToList();

// Different query but should produce same result
var users = (from user in _context.Users 
             from organisation in user.Organisations.DefaultIfEmpty()
             select new ViewModels.UserIndex()
             {
                  Id = user.Id,
                  FirstName = user.FirstName,
                  LastName = user.LastName,
                  Organisation = organisation.Name
             }).ToList();

// Inner joins to the Organisation table thus messing up the result
var users = (from user in _context.Users 
             join ou in _context.OrganisationUsers on user.Id equals ou.UserId into link
             from organisation in link.DefaultIfEmpty()
             select new ViewModels.UserIndex()
             {
                  Id = user.Id,
                  FirstName = user.FirstName,
                  LastName = user.LastName,
                  Organisation = organisation == null ? "N/A" : organisation.Organisation.Name
             }).ToList();

SQL Produced

EF Core (without DefaultIfEmpty)

SELECT [user].[Id], [user].[FirstName], [user].[LastName], [o].[Name]
FROM [Context].[Users] AS [user]
CROSS JOIN [Context].[Organisations] AS [o]
WHERE EXISTS (
    SELECT 1
    FROM [Context].[OrganisationUserLinks] AS [u]
    WHERE ([u].[UserId] = [user].[Id]) AND ([o].[Id] = [u].[OrganisationId]))

EF6

SELECT 
    1 AS [C1], 
    [Extent1].[Id] AS [Id], 
    [Extent1].[FirstName] AS [FirstName], 
    [Extent1].[LastName] AS [LastName], 
    [Extent2].[Name] AS [Name]
    FROM  [Context].[Users] AS [Extent1]
    LEFT OUTER JOIN [Context].[Organisations] AS [Extent2] ON  EXISTS (SELECT 
        1 AS [C1]
        FROM [Context].[OrganisationUserLinks] AS [Extent3]
        WHERE ([Extent2].[Id] = [Extent3].[OrganisationId]) AND ([Extent3].[UserId] = [Extent1].[Id])
    )

Desired SQL

SELECT 
	u.Id, 
	u.FirstName, 
	u.LastName, 
	u.Email, 
	o.Name 
FROM dbo.Users u
LEFT JOIN dbo.OrganisationUsers ou ON ou.UserId = u.Id
LEFT JOIN dbo.Organisations o ON o.Id = ou.OrganisationId

Exception Message:

Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index

Stack trace:

   at System.ThrowHelper.ThrowArgumentOutOfRange_IndexException()
   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 System.Linq.Expressions.MemberExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression 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 System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Remotion.Linq.Clauses.WhereClause.TransformExpressions(Func`2 transformation)
   at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.Internal.NavigationRewritingExpressionVisitor.NavigationRewritingQueryModelVisitor.VisitWhereClause(WhereClause whereClause, 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.Visit(Expression node)
   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.EntityQueryModelVisitor.OptimizeQueryModel(QueryModel queryModel)
   at Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor.CreateQueryExecutor[TResult](QueryModel queryModel)
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   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 Remotion.Linq.QueryableBase`1.GetEnumerator()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at Admin.Controllers.UsersController.Index() in ..\Controllers\UsersController.cs:line 30
   at lambda_method(Closure , Object , Object[] )
   at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.<InvokeActionMethodAsync>d__27.MoveNext()

Further technical details

EF Core version: Microsoft.EntityFrameworkCore 1.1.1
Database Provider: Microsoft.EntityFrameworkCore.SqlServer 1.1.1
Operating system: Windows 10
IDE: Visual Studio 2015

[maumar]

@gnaungayan try the following:

                var users4 = (from user in ctx.Users
                              join ou in ctx.OrganisationUsers on user.Id equals ou.UserId into link1
                              from ou in link1.DefaultIfEmpty()
                              join o in ctx.Organisations on ou.OrganisationId equals o.Id into link2
                              from o in link2.DefaultIfEmpty()
                              select new ViewModels.UserIndex()
                              {
                                  Id = user.Id,
                                  FirstName = user.FirstName,
                                  LastName = user.LastName,
                                  Organisation = o == null ? "N/A" : o.Name
                              }).ToList();

Currently, SelectMany-GroupJoin_DefaultIfEmpty pattern is the way to model LEFT JOIN in EF Core. You are seeing INNER JOIN for the second navigation, because that JOIN comes from navigation rewrite. In the rewrite we look at the navigation metadata itself to determine whether it should be optional or requited, and in your case navigation OrganizationUser -> Organization is required.

For cases when navigation expansion is used from the start, we "propagate" the optional navigation, so:

entity.OptionalNavigation.RequiredNavigation.Name will produce two LEFT JOINs. However for case when navigations are mixed with "manually" created LEFT JOINs (using the pattern above), we don't detect this currently.

[glennfordd]

@maumar Thanks for the alternative query although unnecessary columns are being selected despite the projection, I guess this is another bug?

I also notice that using this query which is basically the same you gave produces a different SQL Query. It also noted that the below query will use CASE-WHEN pattern compared to the above query

 var users = (from user in this._context.Users
              from ou in this._context.OrganisationUser.Where(ou => ou.UserId == user.Id).DefaultIfEmpty()
              from organisation in this._context.Organisations.Where(o => o.Id == ou.OrganisationId).DefaultIfEmpty()                                                               
              select new Result()
              {
                    Id = user.Id,
                    FirstName = user.FirstName,
                    LastName = user.LastName,                             
                    Organisation = organisation == null ? "N/A" : organisation.Name
               }).ToListAsync();

[maumar]

@gnaungayan additional columns are projected due to #6647. This issue has been fixed so this shouldn't be happening in the next version. The second query produces different SQL because EF Core (unlike EF6) doesn't recognize the from from-from-where-defaultifempty pattern as LEFT JOIN and so it will perform naive translation. We have issue to track the improvement for this here - #7887

[maumar]

Closing this - tracking improvement to the null propagation in separate issue: #7975