Call stored procedure using pomelo.entityframeworkcore.mysql in .net core

Question

I have DB in MySQL, using common mysql entity framework end up with errors, so I switched to pomelo.entityframeworkcore.mysql.

In my MySQL Db I have few stored procedures. But I do not undertand how to comunicate with it. Usual mysql queries runs perfectly and works fine just like that:

public void ONELINE_SQL_TEST()
{
    var db = new DBContext();
    var test = db.DocumentsInfo.FromSql("SELECT * FROM `DocumentsInfo` LIMIT 0, 2").ToList();         
}

DocumentsInfo is my Model class / one of my mysql table.

But I can't call my stored procedures, keep receiving errors:

USE `testProj`$$
CREATE PROCEDURE `GetDocumentsByName` (DocName varchar(255))
BEGIN
Select * from AllDocuments where DocumentName like DocName;
END$$

DELIMITER ;

and code i've tried:

var docs1 = db.DocumentsInfo.FromSql("GetDocumentByName @DocName = {0}", Filename).ToList(); //not working
var docs2 = db.DocumentsInfo.FromSql("GetDocumentByName @p0", Filename).ToList(); //not working

error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GetDocumentByName 'nameAZ'' at line 1'

Kris.J · Accepted Answer · 2018-10-20 01:23:15Z

1

You need to place CALL before your sql stored procedure from memory :)

i.e.

var docs1 = db.DocumentsInfo.FromSql("CALL GetDocumentByName @p0",    Filename).ToList();

Last I read EF Core didn't support named parameters for stored procs, hence the @p0 So if you had more then 1 parameter and say one them was the path you'd do the following:

var docs1 = db.DocumentsInfo.FromSql("CALL GetDocumentByName @p0, @p1",    parameters: new[] { Path, Filename }).ToList();

edited Oct 20, 2018 at 1:23

answered Oct 18, 2018 at 23:19

Kris.J

3681 silver badge13 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Call stored procedure using pomelo.entityframeworkcore.mysql in .net core

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related