public async Task <long> GetCountFilteredAsync(AddressFilterCriteria filter) { var min = filter.MinBalance ?? 0; var max = filter.MaxBalance ?? double.MaxValue; var fromHeight = filter.HeightFrom ?? 0; var toHeight = filter.HeightTo ?? int.MaxValue; var sqlQ = $@"SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT COUNT(1) FROM [dbo].[AddressAggregate] aa WHERE aa.[Balance] >= @min AND aa.[Balance] <= @max AND aa.[LastBlockHeight] >= @fromHeight AND aa.[LastBlockHeight] <= @toHeight;"; using (var sqlCon = await DbConnectionFactory.GetNexusDbConnectionAsync()) { var param = new { min, max, fromHeight, toHeight }; return((await sqlCon.QueryAsync <long>(sqlQ, param)).FirstOrDefault()); } }
public async Task <FilterResult <AddressLiteDto> > GetAddressLitesFilteredAsync(AddressFilterCriteria filter, int start, int count, bool countResults, int?maxResults = null) { var min = filter.MinBalance ?? 0; var max = filter.MaxBalance ?? double.MaxValue; var fromHeight = filter.HeightFrom ?? 0; var toHeight = filter.HeightTo ?? int.MaxValue; var isNxs = filter.IsNexus; var isStk = filter.IsStaking; var trustAddresses = await _redisCommand.GetAsync <List <AddressLiteDto> >(Settings.Redis.TrustKeyAddressCache); var nexusAddresses = await _redisCommand.GetAsync <List <AddressLiteDto> >(Settings.Redis.NexusAddressCache); if (isNxs || isStk) { if ((isNxs && (nexusAddresses == null || !nexusAddresses.Any()) || (isStk && (trustAddresses == null || !trustAddresses.Any())))) { return new FilterResult <AddressLiteDto> { ResultCount = 0, Results = new List <AddressLiteDto>() } } ; var adds = (!isNxs || !isStk) ? isNxs ? nexusAddresses : trustAddresses : trustAddresses.Where(x => nexusAddresses.Any(y => y.Hash == x.Hash)); var filtered = adds.Where(x => x.Balance >= min && x.Balance <= max && x.LastBlockSeen >= fromHeight && x.LastBlockSeen <= toHeight); var ordered = OrderAddresses(filtered, filter.OrderBy).ToList(); var result = new FilterResult <AddressLiteDto> { ResultCount = ordered.Count, Results = ordered.Skip(start).Take(count).ToList() }; return(result); } var sqlOrderBy = "ORDER BY"; switch (filter.OrderBy) { case OrderAddressesBy.LowestBalance: sqlOrderBy += " aa.[Balance]"; break; case OrderAddressesBy.MostRecentlyActive: sqlOrderBy += " LastBlockSeen DESC"; break; case OrderAddressesBy.LeastRecentlyActive: sqlOrderBy += " LastBlockSeen"; break; default: sqlOrderBy += " aa.[Balance] DESC"; break; } var sqlQ = $@"SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT a.[AddressId], a.[Hash], a.[FirstBlockHeight] AS FirstBlockSeen, aa.[LastBlockHeight] AS LastBlockSeen, aa.[Balance] FROM [dbo].[Address] a LEFT JOIN [dbo].[AddressAggregate] aa ON aa.[AddressId] = a.[AddressId] WHERE aa.[Balance] >= @min AND aa.[Balance] <= @max AND aa.[LastBlockHeight] >= @fromHeight AND aa.[LastBlockHeight] <= @toHeight {sqlOrderBy} OFFSET @start ROWS FETCH NEXT @count ROWS ONLY;"; var sqlC = @"SELECT COUNT(*) FROM (SELECT TOP (@maxResults) 1 AS Cnt FROM [dbo].[Address] a LEFT JOIN [dbo].[AddressAggregate] aa ON aa.[AddressId] = a.[AddressId] WHERE aa.[Balance] >= @min AND aa.[Balance] <= @max AND aa.[LastBlockHeight] >= @fromHeight AND aa.[LastBlockHeight] <= @toHeight) AS resultCount;"; using (var sqlCon = await DbConnectionFactory.GetNexusDbConnectionAsync()) { var results = new FilterResult <AddressLiteDto>(); var param = new { min, max, fromHeight, toHeight, start, count, maxResults = maxResults ?? int.MaxValue }; if (countResults) { using (var multi = await sqlCon.QueryMultipleAsync(string.Concat(sqlQ, sqlC), param)) { results.Results = (await multi.ReadAsync <AddressLiteDto>()).ToList(); results.ResultCount = (int)(await multi.ReadAsync <int>()).FirstOrDefault(); } } else { results.Results = (await sqlCon.QueryAsync <AddressLiteDto>(sqlQ, param)).ToList(); results.ResultCount = -1; } foreach (var address in results.Results) { address.InterestRate = trustAddresses?.FirstOrDefault(x => x.Hash == address.Hash)?.InterestRate; address.IsNexus = nexusAddresses?.Any(x => x.Hash == address.Hash) ?? false; } return(results); } }