Unable to apply Take() to a navigation property

[mcchessers-sp]

Hi. I have an aggregate root "Player" with a collection "ActiveDays" via a "PlayerId" foreign key. When I try to apply "Take()" to the navigation query to limit the number of results, like so:

Query.Include(player => player.ActiveDays!.Take(limit));

I get the error System.InvalidOperationException : The LINQ expression 'ROW_NUMBER() OVER(PARTITION BY u0.user_id ORDER BY u0.user_id ASC, u0.day_active ASC)' 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.

(Note that I'm mapping the column "user_id" to "PlayerId" as this is a legacy schema that has some inconsistencies. The ActiveDays table has a composite primary key of user_id and day_active.)

Attempting to add a "AsEnumerable" or similar results in System.InvalidOperationException : The expression 'player.ActiveDays.AsEnumerable().AsQueryable().Take(__limit_1)' is invalid inside an 'Include' operation, since it does not represent a property access: 't => t.MyProperty'. To target navigations declared on derived types, use casting ('t => ((Derived)t).MyProperty') or the 'as' operator ('t => (t as Derived).MyProperty'). Collection navigation access can be filtered by composing Where, OrderBy(Descending), ThenBy(Descending), Skip or Take operations. For more information on including related data, see http://go.microsoft.com/fwlink/?LinkID=746393.

Is there something I am misunderstanding or is this an issue?

(DB is MySQL ver. 5.7.22,)

[ardalis]

The first thing to check is if you can make the query work directly with your dBcontext. Apply your LINQ to dBContext.Players and see if you can get it to work. If not, the problem isn't related to this Specification package.

[fiseni]

Hi @mcchessers-sp,

Unfortunately, it has nothing to do with this library. We're passing the expression as it is. I suspect the MySQL provider doesn't know how to translate the filtered include which contains Take.

You may try to test without specifications, working directly with DbContext.

[fiseni]

Here I created a full test app using SqlServer provider. It works as expected. Try the same code with MySql provider.

using Ardalis.Specification;
using Ardalis.Specification.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore;

await AppDbContext.SeedAsync();

using var dbContext = new AppDbContext();
var repo = new Repository<Customer>(dbContext);
var spec = new CustomerSpec();

var customer = await repo.FirstOrDefaultAsync(spec);

Console.WriteLine($"We fetched {customer!.Addresses.Count} addresses!");

public class CustomerSpec : Specification<Customer>
{
    public CustomerSpec()
    {
        Query.Include(x => x.Addresses.Take(2));
    }
}
public interface IRepository<T> : IRepositoryBase<T> where T : class
{
}
public class Repository<T> : RepositoryBase<T>, IRepository<T> where T : class
{
    public Repository(AppDbContext dbContext) : base(dbContext)
    {
    }
}
public record Address
{
    public int Id { get; set; }
    public int CustomerId { get; set; }
}
public record Customer
{
    public int Id { get; set; }
    public List<Address> Addresses { get; set; } = new();
}
public class AppDbContext : DbContext
{
    public DbSet<Customer> Customers => Set<Customer>();
    public DbSet<Address> Addresses => Set<Address>();
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        var connectionString = "Data Source=(localdb)\\MSSQLLocalDB;Initial Catalog=IncludeTest;Integrated Security=SSPI;ConnectRetryCount=0;";
        optionsBuilder.UseSqlServer(connectionString).LogTo(Console.WriteLine);
    }

    public static async Task SeedAsync()
    {
        using var dbContext = new AppDbContext();
        await dbContext.Database.EnsureDeletedAsync();
        await dbContext.Database.EnsureCreatedAsync();
        var customer = new Customer
        {
            Addresses = new List<Address>()
            {
                new Address(),
                new Address(),
                new Address()
            }
        };
        dbContext.Customers.AddRange(customer);
        await dbContext.SaveChangesAsync();
    }
}

[mcchessers-sp]

Thank you for the suggestions. I tried using DbContext outwith the specification:

DbContext.Players.Where(p => p.Id == (int)TestPlayers[0].Id).Include(p => p.ActiveDays.Take(1));

.. and got the same error, which would suggest that perhaps the driver (Pomelo) may be at fault?

[fiseni]

Yes, you might open an issue there (if there isn't one already).