ef core mysql调用存储过程,如何使用mysql使用输入和输出参数在Entity Framework Core中调用存储过程...

I am using ASP.net Core 2.2 with Entity Framework core 2.2.6 and Pomelo.EntityFrameworkCore.MySql 2.2.0 for connectivity with MySQL, I have a stored procedure which takes 3 input parameters and 1 output parameter. I am able to call it in MySQL workbench like

CALL GetTechniciansByTrade('Automobile', 1, 10, @total);

select @total;

Now I want to call this using entity framework core, the code I am currently using is

var outputParameter = new MySqlParameter("@PageCount", MySqlDbType.Int32);

outputParameter.Direction = System.Data.ParameterDirection.Output;

var results = await _context.GetTechnicians.FromSql("Call GetTechniciansByTrade(@MyTrade, @PageIndex, @PageSize, @PageCount OUT)",

new MySqlParameter("@MyTrade", Trade),

new MySqlParameter("@PageIndex", PageIndex),

new MySqlParameter("@PageSize", PageSize),

outputParameter).ToListAsync();

int PageCount = (int)outputParameter.Value;

Exception I am getting currently is

Only ParameterDirection.Input is supported when CommandType is Text (parameter name: @PageCount)

解决方案

I found the solution using @matt-g suggestion based on this Question.

I had to use ADO.net for this as

var technicians = new List();

using (MySqlConnection lconn = new MySqlConnection(_context.Database.GetDbConnection().ConnectionString))

{

lconn.Open();

using (MySqlCommand cmd = new MySqlCommand())

{

cmd.Connection = lconn;

cmd.CommandText = "GetTechniciansByTrade"; // The name of the Stored Proc

cmd.CommandType = CommandType.StoredProcedure; // It is a Stored Proc

cmd.Parameters.AddWithValue("@Trade", Trade);

cmd.Parameters.AddWithValue("@PageIndex", PageIndex);

cmd.Parameters.AddWithValue("@PageSize", PageSize);

cmd.Parameters.AddWithValue("@PageCount", MySqlDbType.Int32);

cmd.Parameters["@PageCount"].Direction = ParameterDirection.Output; // from System.Data

using (var reader = cmd.ExecuteReader())

{

while (reader.Read())

{

technicians.Add(new TechnicianModel()

{

City = reader["City"].ToString(),

ExperienceYears = reader["ExperienceYears"] != null ? Convert.ToInt32(reader["ExperienceYears"]) : 0,

Id = Guid.Parse(reader["Id"].ToString()),

Name = reader["Name"].ToString(),

Qualification = reader["Qualification"].ToString(),

Town = reader["Town"].ToString()

});

}

}

Object obj = cmd.Parameters["@PageCount"].Value;

var lParam = (Int32)obj; // more useful datatype

}

}