Some part of your SQL statement is nested too deeply

Question

I have the following code

[WebGet]
        public Bid GetHighestBidInOpenAuctions(int auctionEventId)
        {
            var auctionEvent = CurrentDataSource.AuctionEvents.Where(x => x.Id == auctionEventId).FirstOrDefault();
            var auctionIds = CurrentDataSource.Auctions.Where(x => x.AuctionEventId == auctionEventId && x.Ends > DateTime.UtcNow).Select(x => x.Id).ToList();

            var bids = CurrentDataSource.Bids.Where(x => auctionIds.Any(t => t == x.AuctionId));

            // If the auction Event has not yet started or there are no bids then show auction with high pre-sale estimate.
            if (bids.Count() == 0 || auctionEvent.Starts > DateTime.UtcNow)
            {
                return null;
            }

            var highestBid = bids.Where(b => b.IsAutobid == false).OrderByDescending(b => b.Amount).FirstOrDefault();

            return highestBid;
        }

This line throws the below exception

if (bids.Count() == 0 || auctionEvent.Starts > DateTime.UtcNow)

Some part of your SQL statement is nested too deeply. Rewrite the query or break it up into smaller queries.

What's wrong?

EDIT

I have tried doing this

IQueryable<Bid> bids = CurrentDataSource.Bids.Where(b => 0 == 1);
            foreach(var auctionId in auctionIds)
            {
                int id = auctionId;
                bids = bids.Union(CurrentDataSource.Bids.Where(b => b.AuctionId == id));
            }

But I still get the same error.

Answer 1

Rather than using a subquery, try replacing the bid query with:

var bids = CurrentDataSource.Bids.Where(b => b.AuctionEventId == auctionEventId
                && b.Auction.AuctionEvent.Starts > DateTime.UtcNow
                && b.Auction.Ends > DateTime.UtcNow);

if (bids.Count() == 0
{
    return null;
}