LINQ的ORM功能中对使用sp_executesql语句的存储过程的支持-阿里云开发者社区

在实际的项目中，我们经常会碰到存储过程中需要使用sp_executesql语句的情形，如下：

1 IF EXISTS ( SELECT * FROM sysobjects WHERE type = 'P' AND name = 'GetEmployeesByFilter')

2 BEGIN

3 DROP Procedure [GetEmployeesByFilter]

4 END

6 GO

8 CREATE Procedure [GetEmployeesByFilter]

9 (

10 @EmployeeNo NVarChar(50) = NULL,

11 @EmployeeName NVarChar(50) = NULL,

12 @DepartmentId Int = NULL,

13 @PositionId Int = NULL,

14 @EmployeeManager Int = NULL,

15 @BeginEmployeeEntryDate DateTime = NULL,

16 @EndEmployeeEntryDate DateTime = NULL,

17 @EmployeeStatus Int = NULL,

18 @PageSize Int = NULL,

19 @PageIndex Int = NULL,

20 @RecordCount Int = NULL OUTPUT

21 )

23 AS

25 BEGIN

26 DECLARE @MinIndex Int

27 DECLARE @MaxIndex Int

28 SET @MinIndex = (@PageIndex - 1) * @PageSize + 1

29 SET @MaxIndex = @MinIndex + @PageSize - 1

31 DECLARE @Where NVarChar( MAX)

32 SET @Where = '0 = 0'

33 IF @EmployeeNo IS NOT NULL

34 SET @Where = @Where + ' AND [EmployeeNo] LIKE ''%' + @EmployeeNo + '%'''

35 IF @EmployeeName IS NOT NULL

36 SET @Where = @Where + ' AND [EmployeeName] LIKE ''%' + @EmployeeName + '%'''

37 IF @DepartmentId IS NOT NULL

38 SET @Where = @Where + ' AND [DepartmentId] = ''' + CONVERT( NVarChar, @DepartmentId) + ''''

39 IF @PositionId IS NOT NULL

40 SET @Where = @Where + ' AND [PositionId] = ''' + CONVERT( NVarChar, @PositionId) + ''''

41 IF @EmployeeManager IS NOT NULL

42 SET @Where = @Where + ' AND [EmployeeManager] = ''' + CONVERT( NVarChar, @EmployeeManager) + ''''

43 IF @BeginEmployeeEntryDate IS NOT NULL

44 SET @Where = @Where + ' AND [EmployeeEntryDate] >= ''' + CONVERT( NVarChar, @BeginEmployeeEntryDate, 101) + ' ' + '00:00:00' + ''''

45 IF @EndEmployeeEntryDate IS NOT NULL

46 SET @Where = @Where + ' AND [EmployeeEntryDate] <= ''' + CONVERT( NVarChar, @EndEmployeeEntryDate, 101) + ' ' + '23:59:59' + ''''

47 IF @EmployeeStatus IS NOT NULL

48 SET @Where = @Where + ' AND [EmployeeStatus] = ''' + CONVERT( NVarChar, @EmployeeStatus) + ''''

50 DECLARE @Record NVarChar( MAX)

51 SET @Record = 'SELECT ROW_NUMBER() OVER(ORDER BY [EmployeeId]) AS [Index],

52 [EmployeeId],

53 [EmployeeNo],

54 [EmployeeName],

55 [DepartmentId],

56 [PositionId],

57 [EmployeeManager],

58 [EmployeeGender],

59 [EmployeeEntryDate],

60 [EmoplyeeBirthday],

61 [EmployeePhone],

62 [EmployeeEmail],

63 [EmployeeStatus]

64 FROM [Employee]

65 WHERE' + ' ' + @Where

67 DECLARE @Sql NVarChar( MAX)

68 SET @Sql = 'SELECT @RecordCount = COUNT(*)

69 FROM (' + @Record + ') DERIVEDTBL

71 SELECT [EmployeeId],

72 [EmployeeNo],

73 [EmployeeName],

74 [DepartmentId],

75 [PositionId],

76 [EmployeeManager],

77 [EmployeeGender],

78 [EmployeeEntryDate],

79 [EmoplyeeBirthday],

80 [EmployeePhone],

81 [EmployeeEmail],

82 [EmployeeStatus]

83 FROM (' + @Record + ') DERIVEDTBL

84 WHERE [Index] >= @MinIndex AND [Index] <= @MaxIndex'

86 DECLARE @Parameter NVarChar( MAX)

87 SET @Parameter = '@MinIndex Int, @MaxIndex Int, @RecordCount Int OUTPUT'

89 EXEC sp_executesql @Sql, @Parameter, @MinIndex, @MaxIndex, @RecordCount OUTPUT

90 END

92 GO

但在这种情况下，LINQ往往不能正确识别：

1 < Function Name =" dbo.GetEmployeesByFilter" Method =" GetEmployeesByFilter" >

2 < Parameter Name =" EmployeeNo" Parameter =" employeeNo" Type =" System.String" DbType =" NVarChar(50)" />

3 < Parameter Name =" EmployeeName" Parameter =" employeeName" Type =" System.String" DbType =" NVarChar(50)" />

4 < Parameter Name =" DepartmentId" Parameter =" departmentId" Type =" System.Int32" DbType =" Int" />

5 < Parameter Name =" PositionId" Parameter =" positionId" Type =" System.Int32" DbType =" Int" />

6 < Parameter Name =" EmployeeManager" Parameter =" employeeManager" Type =" System.Int32" DbType =" Int" />

7 < Parameter Name =" BeginEmployeeEntryDate" Parameter =" beginEmployeeEntryDate" Type =" System.DateTime" DbType =" DateTime" />

8 < Parameter Name =" EndEmployeeEntryDate" Parameter =" endEmployeeEntryDate" Type =" System.DateTime" DbType =" DateTime" />

9 < Parameter Name =" EmployeeStatus" Parameter =" employeeStatus" Type =" System.Int32" DbType =" Int" />

10 < Parameter Name =" PageSize" Parameter =" pageSize" Type =" System.Int32" DbType =" Int" />

11 < Parameter Name =" PageIndex" Parameter =" pageIndex" Type =" System.Int32" DbType =" Int" />

12 < Parameter Name =" RecordCount" Parameter =" recordCount" Type =" System.Int32" DbType =" Int" Direction =" InOut" />

13 < Return Type =" System.Int32" DbType =" Int" />

14 </ Function >

这就是说，原本这个存储过程会返回一个结果集的，但LINQ会误认为返回的是一个Int32的值。
遇到这种情况，我们该怎么处理呢？这里提供两种解决方案：
1、直接更改DBML文件，将返回值改成一个空的结果集。