使用 PetaPoco 从 PostgreSQL 返回记录 ID

Question

PetaPoco PostgreSQL 11.1

我正在尝试获取已删除记录 ID 的列表。这不起作用：

var sql = new Sql()
.Append(@"; WITH _in (lastname, firstname, birthdate, description, dencounter) AS (
VALUES ( UPPER(@0), UPPER(@1), @2::date, LOWER(@3), @4::date  )
)
DELETE FROM dx d
USING _in n
JOIN patients pt ON (pt.lastname, pt.firstname, pt.birthdate) = (n.lastname, n.firstname, n.birthdate)
JOIN disease z ON (z.description = n.description)
WHERE (d.patient_recid, d.disease_recid, d.dposted)=(pt.recid, z.recid, n.dencounter)
RETURNING d.recid;", lastName, firstName, birthDate, description, tencounter);
return db.Fetch<int?>(sql);

回归没有得到尊重。(我收到显示删除成功的"1"而不是往复值(。它在 pgAdmin 4 中正常运行。

无论如何可以用 PetaPoco(和 C#(做到这一点吗？我在寻找的不仅仅是一个单一的recid，而是一个IEnumerable的int？从许多删除。

蒂亚

Answer 1

我花了一点时间来了解这里发生了什么。

Fetch<T>运行查询并将结果集作为类型化列表返回。直接来自文档。

您可能需要遵守其中的键入部分。

希望以下内容演示正在发生的事情。

public class TypedReturn { 
public string recid { get; set; }
}
var sql = new Sql()
.Append(@"; WITH _in (lastname, firstname, birthdate, description, dencounter) AS (
VALUES ( UPPER(@0), UPPER(@1), @2::date, LOWER(@3), @4::date  )
)
DELETE FROM dx d
USING _in n
JOIN patients pt ON (pt.lastname, pt.firstname, pt.birthdate) = (n.lastname, n.firstname, n.birthdate)
JOIN disease z ON (z.description = n.description)
WHERE (d.patient_recid, d.disease_recid, d.dposted)=(pt.recid, z.recid, n.dencounter)
RETURNING d.recid;", lastName, firstName, birthDate, description, tencounter);
List<TypedReturn> returnValues = db.Fetch<TypedReturn>(sql);
foreach(var returnValue in returnValues) 
{
Console.WriteLine(returnValue.recid);
}

或者你可以使用dynamic，你会这样做

var sql = new Sql()
.Append(@"; WITH _in (lastname, firstname, birthdate, description, dencounter) AS (
VALUES ( UPPER(@0), UPPER(@1), @2::date, LOWER(@3), @4::date  )
)
DELETE FROM dx d
USING _in n
JOIN patients pt ON (pt.lastname, pt.firstname, pt.birthdate) = (n.lastname, n.firstname, n.birthdate)
JOIN disease z ON (z.description = n.description)
WHERE (d.patient_recid, d.disease_recid, d.dposted)=(pt.recid, z.recid, n.dencounter)
RETURNING d.recid;", lastName, firstName, birthDate, description, tencounter);
List<dynamic> returnValues = db.Fetch<dynamic>(sql);
foreach(var returnValue in returnValues) 
{
Console.WriteLine(returnValue.recid);
}