Error report of C # groupby using AsQueryable

Question

var commodity = _appDbContext.ArchivesCCommodity.Where(lambda)
          .GroupJoin(_appDbContext.ArchivesCCommoditySpecification, a => a.Code, b => b.Commodity, (a, b) => new { a, b })
          .SelectMany(a => a.b.DefaultIfEmpty(), (a, b) => new { a.a, b })
          .GroupJoin(_appDbContext.ArchivesCSpecificationDetail, a => a.a.a.b.SpecificationDetail, d => d.Code, (a, d) => new { a, d })
          .SelectMany(a => a.d.DefaultIfEmpty(), (a, d) => new
          {
              Commodity = a.a.a.Code,
              CommodityName = a.a.a.Name,
              SpecificationDetailName = d.Name,
              OrderSN = d.OrderSN
          }).AsQueryable().OrderBy(a => a.OrderSN).GroupBy(a => new { a.Commodity, a.CommodityName })
           .Select(a => new
           {
               Commodity = a.Key.Commodity,
               CommodityName = a.Key.CommodityName,
               SpecificationDetailName = string.Join(" - ", a.Select(a => a.SpecificationDetailName)),
               SpecificationDetailTotal = string.Join(" - ", a.Select(a => a.SpecificationDetailName)) == "" ? 0 : a.Count()
           }); 

Where .AsQueryable() will cause an error

.AsQueryable()
.OrderBy(a => a.OrderSN)
.GroupBy(a => new { a.Commodity, a.CommodityName })

No error will be reported when changing to AsEnumerable()

.ASEnumerable()
.OrderBy(a => a.OrderSN)
.GroupBy(a => new { a.Commodity, a.CommodityName })

But I don't want to send this code to the database for the time being, because it will be sent after paging query. I don't know how to deal with it?

//////////////I pasted my complete code and talked about my actual needs

Query the code and query the database page by page. For example, only one page and 10 rows of records are checked. Here is OK.

            var AA= _appDbContext.ArchivesCCommodity.Where(lambda)
              .GroupJoin(_appDbContext.ArchivesCCommoditySpecification, a => a.Code, b => b.Commodity, (a, b) => new { a, b })
              .SelectMany(a => a.b.DefaultIfEmpty(), (a, b) => new { a.a, b })
              .GroupJoin(_appDbContext.ArchivesCSpecificationDetail, a => a.a.b.SpecificationDetail, d => d.Code, (a, d) => new { a, d })
              .SelectMany(a => a.d.DefaultIfEmpty(), (a, d) => new
              {
                  Commodity = a.a.a.a.a.Code,
                  CommodityName = a.a.a.a.a.Name,
                  SpecificationDetailName = d.Name,
                  OrderSN = d.OrderSN
              });

            PageHealper<object> page = new PageHealper<object>();

            page.Start(pageNum, pageSize);

            page = await page.RestPage(AA);

At this time, I grouped and sorted again, and now I found that:

1. It is not to operate the paging query results, but to query all the AA databases.
2. Based on the previous pagination query, the number of rows and page numbers are obtained. Here, the number of rows is changed by grouping and merging. That's why I want to put grouping and sorting together, and finally pagination.

  var BB = AA.AsEnumerable().OrderBy(a => a.OrderSN).GroupBy(a => new { a.Commodity, a.CommodityName, a.Specification, a.SpecificationName })
               .Select(a => new
               {
                   Commodity = a.Key.Commodity,
                   CommodityName = a.Key.CommodityName,
                   SpecificationDetailName = string.Join(" - ", a.Select(a => a.SpecificationDetailName)),
                   SpecificationDetailTotal = string.Join(" - ", a.Select(a => a.SpecificationDetailName)) == "" ? 0 : a.Count()
               }); ;

            page.Data = BB.ToList<object>();
            return page;

Answer 1

Checkout this article https://weblogs.asp.net/zeeshanhirani/using-asqueryable-with-linq-to-objects-and-linq-to-sql about what AsQueryable does.

I think you dont really need AsQueryable there... LINQ to SQL does not like something about that query.

It does not like the String.Join(...) because it cannot translate it.

So one thing you can do is put .AsEnumerable() after the GroupBy() this will do everything up to in SQL and everything after in memory.

Ex:

var commodity = _appDbContext.ArchivesCCommodity.Where(lambda)
        .GroupJoin(_appDbContext.ArchivesCCommoditySpecification, a => a.Code, b => b.Commodity, (a, b) => new { a, b })
        .SelectMany(a => a.b.DefaultIfEmpty(), (a, b) => new { a.a, b })
        .GroupJoin(_appDbContext.ArchivesCSpecificationDetail, a => a.a.a.b.SpecificationDetail, d => d.Code, (a, d) => new { a, d })
        .SelectMany(a => a.d.DefaultIfEmpty(), (a, d) => new
        {
            Commodity = a.a.a.Code,
            CommodityName = a.a.a.Name,
            SpecificationDetailName = d.Name,
            OrderSN = d.OrderSN
        }).OrderBy(a => a.OrderSN).GroupBy(a => new { a.Commodity, a.CommodityName })
        .AnEnumerable()
        .Select(a => new
        {
            Commodity = a.Key.Commodity,
            CommodityName = a.Key.CommodityName,
            SpecificationDetailName = string.Join(" - ", a.Select(a => a.SpecificationDetailName)),
            SpecificationDetailTotal = string.Join(" - ", a.Select(a => a.SpecificationDetailName)) == "" ? 0 : a.Count()
        });