How to call Procedure using Asp.net MVC with Entity Framework

Question

In an application Asp.net mvc, using ADO.NET with Entity framework and mysql, I have a procedure, that returns a listing.

CREATE PROCEDURE SP_CATEGORIAS()
BEGIN
    select * 
      from (select a.id,
                   @rownum:=@rownum+1 as rownum,
                   case when ifnull(a.categoria_id,0) = 0
                        then a.id
                        else a.categoria_id
                   end as grupo,
                   ifnull((select b.categoria_id
                             from categoria b, (select @rownum:=0) r
                            where b.id = a.categoria_id
                              and @rownum = 1), 0) || a.categoria_id || a.id as ordem,
                    a.nome
               from categoria a) as x
      order by grupo, rownum;
END

How do I call this application in my Asp.net code?

Answer 1

first you have a model that represents the data returned by the previous:

public class Categoria
{
    [Column(Name = "id", Order = 1)]
    public int ID { get; set; }

    [Column(Name = "rownum", Order = 2)]
    public int RowNumber { get; set; }

    [Column(Name = "grupo", Order = 3)]
    public int Grupo { get; set; }

    [Column(Name = "ordem", Order = 4)]
    public int Ordem { get; set; }

    [Column(Name = "nome", Order = 5)]
    public string Nome { get; set; }
}

Then you can come call your trial as follows:

using(var context = new DataBaseContext())
{
    var resultado = context.Database.SqlQuery<Categoria>("SP_CATEGORIAS").ToList();
}

Note

Good thing that this stored Procedure is from Mysql, if it were in SQL Server would suggest removing thesp_ prefix from it.