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文件，将返回值改成一个空的结果集。