iT邦幫忙

0

C# Dapper SQL Jsonb 無法代入參數

Ks 2019-04-01 23:03:011257 瀏覽

我有一個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 });

1 個回答

3
暐翰
iT邦大師 1 級 ‧ 2019-04-02 08:29:27
最佳解答

查看所執行的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 });

至於為何要這麼做的詳細原因,可以閱讀:

看更多先前的回應...收起先前的回應...
Ks iT邦新手 4 級 ‧ 2019-04-02 09:53:37 檢舉

感謝暐翰大大的答覆,我底下有使用了這個部分。
/images/emoticon/emoticon02.gif

var result = 
await connection.QueryAsync<Table>(
mainTemplate.RawSql, mainTemplate.Parameters);

感覺上是因為這個雙引號的部分"@value",導致我的Value在最終執行的時候沒帶入, 但是還不知道怎麼修正這個部分.
我有試者直接把@value改成我要的值,資料讀取出來是正確的,
但是改回@value就無法.

builder.OrWhere("(\"JsonBColumn\" @> '{\"test\": \"@value\"}')", new { value });
暐翰 iT邦大師 1 級 ‧ 2019-04-02 10:22:30 檢舉

我更新回覆在原文了
你看一下,有問題跟我說一下 :D

Ks iT邦新手 4 級 ‧ 2019-04-02 11:14:47 檢舉

謝謝,我改成以下方式之後,
出現了錯誤 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 });
Ks iT邦新手 4 級 ‧ 2019-04-02 11:41:03 檢舉

感謝@暐翰的協助,問題解決了,我改成這樣之後就沒問題了.
同時也參考了這一篇
https://stackoverflow.com/questions/43395786/how-to-use-the-contains-operator-with-dapper-orm

但是還不怎麼了解關於 json_in與 cstring
這兩個部分的用法

非常感謝
/images/emoticon/emoticon02.gif

string value = "{\"test\": \"" + Guid + "\"}";
builder.OrWhere("(\"JsonBColumn\" @> json_in(@value::cstring) ::jsonb)", new { value });
暐翰 iT邦大師 1 級 ‧ 2019-04-02 12:10:45 檢舉

/images/emoticon/emoticon12.gif

我要發表回答

立即登入回答