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

Posted on Nov 14, 2017 09:08 AM Modified on on Nov 14, 2017 09:30 AM

Sorry but I dont understand

I have no error from code !!!

my stored procedure is as

CREATE PROCEDURE [dbo].[PivotOrdiniMeseLineaSp]
     @Codage VARCHAR(3),
     @CodiceCliente VARCHAR(5),
     @Anno smallint,
     @Linea VARCHAR(MAX)
AS
	SELECT ...
        FROM
        (
         SELECT          ...
         FROM            ...
         WHERE (dbo.tab.Codage = @Codage) AND 
             (dbo.tab.Anno = COALESCE(@Anno, dbo.tab.RolAnno) ) AND 
	     (dbo.tab.Codcli = COALESCE(@CodiceCliente, dbo.tab.Codcli) ) AND 	 
	     (CASE ... END IN ( SELECT Value FROM dbo.Split(COALESCE(@Linea,
			 CASE... END), ','))  )

)TAB

PIVOT (
 SUM(Importo) FOR Mese IN ( [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])
) PTab

---split function
CREATE FUNCTION [dbo].[Split]
(
 @List nvarchar(2000),
 @SplitOn nvarchar(5)
) 
RETURNS @RtnValue table 
(
 
 Id int identity(1,1),
 Value nvarchar(100)
) 
AS 
BEGIN
While (Charindex(@SplitOn,@List)>0)
Begin 
Insert Into @RtnValue (value)
Select
 Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1)))
Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))
End 
 Insert Into @RtnValue (Value)
 Select Value = ltrim(rtrim(@List))

 Return
END

 When I pass @Linea parameter i receive no result i do that here

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

 For now i solved passing null to @Linea

then I filter the model with join to selected lines (checkboxes)

model = from i in model
        join x in lineeSelezionate on i.Linea equals x
        select i;

 

model = db.Database.
        SqlQuery<OrdineMeseLineaView>("dbo.PivotOrdiniMeseLineaSp @p0, @p1,@p2, @p3",
                                      Codage,
                                      (object)cliente ?? DBNull.Value,
                                      (object)anno ?? DBNull.Value,
                                      DBNull.Value).ToList();

But i would like a better solution


Posted on Nov 14, 2017 11:33 AM

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.