How to send a single request instead of two requests when using sieve in asp core for paging?

Question

I write this query with the aim of getting users with paging. I'm using Sieve for paging and filtering and sorting, but I have a problem with this Query :

I intend to send a single request to the database and just return me this data :

 public class UserPagingDto
{
    [Sieve(CanFilter = true, CanSort = true)]
    public Guid Id { get; set; }
    [Sieve(CanFilter = true, CanSort = true)]
    public string Username { get; set; }
    [Sieve(CanFilter = true, CanSort = true)]
    public string DisplayName { get; set; }
    [Sieve(CanFilter = true, CanSort = true)]
    public bool IsActive { get; set; }
    [Sieve(CanFilter = true, CanSort = true)]
    public bool IsLockedEnd { get; set; }
    [Sieve(CanFilter = true, CanSort = true)]
    public bool ConfirmPhoneNumber { get; set; }
    public UserPagingInfo UserInfos { get; set;
    }
}

public class UserPagingInfo
{
    [Sieve(CanFilter = true, CanSort = true)]
    public int AccountFaile { get; set; }
    [Sieve(CanFilter = true, CanSort = true)]
    public bool ConfirmEmail { get; set; }
    [Sieve(CanFilter = true, CanSort = true)]
    public string PhoneNumber { get; set; }
    [Sieve(CanFilter = true, CanSort = true)]
    public DateTimeOffset? LockedEnd { get; set; }
    [Sieve(CanFilter = true, CanSort = true)]
    public string? Email { get; set; }
    [Sieve(CanFilter = true, CanSort = true)]
    public string Name { get; set; }
    [Sieve(CanFilter = true, CanSort = true)]
    public string Family { get; set; }
    [Sieve(CanFilter = true, CanSort = true)]
    public string RoleName { get; set; }
    [Sieve(CanFilter = true, CanSort = true)]
    public Guid RoleId { get; set; }

}

With this purpose I have written this:

    var user = Users.AsNoTracking().Select(x => new
            {
                Id=x.Id,
                Username = x.Username,
                Name = x.Name,
                Family = x.Family,
                DisplayName = $"{x.Name} {x.Family}",
                Email = x.Email,
                PhoneNumber = x.PhoneNumber,
                AccountFaile = x.AccountFaile,
                IsActive = x.IsActive,
                IsLockedEnd = x.IsLockedEnd,
                ConfirmPhoneNumber = x.ConfirmPhoneNumber,
                ConfirmEmail = x.ConfirmEmail,
                LockedEnd = x.LockedEnd,
                Role = x.UserRoles.Role
            }).Select(c => new UserPagingDto
            {
                Id=c.Id,
                ConfirmPhoneNumber = c.ConfirmPhoneNumber,
                DisplayName = c.DisplayName,
                IsActive = c.IsActive,
                IsLockedEnd = c.IsLockedEnd,
                Username = c.Username,
                UserInfos = new UserPagingInfo
                {
                    AccountFaile = c.AccountFaile,
                    ConfirmEmail = c.ConfirmEmail,
                    Email = c.Email,
                    Family = c.Family,
                    LockedEnd = c.LockedEnd,
                    Name = c.Name,
                    PhoneNumber = c.PhoneNumber,
                    RoleId = c.Role.Id,
                    RoleName = c.Role.Name
                }
            });
            var sieveModel = new SieveModel
            {
                PageSize = formQuery.PageSize,
                Filters = formQuery.Filters,
                Page = formQuery.Page,
                Sorts = formQuery.Sorts
            };
            var result = sieveProcessor.Apply(sieveModel, user);
            return OperationResult<GetAllPaging<UserPagingDto>>.BuildSuccessResult(new GetAllPaging<UserPagingDto>
            {
                Records = result,
                TotalCount = await Users.CountAsync()
            });

but it sends two requests to the database and it is not good. I would like to achieve the same result by sending a single request.

I track the queries to database with profiler.

one query is this:

    SELECT COUNT(*)
    FROM [User] AS [u]
         WHERE [u].[IsDelete] = CAST(0 AS bit)

and the second query is this and I intend to send just this query :

 SELECT [u].[Id], [u].[ConfirmPhoneNumber], [u].[Name], [u].[Family], [u].[IsActive], [u].[IsLockedEnd], [u].[Username], [u].[AccountFaile], [u].[ConfirmEmail], [u].[Email], [u].[LockedEnd], [u].[PhoneNumber], [t0].[Id], [t0].[Name]
FROM [User] AS [u]
LEFT JOIN (
    SELECT [u0].[Id], [u0].[IsDelete], [u0].[RoleId], [u0].[UserId]
    FROM [UserRole] AS [u0]
    WHERE [u0].[IsDelete] = CAST(0 AS bit)
) AS [t] ON [u].[Id] = [t].[UserId]
LEFT JOIN (
    SELECT [r].[Id], [r].[Description], [r].[IsDelete], [r].[Name], [r].[SecurityStamp]
    FROM [Role] AS [r]
    WHERE [r].[IsDelete] = CAST(0 AS bit)
) AS [t0] ON [t].[RoleId] = [t0].[Id]
WHERE [u].[IsDelete] = CAST(0 AS bit)
ORDER BY [u].[Id]
OFFSET @__p_0 ROWS FETCH NEXT @__p_1 ROWS ONLY

score 2 · Answer 1 · answered May 01 '20 at 15:25

2

You're confusing queries and collections.

This:

var user = Users.AsNoTracking().Select(x => new
            {
   . . .
                    RoleId = c.Role.Id,
                    RoleName = c.Role.Name
                }
            });

Is not a collection of Objects. It's a query. If you pass the query to a method that runs .Count() and then .ToList() you are running two different queries.

So just run the query once, and store the results in a List. eg:

var userQuery = Users.AsNoTracking().Select(x => new
            {
   . . .
                    RoleId = c.Role.Id,
                    RoleName = c.Role.Name
                }
            });
 var user = userQuery.ToList();

answered May 01 '20 at 15:25

David Browne - Microsoft

80,331
6
39
67

no man . problem is not here . problem is it => the first query send request to database . and `sieve` again send request for paging and . . . . – mr coder May 01 '20 at 15:31
my question is this . how can i handle the query for send just one request to database – mr coder May 01 '20 at 15:32
" how can i handle the query for send just one request to database" You can query the full result for every page, query the full result, and cache it, or you can use a paging implementation that doesn't need to count the number of rows in the full result for each page. – David Browne - Microsoft May 01 '20 at 15:38

score 1 · Answer 2 · answered May 01 '20 at 16:36

Let's think about this a little bit. You have a table, let's assume that there are 100 000 000 records. After filtering and applying your paging settings, your query ends up with a page of records, let's assume there are 100 such items. From the number of items (which is 100 in the example) you cannot deduce the total number (which is 100 000 000 in the example). If we take a look at the fields, none of those are giving any hint whatsoever about the total number of records. Adding a column for the count for all records would be an extremely bad idea.

So, if we remain reasonable, then we acknowledge that the query that you intend to be the only query will not give you any information about the count. To remedy this situation, you will need to have that count stored as a server variable. If you have the count cached in such a manner, then you will be able to use that server variable and reuse it whenever you like instead of hard-computing it upon each load. However, in order to ensure that this works well, you will need to maintain that value:

whenever such a record is inserted, increment the count value on the server
whenever such a record is removed, decrement the count value on the server
whenever such a record is undeleted, increment the count value on the server
of course, if batch insert, delete or undelete happens, then you will need to change the count accordingly
you will also need a heartbeat task to ensure that the server variable that you have is synchronized with the actual count, because records can be created/removed/updated outside your application as well, at least by direct queries being executed on the database

How to send a single request instead of two requests when using sieve in asp core for paging?

2 Answers2