Linq to fetch all records if PageSize is 0

Question

I have a Linq Query where I added pagination the default pageNumber value is 0 but I want to fetch all the records if no pageSize is passed.

await (from a in ctx.Appeals
join img in ctx.AppealImages on a.Id equals img.AppealId into appealImgGroup
from _img in appealImgGroup.DefaultIfEmpty()
where
(a.Type == (int)AppealType.Appeal &&
(string.IsNullOrEmpty(search) || a.Name.Contains(search)))
select new Appeal
{
    Id = a.Id,
    Name = a.Name,
    ImagePath = _img.IsDefault == 1 ? _img.FilePath : null,
    TargetAmount = a.AmountToBeRaised.ToString("C", CultureInfo.CreateSpecificCulture(Configuration.UICulture)),
}).Skip(pageNumber).Take(pageSize).ToListAsync();

Remove the `.Take(pageSize).ToListAsync();` bit. Then only apply `Take` if `pageSize` is > 0. Then apply `ToListAsync`. — mjwills, Jun 21 '21 at 04:49
Also keep in mind that without `OrderBy` the order of the data returned is in no way guaranteed (i.e. quasi-random). Which, given you are using `Skip`, is likely not what you want to happen. — mjwills, Jun 21 '21 at 04:50
Please, check below on Linq skip() and Take() usage. https://stackoverflow.com/questions/2380413/paging-with-linq-for-objects/2380427 — Thomson Mixab, Jun 21 '21 at 05:04

score 3 · Answer 1 · answered Jun 21 '21 at 05:00

Like mjwills said, break your code up into parts:

var baseQuery = (from a in ctx.Appeals
join img in ctx.AppealImages on a.Id equals img.AppealId into appealImgGroup
from _img in appealImgGroup.DefaultIfEmpty()
where
(a.Type == (int)AppealType.Appeal &&
(string.IsNullOrEmpty(search) || a.Name.Contains(search)))
select new Appeal
{
    Id = a.Id,
    Name = a.Name,
    ImagePath = _img.IsDefault == 1 ? _img.FilePath : null,
    TargetAmount = a.AmountToBeRaised.ToString("C", CultureInfo.CreateSpecificCulture(Configuration.UICulture)),
}).OrderBy( ... something ...);

if(pageSize > 0)
    baseQuery = baseQuery.Skip(pageNumber).Take(pageSize)

var result = await baseQuery.ToListAsync();

Note; you need to fix up the OrderBy

but then it will fetch all records and then it will do pagination but what I want is it should fetch paginated records — Kovid Purohit, Jun 21 '21 at 06:55

score 2 · Answer 2 · edited Jun 21 '21 at 05:02

2

You can only apply the Take and Skip if the pageSize is passed:

var query = await (from a in ctx.Appeals
join img in ctx.AppealImages on a.Id equals img.AppealId into appealImgGroup
from _img in appealImgGroup.DefaultIfEmpty()
where
(a.Type == (int)AppealType.Appeal &&
(string.IsNullOrEmpty(search) || a.Name.Contains(search)))
select new Appeal
{
    Id = a.Id,
    Name = a.Name,
    ImagePath = _img.IsDefault == 1 ? _img.FilePath : null,
    TargetAmount = a.AmountToBeRaised.ToString("C", CultureInfo.CreateSpecificCulture(Configuration.UICulture)),
});

if (pageSize != null && pageSize > 0) {
  query = query.Skip(pageNumber).Take(pageSize);
}

return query.ToListAsync();

edited Jun 21 '21 at 05:02

41686d6564 stands w. Palestine

19,168
12
41
79

answered Jun 21 '21 at 04:57

CommonMind

178
9

Almost certainly `Skip(pageNumber)` is wrong too. Some form of multiplication with the pageSize will be needed. – mjwills Jun 21 '21 at 05:25
yes!! I did the multiplication part and then the calculated value comes in pageNumber.and about the solution u provided I am aware of it but then it will fetch all the records and then it will do pagination what I want is it should fetch paginated records directly. – Kovid Purohit Jun 21 '21 at 06:57

score 0 · Answer 3 · answered Jun 21 '21 at 20:38

My advise would be to create an extension method of any IQueryable<T>, that fetches page pageNr of size pageSize. Something like this:

public static ICollection<T> FetchPage<T>(this IQueryable<T> source,
    int pageNr, int pageSize)
{
    // TODO: implement
}

and the async version:

public static async Task<ICollection<T>> FetchPageAsync(this IQueryable<T> source,
    int pageNr, int pageSize) {...}

Usage would be:

var query = from a in ctx.Appeals  ...
select new Appeal
{
    ...
});

// Using page size 25, fetch page 5:
ICollection<Appeal> page5 = query.FetchPage(5, 25);

// Using page size 25, fetch page 20 async:
ICollection<Appeal> page5 = await query.FetchPageAsync(20, 25);    

// Fetch all Appeals:
ICollection<Appeal> allAppeals = query.FetchPage(5, 0);

If this is what you want, on with the implementation!

If you are not familiar with extension methods, read Extension methods demystified

The code for the sync and the async version are almost the same, so I'd create a common extension method first:

public static IQueryable<T>> QueryPage(this IQueryable<T> source,
    int pageNr, int pageSize)
{
    // TODO: check input: source not null, pageNr / pageSize not negative, etc

    // zero pageSize: return all:
    if (pageSize == null)
        return source;
    else
        return source.Skip(pageNr * pageSize).Take(pageSize);
}

Now that you've got this, the FechPage is a one-liner:

public static ICollection<T> FetchPage<T>(this IQueryable<T> source,
    int pageNr, int pageSize)
{
    return source.QueryPage(pageNr, pageSize).ToList();
}

public static async Task<ICollection<T>> FetchPageAsync<T>(this IQueryable<T> source,
    int pageNr, int pageSize)
{
    return await source.QueryPage(pageNr, pageSize).ToListAsync();
}

If you want a proper exception if source equals null, consider to call:

return QueryPage(source, pageNr, pageSize);

the this parameter in extension methods can also be used as a traditional parameter.

Linq to fetch all records if PageSize is 0

3 Answers3