Error using LINQ after migrating from 2.2 to 6.0

[abhiquiet]

Steps to reproduce

Simple inner join with the local list is generating an error. The same code is working well in DotNet Core 2.2

Packages referred in the project are as under:

<ItemGroup>
    <PackageReference Include="Pomelo.EntityFrameworkCore.MySql" Version="6.0.2" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="6.0.11" />
    <PackageReference Include="Newtonsoft.Json" Version="13.0.1" />
    <PackageReference Include="Swashbuckle.AspNetCore" Version="6.4.0" />
    <PackageReference Include="Oracle.ManagedDataAccess.Core" Version="3.21.80" />
    <PackageReference Include="ExcelDataReader" Version="3.6.0" />
    <PackageReference Include="ExcelDataReader.DataSet" Version="3.6.0" />
    <PackageReference Include="HtmlAgilityPack" Version="1.11.16" />
    <PackageReference Include="Microsoft.AspNetCore.StaticFiles" Version="2.2.0" />
    <PackageReference Include="System.Text.Encoding.CodePages" Version="6.0.0" />
  </ItemGroup>

The LINQ that I am trying to process is as under:

                        objGrpRtsMstData.Modules = (from m in objAdminDB.ModuleMaster
                                                    join p in objGrpRtsMstData.Projects on new { x = m.Solution, y = m.Project } 
                                                    equals new { x = p.Solution, y = p.Project }
                                                    select m).ToList();

This is a snippet of the code where I am getting the exception. objAdminDB is the Database Context, ModuleMaster is the model for the Table in the database, and objGrpRtsMstData.Projects is the list of class objects.

The issue

The system is generating exceptions while trying to execute the above simple join. The code is running seamlessly in DotNet Core 2.2 but generating the following error using DotNet EF Core 6.0

If you are seeing an exception, include the full exception details (message and stack trace).

Exception message:
The LINQ expression 'DbSet<ModuleMaster>()
    .Join(
        inner: __p_0, 
        outerKeySelector: m => new { 
            x = m.Solution, 
            y = m.Project
         }, 
        innerKeySelector: p => new { 
            x = p.Solution, 
            y = p.Project
         }, 
        resultSelector: (m, p) => m)' 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.NavigationExpandingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.Expand(Expression query)
   at Microsoft.EntityFrameworkCore.Query.QueryTranslationPreprocessor.Process(Expression query)
   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 EFCore.Abhipra.Admin.GroupRightsMst.getGroupRights(AuthDetails AuDts, String GroupId, String FormId) in /home/abhinav/Drive/Codes/AbhipraGlobalProjBLL/NETCORE/Netcore6/bll/Admin/WebFormManagement/WebFormManagement/GroupRights.cs:line 85

Further technical details

MySQL version: 5.7.34 Operating system: Windows 10 Pomelo.EntityFrameworkCore.MySql version: 6.0.2

[KirillKaverin]

@abhiquiet it is not an issue of the Pomelo. There was a breaking change in the EF Core 3.x about the client query evaluation. Read more: https://learn.microsoft.com/en-us/ef/core/what-is-new/ef-core-3.x/breaking-changes#linq-queries-are-no-longer-evaluated-on-the-client

[lauxjpn]

@abhiquiet Please share the classes (and Fluent API if any) of the following object with us:

• objAdminDB
• ModuleMaster
• objGrpRtsMstData
• Project