public async Task <PaginatedResponse <AuditModel> > GetAll(AuditLogsFilterDto filter, int?skip, int?take) { take = PaginationHelper.GetTake(take); var sqlFilter = filter.AddSqlLikeWildcards(); var whereClause = "WHERE 1=1 " + (!string.IsNullOrWhiteSpace(sqlFilter.UserName) ? " AND LOWER(UserName) like LOWER(@UserName)" : "") + (!string.IsNullOrWhiteSpace(sqlFilter.CorrelationId) ? " AND CorrelationId=@CorrelationId" : "") + (!string.IsNullOrWhiteSpace(sqlFilter.ReferenceId) ? " AND LOWER(DataReference) like LOWER(@ReferenceId)" : "") + (sqlFilter.DataTypes.Any() ? " AND DataType IN @DataTypes" : "") + (sqlFilter.ActionType != null ? " AND Type=@ActionType" : "") + (sqlFilter.StartDateTime != null ? " AND Timestamp >= @StartDateTime" : "") + (sqlFilter.EndDateTime != null ? " AND Timestamp <= @EndDateTime" : ""); var paginationClause = $" ORDER BY [Timestamp] DESC OFFSET {skip ?? 0} ROWS FETCH NEXT {take} ROWS ONLY"; await using var conn = new SqlConnection(_connectionString); var gridReader = await conn.QueryMultipleAsync( $"SELECT {GetColumns} FROM MarginTradingAccountsAuditTrail WITH (NOLOCK) {whereClause} {paginationClause}; SELECT COUNT(*) FROM MarginTradingAccountsAuditTrail {whereClause}", sqlFilter); var contents = (await gridReader.ReadAsync <DbSchema>()) .Select(x => x.ToDomain()) .ToList(); var totalCount = await gridReader.ReadSingleAsync <int>(); return(new PaginatedResponse <AuditModel>( contents, skip ?? 0, contents.Count, totalCount )); }
public Task <PaginatedResponse <IAuditModel> > GetAll(AuditLogsFilterDto filter, int?skip, int?take) { (skip, take) = ValidateSkipAndTake(skip, take); return(_auditRepository.GetAll(filter, skip, take)); }
public async Task <PaginatedResponse <IAuditModel> > GetAll(AuditLogsFilterDto filter, int?skip, int?take) { await using var context = _contextFactory.CreateDataContext(); var query = context.AuditTrail.AsNoTracking(); if (!string.IsNullOrEmpty(filter.UserName)) { query = query.Where(x => x.UserName.ToLower().Contains(filter.UserName.ToLower())); } if (!string.IsNullOrEmpty(filter.CorrelationId)) { query = query.Where(x => x.CorrelationId == filter.CorrelationId); } if (!string.IsNullOrEmpty(filter.ReferenceId)) { query = query.Where(x => x.DataReference.ToLower().Contains(filter.ReferenceId.ToLower())); } if (filter.DataType.HasValue) { query = query.Where(x => x.DataType == filter.DataType.Value); } if (filter.ActionType.HasValue) { query = query.Where(x => x.Type == filter.ActionType.Value); } if (filter.StartDateTime.HasValue) { query = query.Where(x => x.Timestamp >= filter.StartDateTime.Value); } if (filter.EndDateTime.HasValue) { query = query.Where(x => x.Timestamp <= filter.EndDateTime.Value); } var total = await query.CountAsync(); query = query.OrderByDescending(x => x.Timestamp); if (skip.HasValue && take.HasValue) { query = query.Skip(skip.Value).Take(take.Value); } var contents = await query.ToListAsync(); var result = new PaginatedResponse <IAuditModel>( contents: contents, start: skip ?? 0, size: contents.Count, totalSize: total ); return(result); }