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

Last Reply 8 months ago By Mudassar

Posted 8 months ago

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

Posted 8 months ago

As stated in the Links given by me EF does not support functions which Split string.

Thus only solution is to either normalize database to store the data better so that you can avoid such splitting.

Or you can do splitting in CODE as you are doing now.