我有一個Jsonb的SQL指令需要放到Dapper(DB: postgresql,Type:jsonb)
在SQL Explorer查詢的時候如下
SELECT count(1) FROM Table WHERE "JsonBColumn" @> '{"test": "something value"}'
但是我想放到Dapper SQLBuilder進行參數化的時候,似乎沒辦法帶入參數.
查看所執行的SQL指令會完全的印出“@userid”,而不是替代過去我的參數,
請問對於雙引號“的部分我改怎麼修正呢？
謝謝
builder.OrWhere("(\"JsonBColumn\" @> '{\"test\": \"@value\"}')", new { value });
已邀請的邦友 {{ invite_list.length }}/5
查看所執行的SQL指令會完全的印出“@userid”,而不是替代過去我的參數,
Dapper SQLBuilder 用法不是這樣,它不會替代參數
我這邊沒有PG,我先以SQLServer為示範
舉例
我想要動態建立SQL,需要先使用AddTemplate設定我需要的SQL樣板
接著呼叫SqlBuilder的方法,添加我要的SQL跟參數
像是我想要動態where我需要在Template想添加部分加上/**where**/(系統到時候會自動替換)
最後使用Dapper Query API時候使用SQLBuilder的RawSql得到生成SQL、Parameters傳入參數。
void Main()
{
var builder = new SqlBuilder();
var sqlTp = builder.AddTemplate("with T as (select @value val) SELECT @value FROM T /**where**/");
builder.OrWhere(" 'something value' = @value ", new { value = "something value" }); //得到SQL是 : with T as (select @value val) SELECT @value FROM T WHERE ( 'something value' = @value )
var data = Connection.QueryFirst<string>(sqlTp.RawSql, sqlTp.Parameters).Dump();
//Dapper底層呼叫SQL,幫我們做好參數化 : exec sp_executesql N'with T as (select @value val) SELECT @value FROM T WHERE ( ''something value'' = @value ) ',N'@value nvarchar(4000)',@value=N'something value'
//結果: "something value"
}
感覺上是因為這個雙引號的部分"@value",導致我的Value在最終執行的時候沒帶入, 但是還不知道怎麼修正這個部分.
我有試者直接把@value改成我要的值,資料讀取出來是正確的,
但是改回@value就無法.builder.OrWhere("("JsonBColumn" @> '{"test": "@value"}')", new { value });
不能放在字串內
要使用||@value||串接字串,並且轉jsonb
以原生SQL舉例
CREATE TABLE T (
"JsonBColumn" jsonb
);
INSERT INTO T
("JsonBColumn")
VALUES
('{"test": "something value"}');
PREPARE query (varchar) AS
select * from T
WHERE "JsonBColumn" @> ('{"test": "'||$1||'"}')::jsonb;
EXECUTE query('something value');
DB Fiddle - SQL Database Playground
最後你改成以下Code試一下
builder.OrWhere( "(\"JsonBColumn\" @> '{\"test\": \"' || @value || '\"}')::jsonb ", new { value });
至於為何要這麼做的詳細原因,可以閱讀:
感謝暐翰大大的答覆,我底下有使用了這個部分。
var result =
await connection.QueryAsync<Table>(
mainTemplate.RawSql, mainTemplate.Parameters);
感覺上是因為這個雙引號的部分"@value",導致我的Value在最終執行的時候沒帶入, 但是還不知道怎麼修正這個部分.
我有試者直接把@value改成我要的值,資料讀取出來是正確的,
但是改回@value就無法.
builder.OrWhere("(\"JsonBColumn\" @> '{\"test\": \"@value\"}')", new { value });
我更新回覆在原文了
你看一下,有問題跟我說一下 :D
謝謝,我改成以下方式之後,
出現了錯誤 invalid input syntax for type json.
,還在研究怎麼解決,我的value會傳入一個Guid
builder.OrWhere( "(\"JsonBColumn\" @> '{\"test\": \"'||@value||'\"}')::jsonb ", new { value });
我換了另外一種方式,但是出現一樣的錯誤
string value = "{\"test\": \"" + Guid + "\"}";
builder.OrWhere("(\"JsonBColumn\" @> '@value')::jsonb", new { value });
感謝@暐翰的協助,問題解決了,我改成這樣之後就沒問題了.
同時也參考了這一篇
https://stackoverflow.com/questions/43395786/how-to-use-the-contains-operator-with-dapper-orm
但是還不怎麼了解關於 json_in與 cstring
這兩個部分的用法
非常感謝
string value = "{\"test\": \"" + Guid + "\"}";
builder.OrWhere("(\"JsonBColumn\" @> json_in(@value::cstring) ::jsonb)", new { value });
iThome鐵人賽
看影片追技術