dotnet / dotnet/efcore

GroupJoin throws when applied on an anonymous return type output of another Linq

Open
#28,248 3 comments 1 reaction 0 assignees View on GitHub
area-query customer-reported ef6-parity
Dominant language
C#
Stars
14.8k
Forks
3.4k
PR merge metrics
PR metrics pending

Description

Models:

```
public partial interface IUser
{
long Id { get; }
bool IsActive { get; }
long CreatedById { get; }
}

public partial interface IUserSiteAccess
{
long Id { get; }
bool IsActive { get; }
long? UserId { get; }
long CreatedById { get; }
}
public partial class EUser : IUser
{
public EUser()
{
}

long Id { get; set; }
bool IsActive { get; set; }
long CreatedById { get; set; }
}
public partial class EUserSiteAccess : IUserSiteAccess
{
public EUserSiteAccess()
{
}

long Id { get; set; }
bool IsActive { get; set; }
long? SiteConfigId { get; set;}
long? UserId { get; set;}
long CreatedById { get; set; }
}

public partial class BananaContext : AbstractDbContext
{
public BananaContext()
{
ChangeTracker.AutoDetectChangesEnabled = false;
}

public BananaContext(DbContextOptions options)
{
}
public virtual DbSet UserSiteAccesses { get; set; }
public virtual DbSet Users { get; set; }
}
```

**Working Sample (without anonymous type in the first query):**

```
var userSiteIds1 = from u in context.UserSiteAccesses
select u.UserId;

var finalQuery1 = (from users in context.Users
join userSites in userSiteIds1 on users.Id equals userSites into otherUserSitesRS
from otherUserSites in otherUserSitesRS.DefaultIfEmpty()
where otherUserSites == null
select users);

**SQL Produced:**
SELECT [u].[Id], [u].[CreatedById], [u].[IsActive]
FROM [Users] AS [u]
LEFT JOIN [UserSiteAccesses] AS [u0] ON [u].[Id] = [u0].[UserId]
WHERE [u0].[UserId] IS NULL
```

**Failing Case:**

```
var userSiteIds = from u in context.UserSiteAccesses
select new
{
u.UserId
};

var finalQuery = (from users in context.Users
join userSites in userSiteIds on users.Id equals userSites.UserId into otherUserSitesRS
from otherUserSites in otherUserSitesRS.DefaultIfEmpty()
where otherUserSites == null
select users);
```
**Exception & Stack Trace:**

```
The LINQ expression 'DbSet()
.SelectMany(
collectionSelector: e => DbSet()
.Where(e0 => (long?)e.Id == e0.UserId)
.DefaultIfEmpty()
.Select(e0 => new { UserId = e0.UserId }),
resultSelector: (e, c) => new TransparentIdentifierf__AnonymousType0>(
Outer = e,
Inner = c
))
.Where(ti => ti.Inner == null)' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.g__CheckTranslated|15_0(ShapedQueryExpression translated, <>c__DisplayClass15_0& )
at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query)
at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass9_0`1.b__0()
at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query)
at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.GetEnumerator()
at System.Collections.Generic.LargeArrayBuilder`1.AddRange(IEnumerable`1 items)
at System.Collections.Generic.EnumerableHelpers.ToArray[T](IEnumerable`1 source)
at System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source)
at System.Linq.SystemCore_EnumerableDebugView`1.get_Items()
```

**NOTE:** The above failing query works fine in EF6 and produces the below SQL:
```
SELECT [Extent1].[Id], [Extent1].[CreatedById], [Extent1].[IsActive]
FROM [Users] AS [Extent1]
LEFT OUTER JOIN [UserSiteAccesses] AS [Extent2] ON [Extent1].[Id] = [Extent2].[UserId]
WHERE [Extent1].[UserId] IS NULL
```

### Include provider and version information

EF Core version: 7.0.0-preview.4
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 6.0
Operating system: Win 10
IDE: Visual Studio Professional 2022 (64-bit) Version 17.2.4

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.