title: How to send a single request instead of two requests when using sieve in asp core for paging?
body: <p>I write this query with the aim of getting users with paging. I'm using <code>Sieve</code> for paging and filtering and sorting, but I have a problem with this Query :</p>
  
  <p>I intend to send a single request to the database and just return me this data :</p>
  
  <pre><code> 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; }
  
  }
  </code></pre>
  
  <p>With this purpose I have written this:</p>
  
  <pre><code>    var user = Users.AsNoTracking().Select(x =&gt; 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 =&gt; 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&lt;GetAllPaging&lt;UserPagingDto&gt;&gt;.BuildSuccessResult(new GetAllPaging&lt;UserPagingDto&gt;
              {
                  Records = result,
                  TotalCount = await Users.CountAsync()
              });
  </code></pre>
  
  <p>but it sends <strong>two</strong> requests to the database and it is not good. I would like to achieve the same result by sending a single request. </p>
  
  <p>I track the queries to database with profiler.</p>
  
  <p>one query is this:</p>
  
  <pre><code>    SELECT COUNT(*)
      FROM [User] AS [u]
           WHERE [u].[IsDelete] = CAST(0 AS bit)
  </code></pre>
  
  <p>and the second query is this and I intend to send just this query :</p>
  
  <pre><code> 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
  </code></pre>