How to generate complex type according to stored procedure in Mysql EF6 C#

Question

I'm trying to generate complex type by visual studio wizard, but when I try to call stored procedure where is required to pass parameters, wizard doesn't allow me to pass them.

this is a my first problem, but it is not all, I changed my procedure, removed from them passing parameters to call directly and I'm getting following:

So it doesn't generate fields of complex type. How can I solve this problem?

Answer 1

I can answer your first question. I was facing the same in one of my recent projects. Here is how I solved it.

Create a view in MySQL containing the same column name and column type which you are returning from the stored procedure. Check my example below.

Stored procedure: spGetChartData

BEGIN
    SELECT A.ScAnSection, COALESCE(A.ScAnAnswer1, -1) AS ScAnAnswer1, A.ScAnBaselineFinal
    FROM schooolanswer A
    WHERE A.ScAnAcademicYear = 2018
    AND A.ScAnSchoolId = 10;
END

View: vwGetChartData

select 'ScAnSection' AS `ScAnSection`,1 AS `ScAnAnswer1`,1 AS `ScAnBaselineFinal`

Now import both, your stored procedure and view in entity framework. Then from Model Browser, edit your stored procedure and select "Entities" in "Returns a collection of" section. From dropdown, select the view corresponding to your stored procedure.

Hope this helps you solve the problem