EF Core如何获取PostgreSQL返回refcursor类型的函数结果

问题背景 #

你定义了返回游标的PostgreSQL函数：

CREATE OR REPLACE FUNCTION public."test_func"(in ref refcursor, in id int) 
RETURNS refcursor AS $$
BEGIN
  OPEN ref FOR 
    SELECT *
    FROM "test_table"
    WHERE "Id" = id;
  RETURN ref;
END;
$$ LANGUAGE plpgsql;

常规SQL调用需先执行函数打开游标，再通过FETCH获取数据，但直接用EF Core的FromSqlInterpolated会报错——因为第一条语句仅返回游标名称，无法匹配实体类的Id字段。

可行解决思路 #

思路1：修改函数为返回表类型（推荐） #

将函数改为直接返回查询结果的表类型，适配EF Core的实体映射逻辑：

CREATE OR REPLACE FUNCTION public."test_func"(in id int) 
RETURNS SETOF "test_table" AS $$
BEGIN
  RETURN QUERY
    SELECT *
    FROM "test_table"
    WHERE "Id" = id;
END;
$$ LANGUAGE plpgsql;

之后在EF Core中可直接调用：

// 方式1：使用FromSqlInterpolated
var result = context.TestTable.FromSqlInterpolated($"SELECT * FROM public.test_func({id})").ToList();

// 方式2：配置DbFunction映射（更符合EF Core风格）
[DbFunction("test_func", Schema = "public")]
public IQueryable<TestTable> TestFunc(int id)
{
    return FromExpression(() => TestFunc(id));
}
// 调用示例
var data = context.TestFunc(1).ToList();

思路2：用ADO.NET批量执行游标操作 #

若无法修改原函数，可直接借助Npgsql的ADO.NET接口，在同一个数据库连接中连续执行打开游标和获取数据的命令：

using var connection = context.Database.GetDbConnection();
await connection.OpenAsync();

using var cmd = connection.CreateCommand();
// 合并执行打开游标+获取数据的命令
cmd.CommandText = "SELECT public.test_func('my_cursor', @id); FETCH ALL IN \"my_cursor\";";
cmd.Parameters.Add(new NpgsqlParameter("@id", 1));

using var reader = await cmd.ExecuteReaderAsync();
// 手动映射结果到实体类
var result = new List<TestTable>();
while (await reader.ReadAsync())
{
    var entity = new TestTable
    {
        Id = reader.GetInt32(reader.GetOrdinal("Id")),
        // 按需映射其他字段
    };
    result.Add(entity);
}

注意必须在同一个连接中执行两个命令，因为PostgreSQL游标是会话级别的。

思路3：利用Npgsql原生游标支持 #

Npgsql提供了对PostgreSQL游标的直接操作API，可通过NpgsqlCursor读取数据：

using var connection = context.Database.GetDbConnection();
await connection.OpenAsync();

using var cmd = connection.CreateCommand();
cmd.CommandText = "SELECT public.test_func('my_cursor', @id);";
cmd.Parameters.Add(new NpgsqlParameter("@id", 1));

// 执行函数获取游标对象
using var reader = await cmd.ExecuteReaderAsync();
await reader.ReadAsync();
var cursor = reader.GetValue(0) as NpgsqlCursor;

// 从游标中读取所有数据
using var cursorReader = await cursor.FetchAllAsync();
var result = new List<TestTable>();
while (await cursorReader.ReadAsync())
{
    var entity = new TestTable
    {
        Id = cursorReader.GetInt32(cursorReader.GetOrdinal("Id")),
        // 按需映射其他字段
    };
    result.Add(entity);
}

内容的提问来源于stack exchange，提问作者dstr