none
Entity Framework Stored procedure returns no column How to solve RRS feed

  • Question

  • I am using a stored procedure in sql server its returning two columns but when i am import this stored procedure in  entity framework this is returning "Stored procedure  returns no column". 

    Stored procedure Given below. 

    Thanks in Advance.

    USE [UPOR_DE_MAIN]
    GO
    /****** Object:  StoredProcedure [dbo].[usp_s_UDMFieldChaltaNos]    Script Date: 11/22/2015 10:40:41 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO

      
      
          
      -- usp_s_UDMFieldChaltaNos 26,4,2,15,66,16207,'','','','','','',''
       -- usp_s_UDMFieldChaltaNos 26,4,2,'','','','','','','','','',''  
              
    ALTER PROCEDURE [dbo].[usp_s_UDMFieldChaltaNos]                                      
    @DistrictCode int                              
    ,@TalukCode int                              
    ,@TownId int                               
    ,@ZoneId int                              
    ,@SectorId int                              
    ,@BlockId int                           
    ,@HouseNo nvarchar(50)                              
    ,@WardId int                              
    ,@PropertyNumber nvarchar(50)                              
    ,@DivisionNo nvarchar(50)                              
    ,@LocalAreaNoId int                              
    ,@PTSheetId int                              
    ,@CitySurveyNo nvarchar(50)                             
          
              
      --satya--    
      as                                     
    begin                                                    
    declare @par nvarchar(max)                    
    declare @parMain nvarchar(max)                           
        --SubChaltaNo is null    and                                
    set @par= ' where  DistrictCode=' + convert(nvarchar(3),@DistrictCode)                   
    set @parMain= ' where a.DistrictCode=' + convert(nvarchar(3),@DistrictCode)                           
                                           
    set @par=@par + ' and TalukCode=' + convert(nvarchar(3),@TalukCode)                    
    set @parMain=@parMain + ' and a.TalukCode=' + convert(nvarchar(3),@TalukCode)                           
                                          
    set @par=@par + ' and TownId=' + convert(nvarchar(3),@TownId)                  
    set @parMain=@parMain + ' and a.TownId=' + convert(nvarchar(3),@TownId)                                        
                            
    set @par=@par +' and ChaltaId not in (select ChaltaId from UPOR_Property_Main where                           
     DistrictCode='+ convert(nvarchar(3),@DistrictCode) +'  and TalukCode='                        
    + convert(nvarchar(3),@TalukCode) +' and TownId= '+ convert(nvarchar(3),@TownId) +')   '                                    
                                  
    if @ZoneId<>0                                        
    begin                                        
    set @par=@par + ' and ZoneId=' + convert(nvarchar,@ZoneId)                     
    set @parMain=@parMain + ' and ZoneId=' + convert(nvarchar,@ZoneId)                                        
    end                                        
                                            
     if @SectorId<>0                                        
    begin                                        
    set @par=@par + ' and SectorId=' + convert(nvarchar,@SectorId)                    
    set @parMain=@parMain + ' and SectorId=' + convert(nvarchar,@SectorId)                                        
    end                                        
                                            
     if @BlockId<>0                                        
    begin                                        
    set @par=@par + ' and BlockId=' + convert(nvarchar,@BlockId)                   
    set @parMain=@parMain + ' and BlockId=' + convert(nvarchar,@BlockId)                                        
    end                             
                                  
     if @WardId<>0                                        
    begin                                        
    set @par=@par + ' and WardId=' + convert(nvarchar,@WardId)                   
    set @parMain=@parMain + ' and WardId=' + convert(nvarchar,@WardId)                                        
    end                                        
                                            
     if @LocalAreaNoId<>0                                        
    begin                                        
    set @par=@par + ' and LocalAreaNoId=' + convert(nvarchar,@LocalAreaNoId)                    
    set @parMain=@parMain + ' and LocalAreaNoId=' + convert(nvarchar,@LocalAreaNoId)                                        
    end                                
                                     
     if @PTSheetId<>0                                        
    begin                                        
    set @par=@par + ' and PTSheetId=' + convert(nvarchar,@PTSheetId)                    
    set @parMain=@parMain + ' and PTSheetId=' + convert(nvarchar,@PTSheetId)                                        
    end                                
                                  
    if @HouseNo<>''          
    begin                                        
    set @par=@par + ' and HouseNo = ' +  char(39) +  @HouseNo  +   char(39)                   
    set @parMain=@parMain + ' and HouseNo = ' +  char(39) +  @HouseNo  +   char(39)                                        
    end                   
                  
    if @PropertyNumber<>''                                        
    begin                                        
    set @par=@par + ' and PropertyNumber = ' +  char(39) +  @PropertyNumber   +   char(39)                   
    set @parMain=@parMain + ' and PropertyNumber = ' +  char(39) +  @PropertyNumber   +   char(39)                                        
    end                     
                                  
    if @CitySurveyNo<>''                                        
    begin                                        
    set @par=@par + ' and CitySurveyNo = ' +  char(39) +  @CitySurveyNo   +   char(39)                   
    set @parMain=@parMain + ' and CitySurveyNo = ' +  char(39) +  @CitySurveyNo   +   char(39)                                        
    end                       
    set @par=@par + ' and isdeleted is null'                     
    set @parMain=@parMain + ' and Type=''S''                  
    and a.MainChaltaId in (select chaltaid from dbo.UPOR_Property_Main)                  
    and a.MainChaltaId=b.chaltaid                  
    and a.chaltaid not in (select chaltaid from dbo.UPOR_Property_Main) and isDeleted is null'                    
                                
        --(case when AdditionalNo IS Null then convert(varchar,ChaltaNo) else convert(varchar,ChaltaNo) +'+'''/''' +'+ AdditionalNo end )as ChaltaNo                  
              --convert(varchar,isnull(AdditionalNo,'''')) as ChaltaNo               
    declare @query nvarchar(max)                   
    declare @queryMain nvarchar(max)                
                   
                                  
                            
    set @query= 'select  ChaltaId,              
    (Case when Additionalno is null and subchaltano is null then convert(varchar,chaltano)             
    when Additionalno is not null and subchaltano is null then convert(varchar,chaltano) +'+'''/'''+'+ additionalno             
    when Additionalno is null and subchaltano is not null then convert(varchar,chaltano)  +'+'''-'''+'+ convert(varchar,subchaltano)             
    else convert(varchar,chaltano) +'+'''/'''+'+ additionalno +'+'''-'''+'+ convert(varchar,subchaltano) end) as ChaltaNo                   
     from UPOR_ChaltaMain'            
    --      convert(varchar,isnull(AdditionalNo,''''))                   
    --as ChaltaNo             
    set @queryMain=' UNION  select distinct a.chaltaid,                   
    (Case when a.Additionalno is null and a.subchaltano is null then convert(varchar,a.chaltano)             
    when a.Additionalno is not null and a.subchaltano is null then convert(varchar,a.chaltano) +'+'''/'''+'+ a.additionalno             
    when a.Additionalno is null and a.subchaltano is not null then convert(varchar,a.chaltano)  +'+'''-'''+'+ convert(varchar, a.subchaltano)             
    else convert(varchar,a.chaltano)+'+'''/'''+'+ a.additionalno +'+'''-'''+'+ convert(Varchar,a.subchaltano) end) as ChaltaNo                         
    from UPOR_ChaltaMain as a,UPOR_Property_Main as b'                        
                       
    set @queryMain=@queryMain + @parMain                  
    set @query=@query+@par                  
    set @query = @query  + @queryMain                 
                             
    --where                                     
    -- DistrictCode='+ convert(varchar,@census_dist_code) +' and TalukCode='+ convert(varchar,@census_taluk_code) +'                          
    -- and TownId=' + convert(varchar,@TownId) + convert(varchar,@WhrVal)                           
                      
    declare @queryUNION nvarchar(max)                                
    set @queryUNION='select distinct chaltaid,ChaltaNo from ('+@query+') as c '+' where ChaltaId !=0 '                  
    --select distinct chaltaid,ChaltaNo from ('+@query+') as c '+' where ChaltaId !=0 
    print (@queryUNION)                          
    exec (@queryUNION)                    
                 
                            
    end        
      --   
          
      



    Monday, November 23, 2015 5:54 AM

Answers

  • Try to use an OUTPUT parameter for the Result.

    As Herro wong metioned, you'll propably need to declare a DataType, but you can try to use the "System.Data.Entity.Core.Objects.ObjectParameter" as an output parameter and try to make sense of the Data that should be held in it's "Value"-Property (it might just crash though, depends if EF can automatically create types or is clever enough to use a Table or Dictionaries as a return value automatically.

    If you don't wanna use OUTPUT parameters, I strongly suggest to use "SET NOCOUNT ON" at least. Also you could try to use the ModelBrowser in VS to create or map the Return type of the SP. If you are inserting, updateing or deleting data, it's best to not use SP with EF, only if you drop the connection after that command anyways and close the Context (otherwise EF won't be able to track the changes and you would need to reload the cached entities).

    A stored procedure in EF returns an integer, just like the SP in SQL should do as well.

    Edit: It is always better to use an OUTPUT parameter with EntityFramework.
    Tuesday, November 24, 2015 1:42 PM

All replies

  • Hi AamerKhan,

    You could try to define data type which will have properties for all possible columns your query can return. It must be flat type with no nested objects and properties must have same names as columns in result set. You cannot return dynamic columns to EF. In EF, you also should define a new class to receive values returned from SP. Something like the following:

    var data = context.Database.SqlQuery<NewType>("SP");

    I hope it helps.

    Regards,


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, November 24, 2015 2:26 AM
    Moderator
  • Try to use an OUTPUT parameter for the Result.

    As Herro wong metioned, you'll propably need to declare a DataType, but you can try to use the "System.Data.Entity.Core.Objects.ObjectParameter" as an output parameter and try to make sense of the Data that should be held in it's "Value"-Property (it might just crash though, depends if EF can automatically create types or is clever enough to use a Table or Dictionaries as a return value automatically.

    If you don't wanna use OUTPUT parameters, I strongly suggest to use "SET NOCOUNT ON" at least. Also you could try to use the ModelBrowser in VS to create or map the Return type of the SP. If you are inserting, updateing or deleting data, it's best to not use SP with EF, only if you drop the connection after that command anyways and close the Context (otherwise EF won't be able to track the changes and you would need to reload the cached entities).

    A stored procedure in EF returns an integer, just like the SP in SQL should do as well.

    Edit: It is always better to use an OUTPUT parameter with EntityFramework.
    Tuesday, November 24, 2015 1:42 PM
  • I am using a stored procedure in sql server its returning two columns but when i am import this stored procedure in  entity framework this is returning "Stored procedure  returns no column".

    You could just make a custom object with two properties for the columns.

    If push comes to shove, you can just use the EF backdoor to use ADO.NET,  SQL Command objects, a data reader and a List<custom objects>.

    http://blogs.msdn.com/b/alexj/archive/2009/11/07/tip-41-how-to-execute-t-sql-directly-against-the-database.aspx

    Wednesday, November 25, 2015 5:16 PM