I have a two entities, AppUser and InterestTag:

public class AppUser : IdentityUser<int>
{
    public ICollection<InterestTag> InterestTags { get; set; }
}

public class InterestTag
{
    public int Id { get; set; }
    public string Name { get; set; }
    public ICollection<AppUser> AppUsers { get; set; }
}

They are configured in the DataContext with a direct many to many relationship

builder.Entity<AppUser>()
        .HasMany(u => u.InterestTags)
        .WithMany(ut => ut.AppUsers);

I'm trying to make a method that gets all the InterestTags of a user by the user's username, then projects the InterestTag to a InterestTagDto and paginates the results.

The InterestTagDto:

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

The mapping is configured as

CreateMap<InterestTag, InterestTagDto>();

The PagedList class:

public class PagedList<T> : List<T>
{
    public PagedList(IEnumerable<T> items, int count, int pageNumber, int pageSize)
    {
        CurrentPage = pageNumber;
        TotalPages = (int)Math.Ceiling(count / (double)pageSize);
        PageSize = pageSize;
        TotalCount = count;
        AddRange(items);
    }

    public int CurrentPage { get; set; }
    public int TotalPages { get; set; }
    public int PageSize { get; set; }
    public int TotalCount { get; set; }

    public static async Task<PagedList<T>> CreateAsync(IQueryable<T> source, int pageNumber, int pageSize)
    {
        var count = await source.CountAsync();
        var items = await source.Skip((pageNumber - 1) * pageSize).Take(pageSize).ToListAsync();
        return new PagedList<T>(items, count, pageNumber, pageSize);
    }
}

The method I have so far:

    public async Task<PagedList<InterestTagDto>> GetUserInterestTagsAsync(string username)
    {
        var query = (await _context.Users.Include(u => u.InterestTags).SingleOrDefaultAsync(u => u.UserName == username)).InterestTags.AsQueryable();

        return await PagedList<InterestTagDto>.CreateAsync(query.ProjectTo<InterestTagDto>(_mapper.ConfigurationProvider)
        .AsNoTracking(), 1, 20);
    }

On the line

var count = await source.CountAsync();

of PagedList<>, I get an error

The provider for the source 'IQueryable' doesn't implement 'IAsyncQueryProvider'. Only providers that implement 'IAsyncQueryProvider' can be used for Entity Framework asynchronous operations

I think the problem is that after it's mapped to the DTO it's type is a HashSet:

{System.Collections.Generic.HashSet`1[API.Entities.InterestTag].Select(dtoInterestTag => new InterestTagDto() {Id = dtoInterestTag.Id, Name = dtoInterestTag.Name})}

I have a similar function that works which gets a list of AppUsers and maps it to a MemberDto and uses the same PagedList class.

After mapping the type is:

{Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable<API.DTOs.MemberDto>}

Any help is much appreciated. Thanks!

CodePudding user response：

The problem is in your "query" (which is actually not a query but a materialized query result):

var query = (await _context.Users.Include(u => u.InterestTags).SingleOrDefaultAsync(u => u.UserName == username)).InterestTags.AsQueryable();

You are materializing your query with SingleOrDefaultAsync so EF will call the database and map the data to memory using .NET classes and collections making AppUser.InterestTags some collection and build in collections usually do not implement IAsyncQueryProvider which leads to the error you are facing.

There are several possible workaround:

1. Create PagedList.CreateAsync overload which accepts IEnumerable and pass it your query result as ordinary enumerable, not queryable.

2. Rewrite query so it is actually a query. Something like this:

var query = await _context.Users
     .Where(u => u.UserName == username)) // assuming UserName is unique
     .SelectMany(t => t.InterestTags);
// pass to CreateAsync ...

P.S.

Also in general try to prevent overfetching (you need only InterestTags but fetch from the database the rest of user data from User table, though it will not lead to the provided error).