efcore The LINQ expression 'ProjectionBindingExpression: 0/1/2' could not be translated.

trafficstars

The below query works fine with EF6 and also produces correct SQL but fails with EFCore 7.0

I have taken a sample code out of a long LINQ expression, essentially when a LINQ having projection as 'x2' below is used in another LINQ expression without specifying client evaluation then EFCore throws error:


            //2. The LINQ expression 'Projection....'
            var x2 = from u in Repository.Users
                     group u.UserAuthorityLimits by u.Id into authgrp
                     select new
                     {
                         Id = authgrp.Key,
                         Entity = authgrp.FirstOrDefault()
                     };

            var x21 = (from users in Repository.Users
                       join paymentSchedule in x2 on users.Id equals paymentSchedule.Id
                       select new
                       {
                           Entity = users,
                           Balance = users.UserEmailAddresses
                       });
            var res = x21.ToList();

The LINQ expression 'ProjectionBindingExpression: 2' 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.Internal.RelationalProjectionBindingExpressionVisitor.Visit(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.VisitNew(NewExpression newExpression)
   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Visit(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.VisitNew(NewExpression newExpression)
   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Visit(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Translate(SelectExpression selectExpression, Expression expression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateSelect(ShapedQueryExpression source, LambdaExpression selector)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass9_0`1.<Execute>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)

Include verbose output

Stack Trace:

   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Visit(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.VisitNew(NewExpression newExpression)
   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Visit(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.VisitNew(NewExpression newExpression)
   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Visit(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Translate(SelectExpression selectExpression, Expression expression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateSelect(ShapedQueryExpression source, LambdaExpression selector)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateTwoParameterSelector(ShapedQueryExpression source, LambdaExpression resultSelector)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateJoin(ShapedQueryExpression outer, ShapedQueryExpression inner, LambdaExpression outerKeySelector, LambdaExpression innerKeySelector, LambdaExpression resultSelector)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](Expression query, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass9_0`1.<Execute>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.EntityQueryProvider.Execute[TResult](Expression expression)
   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.SystemCore_EnumerableDebugView`1.get_Items()

EF6 Generated SQL:

SELECT 
    [Project3].[Id1] AS [Id], 
    [Project3].[LoginFailureCounter] AS [LoginFailureCounter], 
    [Project3].[Id] AS [Id1], 
    [Project3].[FirstName] AS [FirstName], 
    [Project3].[CreatedById] AS [CreatedById], 
    [Project3].[CreatedTime] AS [CreatedTime], 
    [Project3].[UpdatedById] AS [UpdatedById], 
    [Project3].[UpdatedTime] AS [UpdatedTime], 
    [Project3].[LastName] AS [LastName], 
    [Project3].[FullName] AS [FullName], 
    [Project3].[LoginName] AS [LoginName], 
    [Project3].[IsWindowsAuthenticated] AS [IsWindowsAuthenticated], 
    [Project3].[Password] AS [Password], 
    [Project3].[PhoneNumber] AS [PhoneNumber], 
    [Project3].[FaxNumber] AS [FaxNumber], 
    [Project3].[PhoneExtensionNumber] AS [PhoneExtensionNumber], 
    [Project3].[Email] AS [Email], 
    [Project3].[IsEmailNotificationAllowed] AS [IsEmailNotificationAllowed], 
    [Project3].[LoginEffectiveDate] AS [LoginEffectiveDate], 
    [Project3].[LoginExpiryDate] AS [LoginExpiryDate], 
    [Project3].[IsLoginBlocked] AS [IsLoginBlocked], 
    [Project3].[IsAdminBlocked] AS [IsAdminBlocked], 
    [Project3].[LoginBlockedTime] AS [LoginBlockedTime], 
    [Project3].[ForcePasswordReset] AS [ForcePasswordReset], 
    [Project3].[CreationDate] AS [CreationDate], 
    [Project3].[ActivationDate] AS [ActivationDate], 
    [Project3].[DeactivationDate] AS [DeactivationDate], 
    [Project3].[ProfilePicture_Source] AS [ProfilePicture_Source], 
    [Project3].[ProfilePicture_Type] AS [ProfilePicture_Type], 
    [Project3].[ProfilePicture_Content] AS [ProfilePicture_Content], 
    [Project3].[IsFactorAuthenticationEnabled] AS [IsFactorAuthenticationEnabled], 
    [Project3].[IsFactorAuthorizationEnabled] AS [IsFactorAuthorizationEnabled], 
    [Project3].[CanLogin] AS [CanLogin], 
    [Project3].[ApprovalStatus] AS [ApprovalStatus], 
    [Project3].[Title] AS [Title], 
    [Project3].[ESignJWTUserId] AS [ESignJWTUserId], 
    [Project3].[RowVersion] AS [RowVersion], 
    [Project3].[DefaultBusinessUnitId] AS [DefaultBusinessUnitId], 
    [Project3].[MultiFactorAuthenticationId] AS [MultiFactorAuthenticationId], 
    [Project3].[AdminUserId] AS [AdminUserId], 
    [Project3].[DomainId] AS [DomainId], 
    [Project3].[OrganizationConfigId] AS [OrganizationConfigId], 
    [Project3].[C2] AS [C1], 
    [Project3].[C1] AS [C2], 
    [Project3].[Id2] AS [Id2], 
    [Project3].[Email1] AS [Email1], 
    [Project3].[CreatedById1] AS [CreatedById1], 
    [Project3].[CreatedTime1] AS [CreatedTime1], 
    [Project3].[UpdatedById1] AS [UpdatedById1], 
    [Project3].[UpdatedTime1] AS [UpdatedTime1], 
    [Project3].[Description] AS [Description], 
    [Project3].[IsPrimary] AS [IsPrimary], 
    [Project3].[IsActive] AS [IsActive], 
    [Project3].[RowVersion1] AS [RowVersion1], 
    [Project3].[UserId] AS [UserId]
    FROM ( SELECT 
        [Extent1].[Id] AS [Id], 
        [Extent1].[FirstName] AS [FirstName], 
        [Extent1].[CreatedById] AS [CreatedById], 
        [Extent1].[CreatedTime] AS [CreatedTime], 
        [Extent1].[UpdatedById] AS [UpdatedById], 
        [Extent1].[UpdatedTime] AS [UpdatedTime], 
        [Extent1].[LastName] AS [LastName], 
        [Extent1].[FullName] AS [FullName], 
        [Extent1].[LoginName] AS [LoginName], 
        [Extent1].[IsWindowsAuthenticated] AS [IsWindowsAuthenticated], 
        [Extent1].[Password] AS [Password], 
        [Extent1].[PhoneNumber] AS [PhoneNumber], 
        [Extent1].[FaxNumber] AS [FaxNumber], 
        [Extent1].[PhoneExtensionNumber] AS [PhoneExtensionNumber], 
        [Extent1].[Email] AS [Email], 
        [Extent1].[IsEmailNotificationAllowed] AS [IsEmailNotificationAllowed], 
        [Extent1].[LoginEffectiveDate] AS [LoginEffectiveDate], 
        [Extent1].[LoginExpiryDate] AS [LoginExpiryDate], 
        [Extent1].[IsLoginBlocked] AS [IsLoginBlocked], 
        [Extent1].[IsAdminBlocked] AS [IsAdminBlocked], 
        [Extent1].[LoginBlockedTime] AS [LoginBlockedTime], 
        [Extent1].[ForcePasswordReset] AS [ForcePasswordReset], 
        [Extent1].[CreationDate] AS [CreationDate], 
        [Extent1].[ActivationDate] AS [ActivationDate], 
        [Extent1].[DeactivationDate] AS [DeactivationDate], 
        [Extent1].[ProfilePicture_Source] AS [ProfilePicture_Source], 
        [Extent1].[ProfilePicture_Type] AS [ProfilePicture_Type], 
        [Extent1].[ProfilePicture_Content] AS [ProfilePicture_Content], 
        [Extent1].[IsFactorAuthenticationEnabled] AS [IsFactorAuthenticationEnabled], 
        [Extent1].[IsFactorAuthorizationEnabled] AS [IsFactorAuthorizationEnabled], 
        [Extent1].[CanLogin] AS [CanLogin], 
        [Extent1].[LoginFailureCounter] AS [LoginFailureCounter], 
        [Extent1].[ApprovalStatus] AS [ApprovalStatus], 
        [Extent1].[Title] AS [Title], 
        [Extent1].[ESignJWTUserId] AS [ESignJWTUserId], 
        [Extent1].[DefaultBusinessUnitId] AS [DefaultBusinessUnitId], 
        [Extent1].[MultiFactorAuthenticationId] AS [MultiFactorAuthenticationId], 
        [Extent1].[AdminUserId] AS [AdminUserId], 
        [Extent1].[DomainId] AS [DomainId], 
        [Extent1].[OrganizationConfigId] AS [OrganizationConfigId], 
        [Extent1].[RowVersion] AS [RowVersion], 
        [Project2].[Id] AS [Id1], 
        [Extent4].[Id] AS [Id2], 
        [Extent4].[Email] AS [Email1], 
        [Extent4].[CreatedById] AS [CreatedById1], 
        [Extent4].[CreatedTime] AS [CreatedTime1], 
        [Extent4].[UpdatedById] AS [UpdatedById1], 
        [Extent4].[UpdatedTime] AS [UpdatedTime1], 
        [Extent4].[Description] AS [Description], 
        [Extent4].[IsPrimary] AS [IsPrimary], 
        [Extent4].[IsActive] AS [IsActive], 
        [Extent4].[UserId] AS [UserId], 
        [Extent4].[RowVersion] AS [RowVersion1], 
        CASE WHEN ([Extent4].[Id] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1], 
        [Project2].[C1] AS [C2]
        FROM   [dbo].[Users] AS [Extent1]
        INNER JOIN  (SELECT 
            [Extent2].[Id] AS [Id], 
            (SELECT TOP (1) 
                [Extent3].[Id] AS [Id]
                FROM [dbo].[Users] AS [Extent3]
                WHERE [Extent2].[Id] = [Extent3].[Id]) AS [C1]
            FROM [dbo].[Users] AS [Extent2] ) AS [Project2] ON [Extent1].[Id] = [Project2].[Id]
        LEFT OUTER JOIN [dbo].[UserEmailAddresses] AS [Extent4] ON [Extent1].[Id] = [Extent4].[UserId]
    )  AS [Project3]
    ORDER BY [Project3].[Id1] ASC, [Project3].[Id] ASC, [Project3].[C2] ASC, [Project3].[C1] ASC

Include provider and version information

EF Core version: 7.0.0-preview.4.22229.2 Database provider: Microsoft.EntityFrameworkCore.SqlServer Target framework: .NET 6.0 Operating system: Win 10 Pro IDE: Visual Studio 2022 v17.0.4

May 30 '22 11:05 ankitmatrix08

We need full repro code here for us to investigate.

May 31 '22 07:05 smitpatel

@smitpatel Models:

public partial interface IUserSiteAccess
	{
		bool IsActive { get; }
		long? SiteConfigId { get; }
		long? UserId { get; }
                long CreatedById { get; }
	}

public partial class EUserSiteAccess : PersistentEntity, IUserSiteAccess
	{ 
		public EUserSiteAccess() 
		{
		}

		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<BananaContext> options)
        {
        }
        public virtual DbSet<EUserSiteAccess> UserSiteAccesses { get; set; }
   }

Query:

     var x2 = from u in Repository.UserSiteAccesses
                     group u by u.CreatedById into authgrp
                     select new
                     {
                         Id = authgrp.Key,
                         Entity = authgrp.FirstOrDefault()
                     };

     var x21 = (from users in Repository.UserSiteAccesses
                       join paymentSchedule in x2 on users.UserId equals paymentSchedule.Id
                       select new
                       {
                           Entity = users,
                           Id = users.Id
                       });
            var res = x21.ToList();

Output in EF6:

x1 = SELECT 
    1 AS [C1], 
    [Distinct1].[CreatedById] AS [CreatedById], 
    [Limit1].[Id] AS [Id], 
    [Limit1].[IsActive] AS [IsActive], 
    [Limit1].[CreatedById] AS [CreatedById1], 
    [Limit1].[UserId] AS [UserId]
    FROM   (SELECT DISTINCT 
        [Extent1].[CreatedById] AS [CreatedById]
        FROM [dbo].[UserSiteAccesses] AS [Extent1] ) AS [Distinct1]
    OUTER APPLY  (SELECT TOP (1) 
        [Extent2].[Id] AS [Id], 
        [Extent2].[IsActive] AS [IsActive], 
        [Extent2].[UserId] AS [UserId]
        FROM [dbo].[UserSiteAccesses] AS [Extent2]
        WHERE [Distinct1].[CreatedById] = [Extent2].[CreatedById] ) AS [Limit1]

x21 = SELECT 
    1 AS [C1], 
    [Extent1].[Id] AS [Id], 
    [Extent1].[IsActive] AS [IsActive], 
    [Extent1].[CreatedById] AS [CreatedById], 
    [Extent1].[UserId] AS [UserId],
    FROM  [dbo].[UserSiteAccesses] AS [Extent1]
    INNER JOIN  (SELECT [Distinct1].[CreatedById] AS [CreatedById1]
        FROM   (SELECT DISTINCT 
            [Extent2].[CreatedById] AS [CreatedById]
            FROM [dbo].[UserSiteAccesses] AS [Extent2] ) AS [Distinct1]
        OUTER APPLY  (SELECT TOP (1) [Extent3].[Id] AS [Id]
            FROM [dbo].[UserSiteAccesses] AS [Extent3]
            WHERE [Distinct1].[CreatedById] = [Extent3].[CreatedById] ) AS [Limit1] ) AS [Apply1] ON [Extent1].[UserId] = [Apply1].[CreatedById1]

Output/Exceptions in EFCore 7_Preview4:

x1 = SELECT [t].[CreatedById], [t0].[Id], [t0].[IsActive], [t0].[UserId]
FROM (
    SELECT [u].[CreatedById]
    FROM [UserSiteAccesses] AS [u]
    GROUP BY [u].[CreatedById]
) AS [t]
LEFT JOIN (
    SELECT [t1].[Id], [t1].[CreatedById], [t1].[IsActive], [t1].[UserId]
    FROM (
        SELECT [u0].[Id], [u0].[CreatedById], [u0].[IsActive], [u0].[UserId], ROW_NUMBER() OVER(PARTITION BY [u0].[CreatedById] ORDER BY [u0].[Id]) AS [row]
        FROM [UserSiteAccesses] AS [u0]
    ) AS [t1]
    WHERE [t1].[row] <= 1
) AS [t0] ON [t].[CreatedById] = [t0].[CreatedById]

x21 = Exception:

The LINQ expression 'ProjectionBindingExpression: 2' 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.

Stack Trace:

   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Visit(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.VisitNew(NewExpression newExpression)
   at System.Linq.Expressions.NewExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Visit(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.VisitNew(NewExpression newExpression)
   at System.Linq.Expressions.NewExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Visit(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Translate(SelectExpression selectExpression, Expression expression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateSelect(ShapedQueryExpression source, LambdaExpression selector)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateTwoParameterSelector(ShapedQueryExpression source, LambdaExpression resultSelector)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateJoin(ShapedQueryExpression outer, ShapedQueryExpression inner, LambdaExpression outerKeySelector, LambdaExpression innerKeySelector, LambdaExpression resultSelector)
   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.Storage.Database.CompileQuery[TResult](Expression query, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass9_0`1.<Execute>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.EntityQueryProvider.Execute[TResult](Expression expression)
   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.SystemCore_EnumerableDebugView`1.get_Items()

Jun 01 '22 08:06 ankitmatrix08

Any update on this guys? I get this same problem in EFCore version 7.0.2.

@ankitmatrix08 did this exception go away? What did you do without havng to rewrite your query?

Jan 27 '23 04:01 ayodejii

This looks like a GroupBy-related issue... Simplified minimal repro:

_ = ctx.UserSiteAccesses
    .GroupBy(u => u.CreatedById)
    .Select(g => new { Id = g.Key, Entity = g.FirstOrDefault() })
    .Join(
        ctx.UserSiteAccesses,
        x => x.Id,
        u => u.UserId,
        (x, u) => new { Entity = u, u.Id })
    .ToList();

Unless I'm mistaken, this specific query can be simplified to the following, which does work:

_ = ctx.UserSiteAccesses
    .GroupBy(u => u.CreatedById)
    .Select(g => ctx.UserSiteAccesses.FirstOrDefault(u2 => u2.Id == g.FirstOrDefault().CreatedById))
    .ToList();

Jan 27 '23 10:01 roji

Any update on this guys? I get this same problem in EFCore version 7.0.2.

@ankitmatrix08 did this exception go away? What did you do without havng to rewrite your query?

Unfortunately we could not effort to make changes to the original query and wished it could run seamlessly as in EF6 - hence there was no solution for us and we had to back out from upgrading to EF Core :(

Feb 02 '23 13:02 ankitmatrix08

Hello. I have faced with the same problem during migration from EF6.4.4 to EF Core 7.0.13. There is minimal reproducible example:

internal class Program
{
    static void Main(string[] args)
    {
        using var context = new FooBarContext();

        var groupedFoosQuery = context.Foos
            .Where(f => f.TypeId > 5)
            .GroupBy(
                f => f.ObjectId,
                (_, list) => list.OrderByDescending(f => f.FooId).First());

        var query = 
            from foo in groupedFoosQuery
            join bar in context.Bars
                on new { ObjectId = foo.ObjectId, TypeId = foo.TypeId } equals
                   new { ObjectId = bar.ObjectId, TypeId = bar.TypeId }
                into bars
            select new { Foo = foo, Bars = bars };

        var result = query.ToList();
    }
}

public class FooBarContext : DbContext
{
    public DbSet<Foo> Foos { get; set; }
    public DbSet<Bar> Bars { get; set; }

    public string DbPath { get; }

    public FooBarContext()
    {
        var folder = Environment.SpecialFolder.LocalApplicationData;
        var path = Environment.GetFolderPath(folder);
        DbPath = Path.Join(path, "foobar.db");
    }

    protected override void OnConfiguring(DbContextOptionsBuilder options)
        => options.UseSqlite($"Data Source={DbPath}");
}

public class Foo
{
    public int FooId { get; set; }
    public int ObjectId { get; set; }
    public int TypeId { get; set; }
    public string FooName { get; set; }
}

public class Bar
{
    public int BarId { get; set; }
    public int ObjectId { get; set; }
    public int TypeId { get; set; }
    public string BarName { get; set; }
}

Error:

The LINQ expression 'ProjectionBindingExpression: 0' 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.

Stack Trace:

   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Visit(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.VisitNew(NewExpression newExpression)
   at System.Linq.Expressions.NewExpression.Accept(ExpressionVisitor visitor)
   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Visit(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Translate(SelectExpression selectExpression, Expression expression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateSelect(ShapedQueryExpression source, LambdaExpression selector)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.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.<Execute>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.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at EFCoreErrorExampleJoinOnCompositeKeys.Program.Main(String[] args) in \Program.cs:line 26

EF Core version: 7.0.13 Database provider: Microsoft.EntityFrameworkCore.Sqlite Target framework: .NET 6.0 IDE: Visual Studio 2022 17.4

Nov 10 '23 17:11 ZoySoy

I'm facing the same error when moving 2.2 to 6

Feb 23 '24 06:02 soni2511

facing the same issue, any update ?

Jun 03 '24 07:06 Kebechet

This issue is currently in the backlog. There's a good chance we'll do a general push to improve GroupBy support and fix bugs for EF 10, but it's unlikely to happen for 9. In the meantime, see above for workaround possibilities that involve modifying the query.

Jun 03 '24 08:06 roji

efcore efcore copied to clipboard

The LINQ expression 'ProjectionBindingExpression: 0/1/2' could not be translated.

Include verbose output

Include provider and version information

efcore
efcore copied to clipboard