CODEWITHSUNDEEP
mysqlasp.net-core.net-core
pad0n
pad0n

Reputation: 317

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

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' enter image description here

Upvotes: 5

Views: 4909

Answers (1)

Kris.J
Kris.J

Reputation: 368

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

Upvotes: 1

Related Questions