Using Stored Procedure with Entity Framework in Linq query in ASP.Net

Last Reply on Nov 14, 2017 11:33 AM By Mudassar

Posted on Nov 13, 2017 06:44 AM

hi,

I am trying to pass parameter to sp in an MVC controller.

The stored procedure has a parameter that return a table from a string like 's1,s2,s3' (i have a Split Function in database).

In Sql works fine but in my project i get model with no entities

when i select the checkboxes that represent the multifilter i have no result

I create a comma separated string when user checks the checkboxes that represent the product lines and then i use that string as parameter in stored procedure.

i have a sp that works like

select * from tab where produtLine In (select value from dbo.Split(COALESCE(@Linea,"xxxx", ",")...etc

the code in c# works but when i try to pass as Line Line1,Line2..etc I recive no result

the code

        public ActionResult Index(String cliente, int ? anno, String [] linea, String tipoDati )
        {
            String Codage = "000";
            tipoDati = "Ordini";
            string lineeSelezionate = null;
            cliente = cliente == "cliente" ? null : cliente;
            if (linea != null)
            {
                foreach (var item in linea)
                {
                    lineeSelezionate +=  item + ",";
                }
                
                    lineeSelezionate = "'" + lineeSelezionate.Substring(0, lineeSelezionate.Length - 1) + "'";            
            }
            IEnumerable<OrdineMeseLineaView> model;
            if (string.IsNullOrEmpty(cliente) && anno == null && linea == null)
            {
                model = new List<OrdineMeseLineaView> { new OrdineMeseLineaView
                                                     {  Linea = "", Gen = 0, Feb = 0, Mar = 0, Apr = 0, Mag = 0, Giu = 0,
                                                        Lug = 0, Ago = 0, Set = 0, Ott = 0, Nov = 0, Dic = 0,Tot=0}
                                                };
            }
            else
            {
                model = db.Database.
                        SqlQuery<OrdineMeseLineaView>("dbo.PivotOrdiniMeseLineaSp @p0, @p1,@p2, @p3",
                                                      Codage, 
                                                      (object)cliente ?? DBNull.Value, 
                                                      (object)anno ?? DBNull.Value, 
                                                      (object)lineeSelezionate ?? DBNull.Value).ToList();
            }                                                       

            return View(model);
        }
You are viewing reply posted by: Mudassar on Nov 14, 2017 07:18 AM.