c# MySql.Data.EntityFrameworkCore store procedure pagination

user_3903484 0
Viewed 306

for using pagination in complex query before i used sql server (tsql) i can do it very ease using COUNT(*) OVER() as TotalCount in any complex query it will return total count and i can use it in pagination but in mysql it will not work

to connect with mysql with asp.net core there is two packages

  • MySql.Data.EntityFrameworkCore
  • Pomelo.EntityFrameworkCore.MySql

i used both but i don't know how to implement pagination in store procedure and return using FromSqlRaw the issue is return number of rows OUT TotalCount INT that i can't get it

    DELIMITER $$
CREATE PROCEDURE get_articles(IN _offset INT, IN _count INT, OUT _total INT)
BEGIN
SELECT SQL_CALC_FOUND_ROWS * 
FROM content c JOIN content_types t 
ON c.content_type = t.id
WHERE t.name = 'article' 
LIMIT _offset, _count;
SET _total = FOUND_ROWS();
END$$
DELIMITER ;

c# with MySql.Data.EntityFrameworkCore

var result = await dbContext.Result.FromSqlRaw("CALL get_articles (0,30,@TotalCount)",totalCount
).AsNoTracking().ToListAsync();

Here is SQLFiddle demo

ef-core-3.1 asp.net-core mysql c#
1 Answers

Your procedure should accpet first element or page number then you can combine limit keyword in your SQL. For example limit kpagecountpagenumber , kpagecount(pagenumber+1)-1 There is also version which takes limit n offset m

After edit: please include also parameters in your call (C#).

user_57100840
Related