StoredProcedure not returning any data with entityframework

See more:

EF4.0

stored-procedure

I am using ef4.0 I have a complex and big sp that return a data when normally used with enterprise library, but doesn't return any data when using with ef.
here is the SP:

CREATE PROCEDURE [dbo].[usp_SelectLocations_NotEmptyForTreeView]  
@RefrencePage int,                          
@JobId  int,  
@ItemName varchar(max),                    
@IsInRush bit,  
@ItemRoomId int,  
@SearchLocationId int,  
@ItemCategoryId int,  
@ItemProductionStatusId int,  
@ItemStatusId int,  
@ShowAll bit  
  
  
AS  
BEGIN  
SET NOCOUNT ON;  
  
DECLARE @JobItemStatusIdList nvarchar(max),  
@JobItemProductionStatusIdList nvarchar(max),  
@IsStorageLoc bit,  
@IsDepartmentFilterChecked bit  
  
if (@RefrencePage = 6) ---- 6 For Restoration                                        
begin                                        
 if(@ShowAll = 1)  
 set @JobItemProductionStatusIdList = N'1,57,58,62'  
 else  
    begin  
  set @JobItemProductionStatusIdList = N'1,57,62'  
  set @JobItemStatusIdList = N',30'       
 end  
end                                   
else if (@RefrencePage = 3) ---- 3 For Inspection                                        
begin                                        
 if(@ShowAll = 1)                              
    set @JobItemProductionStatusIdList = N'54,58,59,63,69,70'                                        
 else  
 begin  
  set @JobItemProductionStatusIdList = N'54,58,59,63,69'  
  set @JobItemStatusIdList = ''  
  set @IsStorageLoc = 0  
 end   
end                                            
else if (@RefrencePage = 7) --- 7 For Packaging                                      
begin                             
   if(@ShowAll = 1)                              
 begin  
  set @JobItemProductionStatusIdList = N'70,55,84'  
  set @JobItemStatusIdList = N'26,27,28,29,60,67,68,75,15,16,17'  
  set @IsStorageLoc = 0  -- show only not storage locations when "Packaging Required" is on  
  set @IsDepartmentFilterChecked = 1  
 end  
else  
 set @JobItemProductionStatusIdList = N'70,55,84'  
end                                      
else if (@RefrencePage = 4 OR @RefrencePage = 13)  --- 4 For Salvage Review                                      
begin                                       
  set @JobItemProductionStatusIdList = N'75'                                      
  set @JobItemStatusIdList = N'67,26'                                      
end                                      
else if (@RefrencePage = 8)  --- 8 For Waiting For Disposal                                      
begin                                      
   set @JobItemProductionStatusIdList = N'84'                                      
  set @JobItemStatusIdList = N'15,33'                                      
end                                      
else if (@RefrencePage = 11)   ---- 11 For Restoration Admin Approval                                      
begin                                      
   set @JobItemProductionStatusIdList = N'57,62,70,75,84'                                      
   set @JobItemStatusIdList = N'66,65,11'                                
end                  
else if (@RefrencePage =12)  ---- 12 For Inspection Admin Approval                                      
begin                                      
   set @JobItemProductionStatusIdList = N'58,59,63,70,75,84'                                      
   set @JobItemStatusIdList = N'21,19,20,61,25,18'                  
end;  
  
DECLARE @TempL TABLE(LocationId int, ItemId int, ParentLocationId int, Name varchar(max), NameBarcode varchar(max), IsLocation Bit, JobItemStatusId int, JobItemProductionStatusId int, IsRush bit);  
WITH Loc(LocationId, ParentLocationId, LocationName, LocationNameBarcode) AS   
(  
    SELECT DISTINCT Locations.LocationId, Locations.ParentLocationId, Locations.LocationName,   
  ISNULL(Locations.LocationName, '') + ' {' + ISNULL(Locations.LocationBarcode, '') + '}'  
    FROM Locations INNER JOIN JobItems ON Locations.LocationId = JobItems.LocationId  
       
WHERE  
charindex(','+rtrim(cast(JobItemProductionStatusId as nvarchar(max)))+',',','+Coalesce(@JobItemProductionStatusIdList,cast(JobItemProductionStatusId  as nvarchar(max)),'') +',') > 0  
and charIndex(',' + rtrim(isnull(cast(JobItemStatusId  as nvarchar(max)),'')) + ',',',' + Coalesce(@JobItemStatusIdList,isnull(cast(JobItemStatusId  as nvarchar(max)),''),'') +',') > 0  
--AND isnull(ItemName,'')  like  '%' + coalesce(@ItemName,ItemName,'') + '%' ESCAPE '\'  
AND isnull(ItemName,'') like  '%' + REPLACE(REPLACE(isnull(@ItemName,''), '[', ''), ']', '') + '%'  
and isnull(ItemRoomId,0) = coalesce(@ItemRoomId,ItemRoomId,0)                                                               
and ItemCategoryId = ISNULL(@ItemCategoryId,ItemCategoryId)                                                               
and ISNULL(Locations.LocationId, 0) = coalesce(@SearchLocationId, Locations.LocationId, 0)  
and ItemCategoryId not in(5, 7)  
and isnull(JobId,0) = Coalesce(@JobId,JobId,0)  
and isnull(IsInRush,0) = Coalesce(@IsInRush,IsInRush,0)  
AND IsStorageLocation = ISNULL(@IsStorageLoc, IsStorageLocation)  
--and isnull(LocationBarcode,'') = Coalesce (@LocationBarcode,LocationBarcode,'')                   
AND ISNULL(JobItemProductionStatusId, 0) != 44  
AND ISNULL(JobItemProductionStatusId, 0) = ISNULL(@ItemProductionStatusId, JobItemProductionStatusId)  
AND ISNULL(JobItemStatusId, 0) != 86  
AND ISNULL(JobItemStatusId, 0) = Coalesce(@ItemStatusId, JobItemStatusId, 0)  
AND ((@IsDepartmentFilterChecked = 1 AND ISNULL(Locations.LocationTypeId, 0) != 6) OR (@IsDepartmentFilterChecked IS NULL))  
    UNION ALL  
    SELECT cL.LocationId, cL.ParentLocationId, cL.LocationName,   
  ISNULL(cL.LocationName, '') + ' {' + ISNULL(cL.LocationBarcode, '') + '}'  
    FROM Locations AS cL  
        INNER JOIN Loc AS pL  
        ON cL.LocationId = pL.ParentLocationId   
        --WHERE cL.ParentLocationId = ISNULL(@SearchLocationId, cL.ParentLocationId)  
        --AND (@IsOnlyRoot = 1 AND cL.ParentLocationId IS NULL)  
--    WHERE Level = 0  
)  
--insert locations  
INSERT INTO @TempL(LocationId, ParentLocationId, Name, NameBarcode, IsLocation)  
(  
 Select DISTINCT Loc.LocationId, Loc.ParentLocationId, Loc.LocationName, Loc.LocationNameBarcode, 1   
 FROM Loc   
 -- where Loc.ParentLocationId IS NULL  
 --WHERE Level = 0  
 --WHERE ISNULL(Loc.ParentLocationId, 0) = COALESCE(@SearchLocationId, Loc.ParentLocationId, 0)  
 --OR (@IsOnlyRoot = 1 AND Loc.ParentLocationId IS NULL)  
)  

  
SELECT     LocationId, ItemId, ParentLocationId, NameBarcode, IsLocation, JobItemStatusId, JobItemProductionStatusId, IsRush  
FROM       @TempL tl
where ParentLocationId is null or ParentLocationId=0  
order by tl.Name  
END  

I have add this Sp as function and also have created the complex type for this.
Please help me to get out of this.
Thanks in advance.