Skip and Take not working with MySQL EntityFrameworkCore

Question

I am trying to paginate Products from MySQL db, but if I use Skip() or Take() it returns an empty Json array as my web api response like this

[]

But extension methods such as FirstOrDefault(), Where() ... works fine. Here's the code snippet:

public IActionResult GetPage(int page, int pageSize = 2)
{            
    int productCount = _context.Products.Count(); // 5
    float totalPages = (float)Math.Ceiling((float)productCount / pageSize); //2.5 -- round to 3

    if (page < 1 || page > totalPages) return NotFound();
    var products = _context.Products.Skip((page - 1) * pageSize).Take(pageSize); //skip & take err mysql ef

    return Ok(products);
}

I even hardcoded the query .Skip(1).Take(2) with no luck. Anyone have faced this problem or know a workaround?

Answer 1

It turned out to be a bug in MySql.Data EF connector provided by Oracle, bug details is posted here.

Alternative solution:

I changed to another connector called Pomelo, now Skip and Take works perfectly fine. You can search nuget for Pomelo.EntityFrameworkCore.MySql and install appropriate version for your project.

To use, simply change .UseMySQL to .UseMySql when configuring DbContext, as oracle connector use SQL and pomelo use Sql only casing is different.

services.AddDbContext<ApplicationDbContext>(options =>
    options.UseMySql(Configuration.GetConnectionString("DefaultConnection")));

Answer 2

Well I knwon the answer is Old ... but ... in EF mysql you need do pass one order by or order by desc before the skip.

Real solution, not alternative:

like this:

var yourVar = dbContext.LinkText
                       .Where(x => x.active)
                       .OrderByDescending(x => x.startDate)
                       .Skip(50)
                       .Take(10);

You can use with any logic on skip and take like this :

query
.OrderByDescending(x => x.startDate)
.Skip(page <= 1 ? 0 : (page - 1) * (qty == 0 ? 10 : qty))
.Take(qty == 0 ? 10 : qty);

and then the mySQL will recieve the code:

*...the query...*     
ORDER BY  `Extent1`.`startDate` DESC 
     LIMIT 0,10