Skip and not work with MySQL EntityFrameworkCore

Question

I'm trying to split Products pages into MySQL db, but if I use Skip() or Take() , it returns an empty Json array as my response on api, like this

 [] 

But extension methods like FirstOrDefault() , Where() ... work 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 programmed the .Skip(1).Take(2) request without any luck. Has anyone encountered this problem or knew a workaround?

Answer 1

This turned out to be an error in the MySql.Data EF connector provided by Oracle, details of the error are published here .

Alternative solution:

I switched to another connector called Pomelo , now Skip and Take working fine. You can search nuget for Pomelo.EntityFrameworkCore.MySql and set the appropriate version for your project.

To use, just change .UseMySQL to .UseMySQL when setting up DbContext , since use SQL as the oracle connector and pomelo use SQL only the case is different.

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

Answer 2

Well, I know the answer is old ... but ... in EF mysql you need to go through one order or order in descending order before skipping.

The real solution, not an alternative:

like this:

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

You can use any logic when skipping and do this:

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

and then mySQL will get the code:

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