MongoDB Pagination and Sorting With JOIN

Adarsh s 0
Viewed 34

Assume I have 2 Type of collections

public class Student
{
  public string Id     {get;set;}
  public int    RollNo {get;set;}
  public string Name   {get;set;}
}
public class TotalMarks
{
  public string Id        {get;set;}
  public string StudentID {get;set;}
  public int    Mark     {get;set;}
}

And a DTO StudentMarksDto

public class StudentMarksDto
{
  public string RollNo {get;set;}
  public string Name {get;set;}
  public int    Mark     {get;set;}
}

The Way i am doing pagination now is as Follows

public async Task<List<StudentMarksDto>> GetPagedAsync(bool isDes, string sortproperty, int pageNo, int itemCount, string searchTearm)
{
    var studentCollection = dbContext.GetMongoDbCollection<Student>();
    var markCollection = dbContext.GetMongoDbCollection<Marks>();

    //Creating and Adding Filter
    var filterBuilder = Builders<Student>.Filter;
    var filter = filterBuilder.Empty;
    if (!string.IsNullOrWhiteSpace(searchTearm))
    {
        var sub = filterBuilder.Where(x => x.Name == searchTearm);
        filter &= sub;
    }

    //Asuming SortProp is not null and matches A FieldName
    var sortBuilder = Builders<Student>.Sort;
    var sort = isDes ? sortBuilder.Descending(sortproperty) : sortBuilder.Ascending(sortproperty);

    pageNo = pageNo < 1 ? 1 : pageNo;
    itemCount = itemCount < 1 ? 1 : itemCount;

    var studentList = await studentCollection.Find(filter).Sort(sort).Skip((pageNo - 1) * itemCount).Limit(itemCount).Project(x => ToDto(x)).ToListAsync();

    // BAD loop
    foreach (var student in studentList)
    {
        student.Mark = await markCollection.Find(x => x.StudentID == student.StudentID).Project(y => y.Mark).FirstOrDefaultAsync();
    }

    return studentList; 
}

Is there any way that I can use Aggregation to avoid the **BAD for loop**, and that enables me to sort based on students marks also?

mongodb-.net-driver entity-framework c#
1 Answers

Yes, you can set up an aggregation pipeline that filters the data, performs the lookup, sorts and applies the paging boundaries.

In order for this to work, you need to define another class that holds the enumeration of marks:

public class StudentWithMarks
{
    public string Id { get; set; }
    public int RollNo { get; set; }
    public string Name { get; set; }

    public IEnumerable<TotalMarks> Marks { get; set; }
}

Then you can define the aggregation pipeline like this:

public async Task<List<StudentMarksDto>> GetPagedAsync(bool isDes, string sortproperty, int pageNo, int itemCount, string searchTearm)
{
    var studentCollection = dbContext.GetMongoDbCollection<Student>();
    var markCollection = dbContext.GetMongoDbCollection<Marks>();

    //Creating and Adding Filter
    var filterBuilder = Builders<Student>.Filter;
    var filter = filterBuilder.Empty;
    if (!string.IsNullOrWhiteSpace(searchTearm))
    {
        var sub = filterBuilder.Where(x => x.Name == searchTearm);
        filter &= sub;
    }

    //Asuming SortProp is not null and matches A FieldName
    var sortBuilder = Builders<StudentMarksDto>.Sort;
    var sort = isDes ? sortBuilder.Descending(sortproperty) : sortBuilder.Ascending(sortproperty);

    pageNo = pageNo < 1 ? 1 : pageNo;
    itemCount = itemCount < 1 ? 1 : itemCount;

    var pipeline = new EmptyPipelineDefinition<Student>()
        .Match(filter)
        .Lookup<Student, Student, TotalMarks, StudentWithMarks>(
            markCollection,
            x => x.Id,
            x => x.StudentID,
            x => x.Marks)
        .Project(x => new StudentMarksDto()
        {
            RollNo = x.RollNo.ToString(), 
            Name = x.Name, 
            Mark = x.Marks.First().Mark,
        })
        .Sort(sort)
        .Skip((pageNo - 1) * itemCount)
        .Limit(itemCount);
    var studentList = await (await studentCollection.AggregateAsync(pipeline)).ToListAsync();
    return studentList; 
}

Please note that in above sample, I have changed the type of the builder for the Sort. Also, you might need to tweak the type names for the lookup.

userNbH20
Related