IQueryable support for Union

[deadmann]

I have two different query, and I want to union them in one table, but I have pagination and a huge list of data in each table

Code Sample:

public async Task<BaseResponseDto<GetAllCustomerWhiteListRecordsResponseDto_Api>> GetAllCustomerWhiteListRecordsAsync(
        GetAllCustomerWhiteListRecordsRequestDto_Api request, string currentUserNationalCode, CancellationToken cancellationToken = default)
    {

        request.Prepare();
        var skip = (request.Page - 1) * request.PageSize;
        var take = request.PageSize;

        var queryNationalCodeWhiteList = GetQueryAllCustomerNationalCodeWhiteListRecords(currentUserNationalCode)
            .OrderByDescending(o => o.CDate);

        var queryOnlineShoppingBourseInfoItem = GetQueryAllCustomerOnlineShoppingBourseInfoItemRecords(currentUserNationalCode)
            .OrderByDescending(o => o.CDate);

        // Union Over IQueryable is not supported
        var query = queryNationalCodeWhiteList
            .Union(queryOnlineShoppingBourseInfoItem)
            .OrderByDescending(o => o.CDate);

        var totalCount = await query.CountAsync(cancellationToken);

        var data = await query
            .Skip(skip)
            .Take(take)
            .ToListAsync(cancellationToken: cancellationToken);

        var result = new BaseResponseDto<GetAllCustomerWhiteListRecordsResponseDto_Api>();

        result.Data.Items = data;
        result.Data.CalculatePagination(totalCount, request.Page, request.PageSize);

        return result.Succeed();
    }

    private IQueryable<GetAllCustomerWhiteListRecordsResponseDto_Item_Api> GetQueryAllCustomerNationalCodeWhiteListRecords(
        string currentUserNationalCode)
    {
        var queryNationalCodeWhiteList =
            from nationalCodeWhiteList in _nationalCodeWhiteListRepository.GetIQueryableAsNoTracking()
            join circulation in _circulationRepository.GetIQueryableAsNoTracking()
                on nationalCodeWhiteList.CrclRow equals circulation.CrclRow
            join announceCar in _announceCarRepository.GetIQueryableAsNoTracking()
                on circulation.AnnounceCarID equals announceCar.Id
            join car in _carRepository.GetIQueryableAsNoTracking()
                on announceCar.CarRow equals car.CarRow
            join onlineShopping in _onlineShoppingRepository.GetIQueryableAsNoTracking()
                on nationalCodeWhiteList.CrclRow equals onlineShopping.CrclRow
            join branch in _branchRepository.GetIQueryableAsNoTracking()
                on new { BranchNo = onlineShopping.BrncNo }
                equals new { BranchNo = branch.BranchCode == null ? (decimal?)null : decimal.Parse(branch.BranchCode) }
                into branches
            from branch in branches.DefaultIfEmpty()
            join payment in _paymentRepository.GetIQueryableAsNoTracking() // 1 to N
                on onlineShopping.Id equals payment.HostID
                into payments
            join customer in _customerRepository.GetIQueryableAsNoTracking()
                on onlineShopping.IDMember equals customer.Id

            where customer.MeliCode == currentUserNationalCode

            select new GetAllCustomerWhiteListRecordsResponseDto_Item_Api
            {
                Type = "WhiteList",
                CDate = nationalCodeWhiteList.CDate,
                CarTitle = car.Title,
                CirculationTitle = circulation.Title,
                RegisteringDate = onlineShopping.CDate.GetPersianDateTimeWithoutSlash(),
                BranchNumber = onlineShopping.BrncNo,
                BranchTitle = branch.BrncName,
                IsPaidSuccessfully = payments.Any(w=> w.TransactionCode != null && w.Status == "00")
            };
        return queryNationalCodeWhiteList;
    }

    private IQueryable<GetAllCustomerWhiteListRecordsResponseDto_Item_Api> GetQueryAllCustomerOnlineShoppingBourseInfoItemRecords(
        string currentUserNationalCode)
    {
        var queryOnlineShoppingBourseInfoItem =
            from bourseInfoItem in _onlineShoppingBourseInfoItemRepository.GetIQueryableAsNoTracking()
            join bourseInfo in _onlineShoppingBourseInfoRepository.GetIQueryableAsNoTracking()
                on bourseInfoItem.OnlineShopingBourseInfoId equals bourseInfo.Id
            join onlineShopping in _onlineShoppingRepository.GetIQueryableAsNoTracking()
                on bourseInfo.OnlineShopingId equals onlineShopping.Id
            join branch in _branchRepository.GetIQueryableAsNoTracking()
                on new { BranchNo = onlineShopping.BrncNo }
                equals new { BranchNo = branch.BranchCode == null ? (decimal?)null : decimal.Parse(branch.BranchCode) }
                into branches
            from branch in branches.DefaultIfEmpty()
            join payment in _paymentRepository.GetIQueryableAsNoTracking()
                on onlineShopping.Id equals payment.HostID
                into payments
            join circulation in _circulationRepository.GetIQueryableAsNoTracking()
                on onlineShopping.CrclRow equals circulation.CrclRow
            join announceCar in _announceCarRepository.GetIQueryableAsNoTracking()
                on circulation.AnnounceCarID equals announceCar.Id
            join car in _carRepository.GetIQueryableAsNoTracking()
                on announceCar.CarRow equals car.CarRow
            join customer in _customerRepository.GetIQueryableAsNoTracking()
                on onlineShopping.IDMember equals customer.Id

            where customer.MeliCode == currentUserNationalCode

            select new GetAllCustomerWhiteListRecordsResponseDto_Item_Api
            {
                Type = "BourseInfoItem",
                CDate = bourseInfoItem.CDate,
                CarTitle = car.Title,
                CirculationTitle = circulation.Title,
                RegisteringDate = onlineShopping.CDate.GetPersianDateTimeWithoutSlash(),
                BranchNumber = onlineShopping.BrncNo,
                BranchTitle = branch.BrncName,
                IsPaidSuccessfully = payments.Any(w=> w.TransactionCode != null && w.Status == "00")
            };
        return queryOnlineShoppingBourseInfoItem;
    }

Union should support IQuaryable to perform union operation in Database, otherwise we either have to either bring whole data into memory, and even if the Extension method with source of IQueryable supports the Union in database, due to the other side of Union having IEnumerable, you also need to transfer (upload) these data to (not from) database, before getting result of query.

This allows for creating more advance query.

[maumar]

Can you provide a standalone repro that shows the problem? What is the exception that you are getting? In principle, Union on queryable sources is supported, but all depends on the details. What could break the translation is RegisteringDate = onlineShopping.CDate.GetPersianDateTimeWithoutSlash() and perhaps IsPaidSuccessfully = payments.Any(w=> w.TransactionCode != null && w.Status == "00"). Try removing those two, and also use anonymous projection rather than DTO:

select new //GetAllCustomerWhiteListRecordsResponseDto_Item_Api
            {
                Type = "BourseInfoItem",
                CDate = bourseInfoItem.CDate,
                CarTitle = car.Title,
                CirculationTitle = circulation.Title,
                RegisteringDate = onlineShopping.CDate,//.GetPersianDateTimeWithoutSlash(),
                BranchNumber = onlineShopping.BrncNo,
                BranchTitle = branch.BrncName,
//                IsPaidSuccessfully = payments.Any(w=> w.TransactionCode != null && w.Status == "00")
               OnlineShoppingId = onlineShopping.Id
            };

[deadmann]

Oh sorry for not reporting back. the error (exception) I was facing was regarding .NET 6 or EF6, after updating to .NET 7.x and EF 7.x, I could perform group join beside left join and inner join, which seem to be the main issue, not the union itself.

although I still am not sure if it is right to use Union of two query and perform extra Expression operation over them. as One side (first argument (in extension method, this X source)) of the Union supports both IQueryable and IEnumerable, but it doesn't accept the second argument of type IQueryable and just IEnumerable and it make me afraid of the fact that all data from database load into memory (due to reading IEnumerable) and then again send back to the database for Union operation.

Note that I can't test the previous code, as by our technical lead order I separated those two queries into two separate APIs which let me understand the actual underlying issue.

I hope you can clarify this for me.

[maumar]

The fact that second argument to the Union is IEnumerable<TSource> is not a problem. The entire expression will be translated to SQL and second argument will not be evaluated and loaded into memory before. Only data that gets brought back from the database will be the result of a translated UNION query.