Efcore: Count with Take or Skip throws InvalidCastException

[Kukkimonsuta]

Query throws InvalidCastException when Take or Skip is used after Select which includes ternary expression with join (in the sample the condition doesn't really make sense since Country doesn't have type hierarchy registered, but effect is the same).

This seems to be contination of https://github.com/aspnet/EntityFrameworkCore/issues/16722

context.Cities
    .Select(c => new
    {
        Name = c.Name,
        Country = c.Country is Country ?
            new
            {
                Name = c.Country.Name,
            } :
            null,
    })
    .Take(20)
    .Count();

Unhandled exception. System.InvalidCastException: Unable to cast object of type 'Microsoft.EntityFrameworkCore.Query.SqlExpressions.SqlFunctionExpression' to type 'System.Linq.Expressions.ConstantExpression'.
   at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.RelationalProjectionBindingRemovingExpressionVisitor.GetProjectionIndex(ProjectionBindingExpression projectionBindingExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.RelationalProjectionBindingRemovingExpressionVisitor.VisitExtension(Expression extensionExpression)
   at System.Linq.Expressions.Expression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at System.Linq.Expressions.ExpressionVisitor.VisitBinary(BinaryExpression node)
   at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.RelationalProjectionBindingRemovingExpressionVisitor.VisitBinary(BinaryExpression binaryExpression)
   at System.Linq.Expressions.BinaryExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at System.Dynamic.Utils.ExpressionVisitorUtils.VisitBlockExpressions(ExpressionVisitor visitor, BlockExpression block)
   at System.Linq.Expressions.ExpressionVisitor.VisitBlock(BlockExpression node)
   at System.Linq.Expressions.BlockExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at System.Linq.Expressions.ExpressionVisitor.VisitLambda[T](Expression`1 node)
   at System.Linq.Expressions.Expression`1.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.VisitShapedQueryExpression(ShapedQueryExpression shapedQueryExpression)
   at Microsoft.EntityFrameworkCore.Query.ShapedQueryCompilingExpressionVisitor.VisitExtension(Expression extensionExpression)
   at System.Linq.Expressions.Expression.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.GetOrAddQueryCore[TFunc](Object cacheKey, Func`1 compiler)
   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 System.Linq.Queryable.Count[TSource](IQueryable`1 source)
   at test_efcore_count.Program.Main(String[] args) in D:\Projects\test-efcore-count\test-efcore-count\Program.cs:line 112
   at test_efcore_count.Program.<Main>(String[] args)

Steps to reproduce

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Logging;

namespace test_efcore_count
{
    class Country
    {
        public int Id { get; set; }
        public string Name { get; set; }

        public ICollection<City> Cities { get; set; }
    }

    class City
    {
        public int Id { get; set; }
        public string Name { get; set; }

        public Country Country { get; set; }
        public int CountryId { get; set; }
    }

    class MapContext : DbContext
    {
        public MapContext(DbContextOptions<MapContext> options)
            : base(options)
        { }

        public DbSet<Country> Countries { get; set; }
        public DbSet<City> Cities { get; set; }
    }

    class Program
    {
        static async Task Main(string[] args)
        {
            var serviceCollection = new ServiceCollection();
            serviceCollection.AddLogging(x => x.AddConsole());
            serviceCollection.AddDbContext<MapContext>(dbContextBuilder =>
            {
                dbContextBuilder.UseSqlServer("Server=.;Initial Catalog=test-efcore-count;Integrated Security=True");
            });

            using var serviceProvider = serviceCollection.BuildServiceProvider();

            Console.WriteLine("Preparing database..");

            using (var scope = serviceProvider.CreateScope())
            using (var context = scope.ServiceProvider.GetRequiredService<MapContext>())
            {
                await context.Database.EnsureDeletedAsync();
                await context.Database.EnsureCreatedAsync();
            }

            Console.WriteLine("Inserting data..");

            using (var scope = serviceProvider.CreateScope())
            using (var context = scope.ServiceProvider.GetRequiredService<MapContext>())
            {
                context.Countries.Add(new Country()
                {
                    Name = "Czech Republic",
                    Cities = new[]
                    {
                        new City() { Name = "Prague" },
                        new City() { Name = "Brno" },
                    }
                });
                context.Countries.Add(new Country()
                {
                    Name = "Spain",
                    Cities = new[]
                    {
                        new City() { Name = "Madrid" },
                        new City() { Name = "Barcelona" },
                    }
                });

                await context.SaveChangesAsync();
            }

            Console.WriteLine("Printing data..");

            using (var scope = serviceProvider.CreateScope())
            using (var context = scope.ServiceProvider.GetRequiredService<MapContext>())
            {
                var query = context.Cities
                    .Select(c => new
                    {
                        Name = c.Name,
                        Country = c.Country is Country ?
                            new
                            {
                                Name = c.Country.Name,
                            } :
                            null,
                    });

                // works
                var list = query.Take(20).ToArray();
                foreach (var item in list)
                {
                    Console.WriteLine($"{item.Name} in {item.Country.Name}");
                }

                // error
                var total = query.Take(20).Count();
                Console.WriteLine($"Database contains {total} cities");
            }
        }
    }
}

Further technical details

EF Core version: 3.0.0
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET Core 3.0
Operating system: Windows 10 1903
IDE: Visual Studio 2019 16.3.4

[smitpatel]

Note for triage: Count after client projection.

[ajcvickers]

Note from triage: this is a client evaluation case where we could throw a better exception in the future.

[Kukkimonsuta]

Could you please elaborate why this query is working with ToArray, but it's not supposed to work with Count? These queries were working in 2.x and it is causing us significant pain when porting to 3.0 since we are using them for paging and discovery of number of possible pages with upper limit.

[maxijuice]

I face a similar issue. But I'm using Sieve on queryable, which pretends not to have any client evaluation. Is there a way to find what is going to be client evaluated in IQueryable?

[TsengSR]

Note from triage: this is a client evaluation case where we could throw a better exception in the future.

Having the same issue. Throws when .Count or .CountAsync is called after projection, i.e. using Automapper

var projectedQuery = await query.ProjectTo<MyViewModel>();
var count = await projectedQuery.CountAsync();
var result = await projectedQuery.ToListAsync();

But only if the projection has navigation properties. Removing the navigation property on the projected entity (MyViewModel) and it works again.

In my case I am having a .ToPagedResultAsync<TEntity>(...) extension method for IQueryable<TEntity> to do all the paging, so I have to do the projection before calling the pagination extension method. Why is it only happens on projections with navigation properties?

My concrete example

public class Company
{
    public Guid Id { get; set; }
    public string CompanyName { get; set; }
    public ICollection<CompanyUser> Users { get; set; }
}

public class CompanyUser
{
    public Guid CompanyId { get; set; }
    public Company Company { get; set; }
    public Guid UserId { get; set; }
    public User User { get; set; }
}

public class User : IdentityUser<Guid>
{
    public string FirstName { get; set; }
    public string LastName { get; set; }
}

public class CompanyViewModel
{
    public Guid Id { get; set; }
    public int LegacyId { get; set; }
    public string WiCustId { get; set; }
    public string CompanyName { get; set; }
    public ICollection<UserViewModel> Users { get; set; } = new List<UserViewModel>();
}

public class UserViewModel
{
    public Guid Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Email { get; set; }
    public bool EmailConfirmed { get; set; }
    public bool LockoutEnabled { get; set; }
    public DateTimeOffset? LockoutEnd { get; set; }
    public string PhoneNumber { get; set; }
    public bool PhoneNumberConfirmed { get; set; }
    public bool TwoFactorEnabled { get; set; }
    public string UserName { get; set; }
}

Query

var companiesQuery = _context.Companies
    .ProjectTo<CompanyViewModel>(mapper.ConfigurationProvider);
// ... apply some filters based on input parameters
var result = await companiesQuery
    .ToPagedResultAsync(perPage, (page - 1) * perPage);

Where ToPagedResultAsync is

public static async Task<PagedResult<TEntity>> ToPagedResultAsync<TEntity>(
    this IQueryable<TEntity> result,
    int limit,
    int skip,
    CancellationToken cancellationToken = default
)
{
    if (result == null)
        throw new ArgumentNullException(nameof(result));

    int totalCount = await result.CountAsync(cancellationToken);

    var resultQuery = result.Skip(skip).Take(limit);
    int count = await resultQuery.CountAsync(cancellationToken);
    var results = await resultQuery.ToListAsync(cancellationToken);

    var pagedResult = new PagedResult<TEntity>(results, totalCount, count, limit, skip);

    return pagedResult;
}

Removing the Users property of CompanyViewModel and it works.

So, why does it fail only when projection is accessing Navigation properties?

[smitpatel]

Accessing navigation properties anywhere in LINQ query requires EF Core to generate a join which changes the shape of the result which eventually leads to exception.

[TsengSR]

@smitpatel

Accessing navigation properties anywhere in LINQ query requires EF Core to generate a join which changes the shape of the result which eventually leads to exception.

And why is it necessary to generate a join, when the query later calls .CountAsync (assuming no navigation properties have been accessed in the .Where conditions)?

Projection is effectively not even needed for count/min/max and the like. In the case above, the query is prepared (with projection) and based on that does two queries: First, which gets the count and the second to return the actual data.

The only other way doing it would be to have two completely separate LINQ Queries, but then one couldn't really use a nice extension method since applying projection after the .ToPagedResultAsync method is just to late to reduce the number of data fetched from DB.

[sgabler-solytic]

Facing the same issue in EF Core 3.1.4. Did you happen to find a workaround @TsengSR?

@ajcvickers wrote:

Note from triage: this is a client evaluation case where we could throw a better exception in the future.

But @Kukkimonsuta had this question about that statement:

Could you please elaborate why this query is working with ToArray, but it's not supposed to work with Count? These queries were working in 2.x and it is causing us significant pain when porting to 3.0 since we are using them for paging and discovery of number of possible pages with upper limit.

If the maintainers find some time to reply, I'd like to understand if this is currently a bug in EF Core, or if it's actually working as intended. Thank you

[SjacoSun]

issue +1 in EF Core 3.1
Throw the same error when using distinct() like this:
projectQuery.Distinct();

[roji]

I've taken another look at this, and it seems to be resolved in EF Core 5.0 - please try using the recently-released 5.0.0-rc2.