Entity Framework effiiciency

Question

I'm trying to get a list of courses for given page index and pagesize. This is my action method:

public async Task<ActionResult<object>> Get(int? page, int? pageSize)
{
    page = page == null || page < 0 ? 0 : page;
    pageSize = pageSize == null || pageSize < 1 ? 5 : pageSize;
    var skipFrom = (int)(page * pageSize);

    var allCourses = from s in _Context.Courses
                     select s;

    var courseList = await allCourses
                           .OrderBy(t => t.Name)
                           .Skip(skipFrom)
                           .Take((int)pageSize)
                           .Select(c => new
                                        {
                                             c.Id,
                                             c.Name,
                                             c.CourseCode,
                                        })
                           .AsNoTracking()
                           .ToListAsync();

    var result = new
        {
            courses = courseList,
            count = allCourses.Count()
        };

    return result;
}

In this I'm first fetching all the courses and filtering them and only fetching the columns I need. Then I'm returning the list and the total count.

Is this an efficient way to do this? Otherwise I have to write 2 queries to fetch the total count and the filtered result. If there is any better approach please let me know. Thanks.

Answer 1

I'm first fetching all the courses and filtering them and only fetching the columns I need.

No you're not. This statement:

var allCourses = from s in _Context.Courses
                     select s;

creates a Query (IQueryable), not a collection. EF won't translate the Query to a SQL Query and send that to the database until you force the query to be enumerated. Here that happens when you run .ToListAsync().

Is this an efficient way to do this?

So long as there's a unique index on Name in the database, this is reasonably efficient.