Query with Join in BlazorHero

[agorota]

Hi, I would like to create views on the data by making appropriate joins through Linq to Entities, I tried the following path without success, I'll explain what I did by taking for example the 2 example tables in BlazorHero: Brand and Product I would like to create a view in which I highlight whether a brand has products or not as per the following SQL script:

SELECT 
        CASE WHEN products.Name IS NULL THEN 'no product' ELSE 'ok' END AS State, Brands.Name AS Brand, Brands.Id
FROM            
         Products FULL OUTER JOIN  Brands ON Products.BrandId = Brands.Id

This is the result I would like:

In Blazor hero I changed the Products controller to SERVER:

    /// <summary>
    /// Get Status Products
    /// </summary>
    /// <returns>Status 200 OK</returns>
    [Authorize(Policy = Permissions.Products.View)]
    [HttpGet("status")]
    public async Task<IActionResult> GetStatus()
    {
        var products = await _mediator.Send(new GetAllProductsStatusQuery());
        return Ok(products);
    }

Add Class Response in Application:

namespace SigmaAdminBlazorApp.Application.Features.Products.Queries.GetAllPaged { class GetAllStatusProductsResponse { public int Id { get; set; } public string Brand { get; set; } public string State { get; set; } } }

And finally add Query in Application:

namespace SigmaAdminBlazorApp.Application.Features.Products.Queries.GetAllPaged { public class GetAllProductsStatusQuery : IRequest<Result<List>> { public GetAllProductsStatusQuery() {

    }
}

internal class GetAllProductsStatusQueryHandler : IRequestHandler<GetAllProductsStatusQuery, Result<List<GetAllStatusProductsResponse>>>
{
    private readonly IUnitOfWork<int> _unitOfWork;
    private readonly IMapper _mapper;

    public GetAllProductsStatusQueryHandler(IUnitOfWork<int> unitOfWork, IMapper mapper)
    {
        _unitOfWork = unitOfWork;
        _mapper = mapper;
    }

    public async Task<Result<List<GetAllStatusProductsResponse>>> Handle(GetAllProductsStatusQuery request, CancellationToken cancellationToken)
    {
        var dataBrands = _unitOfWork.Repository<Brand>().Entities.ToList();

        var data = await _unitOfWork.Repository<Product>().Entities
                                                                   .Join(dataBrands, Product => Product.BrandId, Brand => Brand.Id,
                                                                   (brand, product) => new GetAllStatusProductsResponse
                                                                   {
                                                                       State = "ok",
                                                                       Brand = brand.Name,
                                                                       Id = brand.Id

                                                                   })
                                                                   .ToListAsync();

        var mappedStatus = _mapper.Map<List<GetAllStatusProductsResponse>>(data);
        return await Result<List<GetAllStatusProductsResponse>>.SuccessAsync(mappedStatus);
    }
}

}

Unfortunately in swagger I get the following answer: 

{ "Data": null, "Messages": [ "The LINQ expression 'DbSet()\r\n .Join(\r\n inner: __p_0, \r\n outerKeySelector: Product => Product.BrandId, \r\n innerKeySelector: Brand => Brand.Id, \r\n resultSelector: (brand, product) => new GetAllStatusProductsResponse{ \r\n State = \"ok\", \r\n Brand = brand.Name, \r\n Id = brand.Id \r\n }\r\n )' 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." ], "Succeeded": false }

I lost a day and a half but without finding a solution, what am I wrong? Please help me ... 
Thanks

[Duelbat]

Yep, good luck. You are really at the mercy of the four winds. Without proper documentation and lack luster support from the developer, you going to pull your hair out. I gave up on this template and now at least I know what is going on in my own code. Write your own stuff and you will be better off in the long run.

[agorota]

@Duelbat it's just your opinion, I'm doing very well. This project is of great help and I believe it will grow further with the help of the community.

[jeremiedevos]

Hey @agorota , I have tried your code and have found the issue! When Googling your issue i came across this page this page they where suggesting at Query (LINQ) expression -Approach2 to use .AsEnumerable.

When adding this to dataBrands it seems to work. I would suggest to read the page. Fix: var dataBrands = _unitOfWork.Repository<Brand>().Entities.AsEnumerable();

[agorota]

Thanks @jeremiedevos It works.

I was almost there but couldn't figure out where to put .AsEnumerable :)

[agorota]

Sorry but I get the problem again using .GroupJoin. But how is it possible ... could you suggest another way to do this?

public async Task<Result<List<GetAllStatusProductsResponse>>> Handle(GetAllProductsStatusQuery request, CancellationToken cancellationToken)
        {
            //var dataBrands = _unitOfWork.Repository<Brand>().Entities.ToList();
            var dataBrands = _unitOfWork.Repository<Brand>().Entities.AsEnumerable();

            var data = await _unitOfWork.Repository<Product>().Entities.Select(d => d)
                                                                       .GroupJoin(dataBrands, Product => Product.BrandId, Brand => Brand.Id,
                                                                       (brand, product) => new GetAllStatusProductsResponse
                                                                       {
                                                                           State = "ok",
                                                                           Brand = brand.Name,
                                                                           Id = brand.Id
                                                                       })
                                                                      .ToListAsync();

            var mappedStatus = _mapper.Map<List<GetAllStatusProductsResponse>>(data);
            return await Result<List<GetAllStatusProductsResponse>>.SuccessAsync(mappedStatus);
        }

Thanks.

[agorota]

I had to reverse the tables to get RIGHT OUTER JOIN in this way:

 public async Task<Result<List<GetAllStatusProductsResponse>>> Handle(GetAllProductsStatusQuery request, CancellationToken cancellationToken)
        {
            //var dataBrands = _unitOfWork.Repository<Brand>().Entities.AsEnumerable();
            var dataProducts = _unitOfWork.Repository<Product>().Entities.AsEnumerable();

            var data = await _unitOfWork.Repository<Brand>().Entities
                                     //.AsEnumerable()
                                     .GroupJoin(dataProducts,
                                     Brand => Brand.Id,
                                     Product => Product.BrandId,
                                     (product, brand) => new { Pr = product, Br = brand })
                                     .SelectMany(
                                        brand => brand.Br.DefaultIfEmpty(),
                                        (Pr, Br) => new GetAllStatusProductsResponse
                                        {
                                            State = Pr.Pr.Description,
                                            Brand = (Br == null ? "No Product" : Br.Name),
                                            Id = Pr.Pr.Id
                                        }
                                     ).ToListAsync();

            var mappedStatus = _mapper.Map<List<GetAllStatusProductsResponse>>(data);
            return await Result<List<GetAllStatusProductsResponse>>.SuccessAsync(mappedStatus);

        }