ProjectToType with Filtered include (EF Core) does not work (does not filter)

[Tolitech]

Imagine the following structure:

Category (ID, Name) Product (ID, Name, CategoryID, Enabled)

And the records in the database (product table): Name, Category, Enabled Product 1, Category A, true Product 2, Category A, true, Product 3, Category A, false, Product 4, Category A, false

When we want to return category A with its enabled products, using ProjectToType always returns all products, while if we remove ProjectToType, the entity framework returns only really enabled products.

var test1 = await _context.Categories.AsNoTracking()
    .Where(x => x.CategoryId == query.CategoryId)
    .Include(x => x.Products!.Where(y => y.Enabled == true))
    .ProjectToType<CategoryQueryResult>()
    .SingleOrDefaultAsync();

var test2 = await _context.Categories.AsNoTracking()
    .Where(x => x.CategoryId == query.CategoryId)
    .Include(x => x.Products!.Where(y => y.Enabled == true))
    // .ProjectToType<CategoryQueryResult>()
    .SingleOrDefaultAsync(); 

[andrerav]

Hi @Tolitech, can you try either using AvoidInlineMapping = true or materializing the results set (for example using ToList()) before the call to ProjectToType()?

[Tolitech]

Hi, @andrerav thanks for the feedback.

I googled a little better and the conclusion I came to is that this is not a Mapster problem.

I did 6 other slightly different tests. None works as I think it should.

Your request doesn't work either:

var config = new TypeAdapterConfig();
config.Default.AvoidInlineMapping(true);

var test5 = await _context.Categories.AsNoTracking()
      .Where(x => x.CategoryId == query.CategoryId)
      .Include(x => x.Products!.Where(y => y.Enabled == true))
      .ProjectToType<Queries.ById.ByIdQueryResult>(config)
      .SingleOrDefaultAsync();

The example below was responsible for my conclusion that it is not a Mapster problem.

var test3 = await _context.Categories.AsNoTracking()
    .Where(x => x.CategoryId == query.CategoryId)
    .Include(x => x.Products!.Where(y => y.Enabled == true))
    // .ProjectToType<Queries.ById.ByIdQueryResult>()
    .Select(x => new Queries.ById.ByIdQueryResult
        {
        CategoryId = x.CategoryId,
        CategoryName = x.CategoryName,
        Enabled = x.Enabled,
        Products = x.Products!.Select(y => new Queries.ById.ByIdQueryResult.ProductQueryResult
                {
            ProductId = y.ProductId,
            Enabled = y.Enabled,
            ProductName = y.ProductName
                })
        })
    .SingleOrDefaultAsync();

The conclusion I come to is that using Projection (.Select) completely ignores Include, including Filtered Include. That is, it doesn't work in Mapster, but it doesn't work with Projection either.

The only test I was able to make it work as I would like was:

var test4 = await _context.Categories.AsNoTracking()
    .Where(x => x.CategoryId == query.CategoryId)
    .Include(x => x.Products!.Where(y => y.Enabled == true))
    // .ProjectToType<Queries.ById.ByIdQueryResult>()
    .Select(x => new Queries.ById.ByIdQueryResult
    {
        CategoryId = x.CategoryId,
        CategoryName = x.CategoryName,
        Enabled = x.Enabled,
        Products = x.Products!.Where(y => y.Enabled == true).Select(y => new Queries.ById.ByIdQueryResult.ProductQueryResult
        {
            ProductId = y.ProductId,
            Enabled = y.Enabled,
            ProductName = y.ProductName
        })
    })
    .SingleOrDefaultAsync();

The result is correct. But I had to inform twice that I would like to get only the enabled products.

Actually it only worked because of the second filter y.Enabled == true, the first y.Enabled == true (filtered include) kept being ignored.

The question now is, can Mapster do anything about it? Or whenever we need to use Filtered include the projection must be done by Select code?

If you need the code on github to do some testing, no problem for me to expose it.

But it really doesn't look like a Mapster bug to me anymore. Unless you can still do something to improve.

Thanks.

[andrerav]

Hi @Tolitech, To me it seems that this is a database/query issue. I don't have any suggestions for improvements from Mapster side of things here I'm afraid. My only suggestion is that you keep in mind what I mentioned above when mapping result sets from databases or other data sources that might yield results incrementally. Feel free to open a new issue if you run into new problems. Happy mapping! :)

[Tolitech]

No problem, @andrerav However, it is not a query issue.

I will do the projection without using Mapster to work.

I've found people with the exact same problem between Filtered Include and Projection.

https://stackoverflow.com/questions/43618096/filtering-on-include-in-ef-core