如何在linq中为storeprocedure设置自动返回类型为sql？ [英] How to set autogenrated return type for storeprocedure in linq to sql ?

问题描述

Hi ,

Hii , 


ALTER  PROCEDURE [dbo].[usp_Get121ReportForMasterAdmin] 
(  
 @DataForYear   Int,  
 @CompanyId    Int,  
 @ManagerId    Int,  
 @BusinessUnitId   Int,  
 @DepartmentId   Int,  
 @TeamId     Int   
)  
As  
Begin  
  
  if (@BusinessUnitId <= 0)  
  begin  
  set @DepartmentId = 0  
  set @TeamId = 0  
  end  
  
  if (@DepartmentId <= 0)  
  begin  
  set @TeamId = 0  
  end  
  
  -- get company business year period  
  DECLARE @businessYearStart  as date  
  DECLARE @businessYearEnd as date  
  exec usp_GetCompanyBusinessYearPeriod   
   @compID = @CompanyId,  
   @DataForYear = @DataForYear,  
   @businessYear_end = @businessYearEnd OUTPUT,  
   @businessYear_start = @businessYearStart OUTPUT  
    
  
  Declare @IncludeTestEmployeeInReport bit  
  Declare @IncludeHRAdminInReport bit  
  
  set @IncludeTestEmployeeInReport=isnull((Select IncludeTestEmployeeInReport from Company where id=@CompanyId),0)  
  set @IncludeHRAdminInReport=isnull((Select IncludeHRAdminInReport from Company where id=@CompanyId),0) 
  
  declare @finalTable as table
	(
		rowID				int identity(1, 1),
		EmpId				int,
		Companyname   nvarchar(max),
		PlanSubject     nvarchar(max),
		PlanType        nvarchar(50),
		PlanDate        datetime,
		[Status]         nvarchar(20),
		EmployeeName  nvarchar(50),
		ManagerName  nvarchar(50)
		
	) 
  insert into @finalTable
  Select
	EmpId,
   (Select Name from Company where Id = CompanyId) Companyname,PlanSubject,PlanType,PlanDate,[Status],EmployeeName,  
  ManagerName From  
  (  
  Select e.Id as EmpId ,p.Comp_Id CompanyId,  
  (Select p.Plan_Subject) as PlanSubject,  
  (Select Case p.[121Type] when  1 then 'Virtual' when  2 then 'Face to Face' Else '' end) as PlanType,  
  (Select p.Paln_Date) as PlanDate,  
  (Select   Case p.IsClosed when  0 then 'Planned' when  1 then 'Published' Else '' end) as  [Status],  
  (Select e.LastName + ' ' + e.FirstName) EmployeeName,  
  
  (Select   
  case when ((Select count(*) from CurrentManagerLogDetails where EmployeeId=e.Id and DataForYear=@DataForYear)=0)  
  then  
  (Select LastName + ' ' + FirstName from Employee where Id = e.ManagerId)  
  else  
  (Select LastName + ' ' + FirstName from Employee where Id = (Select CurrentManagerId from CurrentManagerLogDetails   
  where EmployeeId=e.Id and DataForYear=@DataForYear))  
  end) ManagerName  
    
  from [Plan] p   
  inner join Employee e     
  on p.PlanAddedFor_Id = e.Id  
  where p.Comp_Id = Company_Id    
  and p.IsDeleted=0  
  and p.Comp_Id = @CompanyId  
  and e.Status_Id=0  
  and ((@IncludeTestEmployeeInReport <> 1) and (e.Id in (Select Id from Employee with (nolock)   
  where isnull(IsTest,0) <> 1 and Company_Id=@CompanyId)) or @IncludeTestEmployeeInReport=1)  
  and e.IsDeleted = 0  
  and ((@IncludeHRAdminInReport <> 1) and (e.Id in (Select Id from Employee with (nolock)  where Role_Id <> 4  
  and Company_Id=@CompanyId )) or @IncludeHRAdminInReport=1)  
  
  
  and isnull(p.Paln_Date, @businessYearStart) between @businessYearStart and @businessYearEnd  
    
  and ( isnull(@ManagerId,0)=0 or e.ManagerId = @ManagerId)  
  and (isnull(@BusinessUnitId,0)=0 or e.BusinessUnitId = @BusinessUnitId)  
  and (isnull(@DepartmentId,0)=0 or e.DepartmentId = @DepartmentId)  
  and (isnull(@TeamId,0)=0 or e.TeamId = @TeamId )  
    
  )f  
  order by ManagerName,EmployeeName  

  declare @totalRecords as int 
	set @totalRecords = (select COUNT(*) from @finalTable)
	if(@totalRecords > 0)
	begin
		declare @counter as int
		declare @tmpID as int
		declare @tmpMgrName as nvarchar(100)
		set @counter = 1
		while (@counter <= @totalRecords)
		begin
			set @tmpID = (select EmpId from @finalTable where rowID = @counter)
			EXEC usp_GetManagerNameByYear @empId = @tmpID, @compId	= @CompanyId, @dataForYear= @DataForYear, @returnManagerName = @tmpMgrName output
			update @finalTable set ManagerName = ISNULL(@tmpMgrName, ManagerName) where rowID = @counter and EmpId = @tmpID 
			set @counter = @counter + 1
		end
	end

	select

		Companyname  ,
		PlanSubject    ,
		PlanType       ,
		PlanDate       ,
		[Status]       ,
		EmployeeName,
		ManagerName 

	from @finalTable
	order by ManagerName, EmployeeName
   
End


GO


this is my procedure 

当我尝试拖下我的linq orm它给我一个错误，无法检测到程序的返回类型..



请建议..我试图通过转到此程序的属性为此设置autotdetect ..但不能..



请建议

when i try to drag on drop on my linq orm it gives me an error that return type of the procedure could not be detected ..

Please suggest .. i tried to set autotdetect for this by going to properties of this procedure .. but not able to ..

Please suggest

推荐答案

有时linq to sql非常难以预测： - 当你尝试使用tem表或当你尝试返回复杂的表结构时..很多时候linq到sql不能用这种类型



做完之后一些关于互联网的研究，我发现以下解决方案

sometimes linq to sql is very unpredictable :- when u try to use tem table or when u try to return complex table structure ..many of the times linq to sql doesnt work with this type

after doing some research on internet , i found the below solution

SET FMTONLY OFF;

通过在storeprocedure linq开头使用此命令来接受此过程并使该过程返回类型为autodetect retyrn type

by using this commmand at the begining of storeprocedure linq to sql accept this procedure and makes that procedures return type as autodetect retyrn type

这篇关于如何在linq中为storeprocedure设置自动返回类型为sql？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！