跳过不使用MySQL EntityFrameworkCore [英] Skip and Take not working with MySQL EntityFrameworkCore

问题描述

我正在尝试从MySQL数据库分页Products，但是如果我使用Skip()或Take()，它将返回一个空的Json数组作为我的Web api响应

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

[]

但是诸如FirstOrDefault()，Where()等扩展方法可以正常工作.这是代码段:

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);
}

我什至没有运气对查询.Skip(1).Take(2)进行硬编码.任何人都遇到过此问题或知道解决方法吗?

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

推荐答案

原来是Oracle提供的MySql.Data EF连接器中的错误，错误详细信息已发布

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

替代解决方案:

我改用另一个名为柚子的连接器，现在是Skip和Take工作完美.您可以在nuget中搜索Pomelo.EntityFrameworkCore.MySql，并为您的项目安装适当的版本.

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.

要使用，只需在配置DbContext时将.UseMySQL更改为.UseMySql，因为oracle连接器使用SQL而pomelo使用Sql只是大小写不同.

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")));

这篇关于跳过不使用MySQL EntityFrameworkCore的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！