Skip and Take not working with MySQL EntityFrameworkCore

c# entity-framework-core mysql

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?

1
6
1/15/2017 12:33:08 AM

Accepted Answer

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")));
8
1/15/2017 10:06:57 AM

Popular Answer

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


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow