EF Core调用PostgreSQL函数成功但返回0条结果问题排查

我有一个存储文件树结构节点信息的数据库表main.nodes，同时有一个数据库函数main.find_node，该函数接收字符串路径作为输入，判断路径是否存在于树中，并返回路径最后一个节点的ID，需求是从数据库获取已存在的叶节点ID。

数据库函数定义 #

-- FUNCTION: main.find_node(character varying)

-- DROP FUNCTION IF EXISTS main.find_node(character varying);

CREATE OR REPLACE FUNCTION main.find_node(
    _path character varying)
    RETURNS TABLE(id integer)
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
DECLARE 
    var_folder_current character varying;
    var_parent integer := 1;
    c text;
BEGIN
    drop table if exists tmp_table;
    create temp table tmp_table (
        id integer
    );
    RAISE NOTICE '_Inside main.find_node';
    FOR c IN SELECT unnest(string_to_array(_path, '/'))
    LOOP
        SELECT INTO var_parent main.get_node(c, var_parent);
        RAISE NOTICE 'node: %', c;
        RAISE NOTICE 'parent: %', var_parent;
        IF var_parent = -1
        THEN
            EXIT;
            --RETURN -1;
        END IF;
    END LOOP;
    insert into tmp_table(id) values (var_parent);
    return query select n.id from main.nodes n where n.id = var_parent;
    --RETURN var_parent;
END;
$BODY$;

直接在数据库执行select * from main.find_node('x/docs/addnewproj.png')能得到结果：

"id"
1890184

但在C#中通过EF Core调用该函数时却返回0条结果，相关C#代码如下：

public bool FileExists(long id, string bucket, string path)
{
    var exists = false;
    if (id != -1)
    {
        exists = FileIdExists(id);
    }
    else
    {
        exists = FilePathExists(bucket, path);
        if (!exists)
        {
            var x = _dbContext.nodes.Where(n => n.id == 1890184);
            var existing_node = _dbContext.nodes.FromSqlRaw("select * from main.find_node('{0}')", path);
            if (existing_node != null)
                exists = true;
        }
    }
    return exists;
}

直接查询ID为1890184的节点能得到结果，但existing_node始终为空。

数据库表结构 #

-- Table: main.nodes

-- DROP TABLE IF EXISTS main.nodes;

CREATE TABLE IF NOT EXISTS main.nodes
(
    id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    type integer NOT NULL,
    active boolean NOT NULL,
    parent_id integer NOT NULL,
    name text COLLATE main.case_insensitive_collation NOT NULL,
    inherits_permissions boolean NOT NULL,
    insert_by text COLLATE pg_catalog."default" NOT NULL,
    insert_date timestamp with time zone NOT NULL,
    update_by text COLLATE pg_catalog."default" NOT NULL,
    update_date timestamp with time zone NOT NULL,
    CONSTRAINT "PK_nodes" PRIMARY KEY (id)
)

Node实体类定义 #

public class Node
{
    [Key]
    public int id { get; set; }
    
    [Required]
    public NodeType type { get; set; }

    [Required]
    public bool active { get; set; }
    
    [Required]
    public int parent_id { get; set; }
    
    [Required]
    public string name { get; set; }
    
    [Required]
    public bool inherits_permissions { get; set; }

    [Required]
    public string insert_by { get; set; }

    [Required]
    public DateTime insert_date { get; set; }

    [Required]
    public string update_by { get; set; }

    [Required]
    public DateTime update_date { get; set; }

    [NotMapped]
    public int size { get; set; }

    [NotMapped]
    public int size_limit { get; set; }
}

核心问题分析 #

1. 字段映射不匹配：main.find_node仅返回id字段，但你用_dbContext.nodes.FromSqlRaw接收结果，EF Core期望返回Node实体的所有字段，无法将单字段结果映射为完整实体，导致返回空集合。
2. 延迟查询未触发：FromSqlRaw返回的IQueryable<Node>是延迟加载对象，不会立即执行数据库查询。直接判断existing_node != null永远为true，但实际未获取到数据。
3. SQL注入风险：用'{0}'拼接路径参数，若路径含单引号会触发语法错误，同时存在SQL注入漏洞。

修复方法 #

方法一：关联表获取完整实体

修改SQL语句，通过函数返回的ID关联main.nodes表，获取完整节点字段：

var existing_node = _dbContext.nodes.FromSqlRaw(@"
    SELECT n.* FROM main.nodes n
    JOIN main.find_node({0}) fn ON n.id = fn.id
", path);
// 触发查询并判断是否存在
exists = existing_node.Any();

方法二：直接查询ID验证存在性

若仅需判断节点是否存在，无需完整实体，可直接查询函数返回的ID：

// 定义接收ID的类
public class NodeIdResult
{
    public int Id { get; set; }
}

// 在DbContext中注册无键实体（EF Core 3.0+）
// modelBuilder.Entity<NodeIdResult>().HasNoKey();

// 执行查询并验证
var nodeId = _dbContext.Set<NodeIdResult>()
    .FromSqlRaw("SELECT * FROM main.find_node({0})", path)
    .FirstOrDefault();

exists = nodeId != null && _dbContext.nodes.Any(n => n.id == nodeId.Id);

方法三：修改函数返回完整实体（可选）

调整main.find_node函数，直接返回完整节点记录，让EF Core可直接映射：

CREATE OR REPLACE FUNCTION main.find_node(
    _path character varying)
    RETURNS TABLE(id integer, type integer, active boolean, parent_id integer, name text, inherits_permissions boolean, insert_by text, insert_date timestamp with time zone, update_by text, update_date timestamp with time zone)
    LANGUAGE 'plpgsql'
AS $BODY$
DECLARE 
    var_parent integer := 1;
    c text;
BEGIN
    FOR c IN SELECT unnest(string_to_array(_path, '/'))
    LOOP
        SELECT INTO var_parent main.get_node(c, var_parent);
        IF var_parent = -1
        THEN
            EXIT;
        END IF;
    END LOOP;
    RETURN QUERY SELECT * FROM main.nodes n WHERE n.id = var_parent;
END;
$BODY$;

之后C#调用可简化为：

exists = _dbContext.nodes.FromSqlRaw("SELECT * FROM main.find_node({0})", path).Any();

关键注意事项 #

• 始终使用参数化查询（{0}无需加单引号，EF Core会自动处理参数），避免SQL注入和语法错误。
• 对IQueryable类型结果，必须调用ToList()、Any()、FirstOrDefault()等方法触发数据库查询。

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