c# - 返回复杂类型列表中的存储过程结果

Question

我有以下存储过程，它调用一个返回多行的动态查询。

CREATE PROCEDURE [dbo].[usp_list_connections] @skip int, @take int = 50
WITH EXECUTE AS OWNER
AS
BEGIN
    IF 1 = 2    -- For EF to recognize columns
    BEGIN
        SELECT CAST(1 AS int) [Id], CAST('A' AS nvarchar(100)) [Name], CAST(1 AS bit) [Sim], CAST(1 AS bit) [Device]
    END
    DECLARE @q NVARCHAR(MAX) ='';
        SET @q = N'
        SELECT DISTINCT e.[Id], e.[Name],
        CASE
            WHEN s.[Sim] IS NULL THEN 0
            ELSE 1 END AS [Sim],
        CASE
            WHEN d.[Device] IS NULL THEN 0
            ELSE 1 END AS [Device]
        FROM [dbo].[Employee] e
            LEFT JOIN [dbo].[Sim] s ON s.[Employee] = e.[Id]
            LEFT JOIN [dbo].[Device] d ON d.[Employee] = e.[Id]
        ORDER BY e.[Name]
        OFFSET @skip ROWS
        FETCH NEXT @take ROWS ONLY;'
    SET @q = REPLACE(@q, '@skip', @skip);
    SET @q = REPLACE(@q, '@take', @take);   

    EXEC sp_executesql @q;
END

我将该过程导入到 EF 并将其映射到一个复杂类型。当我尝试使用以下代码通过 EF 运行该过程时

var result = db.usp_list_connections(0,50); 

它返回如下错误信息:

"The result of a query cannot be enumerated more than once."

我做错了什么？如何将此存储过程的结果集存储在变量中？提前致谢。

Answer 1

尝试通过调用 ToList() 实现您的查询可能会解决您的问题:

var result = db.usp_list_connections(0,50).ToList();