How to join two IQueryable list?

Question

I want to get the value by left join the result value of group by each.

        var mgga = mggb.GroupBy(i => i.GoodsCode).Select(i => i.MaxBy(x => x.PriceChangeDate));
        var oega = oegb.GroupBy(i => i.GoodsCode).Select(i => i.MinBy(x => x.EventSalePrice));

And join like this,

        var result = from mgg in mgga
                        join oeg in oega
                        on new { storeCode = mgg.StoreCode, goodsCode = mgg.GoodsCode } equals new { storeCode = oeg.StoreCode, goodsCode = oeg.GoodsCode } into mgoe
                        from oeg in mgoe.DefaultIfEmpty()
                        select new {
                            GoodsCode = mgg.GoodsCode,
                        }; 

But, this show error message.

fail: Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware[1]
      An unhandled exception has occurred while executing the request.
      System.InvalidOperationException: The LINQ expression 'g
          .AsQueryable()
          .Select(e => new {
              ...
           })
          .AsQueryable()
          .MaxBy(x => x.PriceChangeDate)' 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'. 

How do I join mgga or oga with type IQueryable<'a>?

Answer 1

Try the following implementation of your queries. Other part should work.

var mgga = mggb.GroupBy(i => i.GoodsCode)
    .Select(g => g.OrderByDescending(x => x.PriceChangeDate).First());
var oega = oegb.GroupBy(i => i.GoodsCode)
    .Select(g => g.OrderByDescending(x => x.EventSalePrice).First());