C#•3y ago

❔ which of these queries is "better"? And will the second one work?

I'm trying to get the total results of the query before pagination to display it to the client. But when googling, some posts on stackoverflow said its impoossible without two separate queries. But it seems the second option is just one query? Or am i missing something in my code?

      
 var filteredListings = await _dbContext.Listings
                    .Where(listing => listing.GetAvailableAccomodations(request.Guests).Any())
                    .WithinRadius(request.Longitude, request.Latitude, FilterUtilities.LocationRadius)
                    .Where(listing => listing.ListingQualities.Any(quality => request.FilterIds.Contains(quality.QualityId)))
                    .Paginate(request.Page, FilterUtilities.PageSize)
                    .Include(x => x.ListingQualities)
                    .ThenInclude(x => x.Quality)
                    .ToListAsync();

var totalSearchResults = await _dbContext.Listings
                    .Where(listing => listing.GetAvailableAccomodations(request.Guests).Any())
                    .WithinRadius(request.Longitude, request.Latitude, FilterUtilities.LocationRadius)
                    .Where(listing => listing.ListingQualities.Any(quality => request.FilterIds.Contains(quality.QualityId))).CountAsync();

      
 var filteredListings = await _dbContext.Listings
                    .Where(listing => listing.GetAvailableAccomodations(request.Guests).Any())
                    .WithinRadius(request.Longitude, request.Latitude, FilterUtilities.LocationRadius)
                    .Where(listing => listing.ListingQualities.Any(quality => request.FilterIds.Contains(quality.QualityId)))
                    .Paginate(request.Page, FilterUtilities.PageSize)
                    .Include(x => x.ListingQualities)
                    .ThenInclude(x => x.Quality)
                    .ToListAsync();

var totalSearchResults = await _dbContext.Listings
                    .Where(listing => listing.GetAvailableAccomodations(request.Guests).Any())
                    .WithinRadius(request.Longitude, request.Latitude, FilterUtilities.LocationRadius)
                    .Where(listing => listing.ListingQualities.Any(quality => request.FilterIds.Contains(quality.QualityId))).CountAsync();

      
 var filteredListings = await _dbContext.Listings
                    .Where(listing => listing.GetAvailableAccomodations(request.Guests).Any())
                    .WithinRadius(request.Longitude, request.Latitude, FilterUtilities.LocationRadius)
                    .Where(listing => listing.ListingQualities.Any(quality => request.FilterIds.Contains(quality.QualityId)))
                    .Paginate(request.Page, FilterUtilities.PageSize)
                    .Include(x => x.ListingQualities)
                    .ThenInclude(x => x.Quality)
                    .ToListAsync();

var totalSearchResults = await _dbContext.Listings
                    .Where(listing => listing.GetAvailableAccomodations(request.Guests).Any())
                    .WithinRadius(request.Longitude, request.Latitude, FilterUtilities.LocationRadius)
                    .Where(listing => listing.ListingQualities.Any(quality => request.FilterIds.Contains(quality.QualityId))).CountAsync();

      
 var filteredListings = await _dbContext.Listings
                    .Where(listing => listing.GetAvailableAccomodations(request.Guests).Any())
                    .WithinRadius(request.Longitude, request.Latitude, FilterUtilities.LocationRadius)
                    .Where(listing => listing.ListingQualities.Any(quality => request.FilterIds.Contains(quality.QualityId)))
                    .Paginate(request.Page, FilterUtilities.PageSize)
                    .Include(x => x.ListingQualities)
                    .ThenInclude(x => x.Quality)
                    .ToListAsync();

var totalSearchResults = await _dbContext.Listings
                    .Where(listing => listing.GetAvailableAccomodations(request.Guests).Any())
                    .WithinRadius(request.Longitude, request.Latitude, FilterUtilities.LocationRadius)
                    .Where(listing => listing.ListingQualities.Any(quality => request.FilterIds.Contains(quality.QualityId))).CountAsync();

And this

 var filteredListingsQuery = _dbContext.Listings
                        .GetListingsWithAvailableAccomodations(request.Guests, dateRange)
                        .WithinRadius(request.Longitude, request.Latitude, FilterUtilities.LocationRadius)
                        .Where(listing => listing.ListingQualities.Any(quality => request.FilterIds.Contains(quality.QualityId)));

var totalSearchResults = await filteredListingsQuery.CountAsync();

var filteredListings = await filteredListingsQuery.Paginate(request.Page, FilterUtilities.PageSize)
              .Include(x => x.ListingQualities)
                .ThenInclude(x => x.Quality)
               .ToListAsync();

 var filteredListingsQuery = _dbContext.Listings
                        .GetListingsWithAvailableAccomodations(request.Guests, dateRange)
                        .WithinRadius(request.Longitude, request.Latitude, FilterUtilities.LocationRadius)
                        .Where(listing => listing.ListingQualities.Any(quality => request.FilterIds.Contains(quality.QualityId)));

var totalSearchResults = await filteredListingsQuery.CountAsync();

var filteredListings = await filteredListingsQuery.Paginate(request.Page, FilterUtilities.PageSize)
              .Include(x => x.ListingQualities)
                .ThenInclude(x => x.Quality)
               .ToListAsync();

 var filteredListingsQuery = _dbContext.Listings
                        .GetListingsWithAvailableAccomodations(request.Guests, dateRange)
                        .WithinRadius(request.Longitude, request.Latitude, FilterUtilities.LocationRadius)
                        .Where(listing => listing.ListingQualities.Any(quality => request.FilterIds.Contains(quality.QualityId)));

var totalSearchResults = await filteredListingsQuery.CountAsync();

var filteredListings = await filteredListingsQuery.Paginate(request.Page, FilterUtilities.PageSize)
              .Include(x => x.ListingQualities)
                .ThenInclude(x => x.Quality)
               .ToListAsync();

 var filteredListingsQuery = _dbContext.Listings
                        .GetListingsWithAvailableAccomodations(request.Guests, dateRange)
                        .WithinRadius(request.Longitude, request.Latitude, FilterUtilities.LocationRadius)
                        .Where(listing => listing.ListingQualities.Any(quality => request.FilterIds.Contains(quality.QualityId)));

var totalSearchResults = await filteredListingsQuery.CountAsync();

var filteredListings = await filteredListingsQuery.Paginate(request.Page, FilterUtilities.PageSize)
              .Include(x => x.ListingQualities)
                .ThenInclude(x => x.Quality)
               .ToListAsync();

Angius•3/14/23, 9:16 PM

The second option is also two queries

Angius•3/14/23, 9:16 PM

Both .CountAsync().CountAsync() and .ToListAsync().ToListAsync() resolve the query

Angius•3/14/23, 9:17 PM

As for which is better... might sound picky but neither, since both use .Include().Include() instead of a .Select().Select()

Angius•3/14/23, 9:17 PM

With a select you might actually even be able to get the count and the results in the same query

linqisniceOP•3/15/23, 10:55 AM

I'm not sure I get it. Im supposed to eager load some of these related entities for the final query. I need them both the listing and its qualities

Angius•3/15/23, 8:17 PM

Yeah, you can do that with .Select().Select()

Angius•3/15/23, 8:17 PM

That also loads eagerly

Angius•3/15/23, 8:18 PM

Unless you're loading entities to update their related entities, don't use .Include().Include(), always .Select().Select() into a DTO

Accord•3/16/23, 8:42 PM

Was this issue resolved? If so, run

/close

/close

otherwise I will mark this as stale and this post will be archived until there is new activity.

❔ which of these queries is "better"? And will the second one work?

Similar Threads

Similar Threads

Similar Threads