The LINQ expression 'InternalDbSet.Count()' could not be translated

[monstress2]

Hi. Sub query throw error:

System.InvalidOperationException
  HResult=0x80131509
  Message=The LINQ expression 'InternalDbSet<Root> {  }
    .Count( => .Id == NavigationTreeExpression
        Value: EntityReference: Root
        Expression: r.Id)' 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.
  Source=Microsoft.EntityFrameworkCore
  StackTrace:
   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 System.Linq.Expressions.ExpressionVisitor.VisitBinary(BinaryExpression node)
   at System.Linq.Expressions.BinaryExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.ExpandNavigationsForSource(NavigationExpansionExpression source, Expression expression)
   at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.ProcessLambdaExpression(NavigationExpansionExpression source, LambdaExpression lambdaExpression)
   at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.ProcessWhere(NavigationExpansionExpression source, LambdaExpression predicate)
   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.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)
...

using Microsoft.EntityFrameworkCore;
using System.Linq.Dynamic.Core;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Logging;

namespace DynamicLinqEfCoreExample
{
    internal class Program
    {
        static void Main(string[] args)
        {
            var c = new ParsingConfig() { UseParameterizedNamesInDynamicQuery = true };

            var options = new DbContextOptionsBuilder<ExampleContext>().UseInMemoryDatabase(databaseName: "Example").Options;

            using (var context = new ExampleContext(options))
            {
                var q = context.Roots;
                var qCompiled1 = context.Roots.Where(x => q.Count(t => t.Id == x.Id) > 0);
                var qDynBad = q.Where(c, "@0.Count(it.Id == root.Id) > 0", q);
                qCompiled1.ToArray();
                qDynBad.ToArray(); //throw exception
            }

        }


        public class Root
        {
            public int Id { get; set; }
        }


        public class ExampleContext : DbContext
        {
            public ExampleContext() : base()
            {
            }

            public ExampleContext(DbContextOptions<ExampleContext> options)
                : base(options)
            {
            }

            public DbSet<Root> Roots { get; set; }

        }
    }
}

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>net6.0</TargetFramework>
    <ImplicitUsings>enable</ImplicitUsings>
    <Nullable>enable</Nullable>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="Microsoft.EntityFrameworkCore" Version="7.0.12" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.InMemory" Version="7.0.12" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite" Version="7.0.12" />
    <PackageReference Include="Microsoft.Extensions.Logging" Version="7.0.0" />
    <PackageReference Include="Microsoft.Extensions.Logging.Console" Version="7.0.0" />
    <PackageReference Include="Microsoft.Extensions.Logging.Debug" Version="7.0.0" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.DynamicLinq" Version="7.3.5" />
  </ItemGroup>

</Project>

[monstress2]

if used "WrappedConstant" technique, there no exception.

var q = context.Roots;
var qW = ExpressionHelper.WrappedConstant(q);
var qDynGood = q.Where(c, "@0.Count(it.Id == root.Id) > 0", qW);
qDynGood.ToArray();

[StefH]

@monstress2 I think your code fails because root is a reserved word in System.Linq.Dynamic.Core

Can you try naming your entity differently.

Or set AreContextKeywordsEnabled to false: https://dynamic-linq.net/advanced-configuration#arecontextkeywordsenabled

[monstress2]

@monstress2 I think your code fails because root is a reserved word in System.Linq.Dynamic.Core

For better understanding, I rewrite code (renames vars):

                var qRoot = context.Some1;
                var qSub = context.Some1; //here may be any other query (DBSet)

                //qRoot.Where(x => qSub.Count(t => t.Id == x.Id) > 0)
                var qRes = qRoot.Where(_, "@0.Count(it.Id == root.Id) > 0", qSub);
                qRes.ToArray(); //throw exception

I guess qRoot.Where(_, "@0.Count(it.Id == root.Id) > 0", qSub) is equal qRoot.Where(x => qSub.Count(t => t.Id == x.Id) > 0). Am I wrong?

I use the word root to get ID the top query record (qRoot). In this example I could use parent instead of root. But it's the same result. And than I want to compare it with ID subquery(@0 === qSub) record:

:small_orange_diamond: qRoot.Where("@0 :small_blue_diamond:.Where(:small_orange_diamond: root.ID == it.ID :small_blue_diamond: ).Count() > 0", qSub :small_blue_diamond:);

As I already wrote above. I have a solution to the problem. I use a wrapper for the subquery parameter. But I think you might want to improve your library. You can close the issue if this is not a bug.

Thanks you anyway.