|
我一直在尝试失败,在Dapper中使用IEnumerablewith withWHERE IN子句已经有一段时间了。$ R+ ?3 g/ @! K* p. j% g
在文档中,它确实说IEnumerable<i>支持在a中使用,WHERE IN但我什至无法使它正常工作。
' w+ c6 t! C# x/ G0 w0 |Dapper allow you to pass in IEnumerable<i> and will automatically$ `! Z$ t: S N1 o2 X7 k
parameterize your query.: q+ e2 P! W8 z( ]
我不断收到的错误消息是Sql语法错误。 Incorrect syntax near ','., ~# u; v+ _2 Z( [# p
我整理了一些测试代码,希望它们能证明我正在尝试实现的目标。
$ h( d, b C" U+ [* R5 y, B
, L) |5 T1 l2 Q5 h! T7 D* E1 T6 ?string connString = "Server=*.*.*.*;Database=*;User Id=* assword=*;";4 t7 k( G4 e5 d3 A( O" o
string sqlStringIn = @"SELECT StringText FROM
/ k: i' ?( y- W0 x8 z6 n& B/ t( D4 P! g% w (SELECT 1 ID, 'A' StringID, 'This is a test' StringText
% b4 `2 R: v2 {0 j. Q$ J# X UNION SELECT 2 ID, 'B' StringID, 'Another test' StringText5 ~# x5 V% u. h
UNION SELECT 3 ID, 'C' StringID, 'And another' StringText3 k, [' O$ `! v0 G- }
UNION SELECT 4 ID, 'D' StringID, 'and again' StringText3 W% e z: K& W
UNION SELECT 5 ID, 'E' StringID, 'yet again' StringText) data8 g# S4 m$ [5 y
WHERE StringId IN (@str)";/ l2 I. _, {! D- w
string sqlIntegerIn = @"SELECT StringText FROM , a: x- d$ H8 X7 M- X; e) j
(SELECT 1 ID, 'A' StringID, 'This is a test' StringText% h* I: `7 D/ [. d- S& J Q
UNION SELECT 2 ID, 'B' StringID, 'Another test' StringText
0 i* F% @6 Q. l' @7 W' n UNION SELECT 3 ID, 'C' StringID, 'And another' StringText
1 N2 @8 Z, W0 E, y( O' `! | UNION SELECT 4 ID, 'D' StringID, 'and again' StringText- y. q! V) P- }8 Y) I+ k4 ^
UNION SELECT 5 ID, 'E' StringID, 'yet again' StringText) data6 h8 M# g( R$ p% r+ r9 c; C% t7 n' B4 @# c
WHERE ID IN (@integer)";; K+ ~" y0 O- }5 X
8 B; O+ P& C- c* Y! f
using (SqlConnection conn = new SqlConnection(connString))# r( C1 }3 _) G$ j
{
a c- R2 |8 t, Z conn.Open();
& h5 Y! u* Y: m. w# _ List<i> integers = new List<i>{ 1, 2, 3 };( h! S! M: o7 q5 f4 P9 Q0 O& R' B
List strings = new List { "A", "B", "C" };- K, B( [8 V/ A2 Y0 n: x. F) s: e: `
var parameters = new {str = strings, integer = integers }; E' ]2 \2 R. d# g5 X
//fails here8 n; J+ h: x f8 ^6 `7 ~3 _! r
IEnumerable intTest = conn.Query(sqlIntegerIn, parameters, commandType: System.Data.CommandType.Text);" [' [0 C7 j, {9 v c
//and here
: h6 A: L- i0 i1 V; b8 [ IEnumerable stringTest = conn.Query(sqlStringIn, parameters, commandType: System.Data.CommandType.Text);
- P1 |4 ]" g9 A# B! A* Q: v7 p. e}
) r3 z; W0 G* A+ P
% z4 m: M* x, S5 {7 I" ?8 X# L解决方案:
+ D; Y2 }8 U3 I" Z6 ^ , E" G/ A* t0 E* f6 |
& g, u; m/ T# J% s7 L
7 Q* r. Y8 S2 k9 [- T1 s7 Q
为了执行此处需要的操作,dapper需要即时更改SQL-因此需要 真正 确保它在做正确的事情。常规有效的SQL语法包括括号:
% l i- s/ c* d n+ z) O5 |WHERE StringId IN (@str)
. B3 z# I! J5 G6 L为了消除歧义, voodoo dapper语法 省略 了括号:
5 d( z0 s/ o% |9 I1 O! gWHERE StringId IN @str
* p- |9 e3 W2 c2 f如果检测到此错误,它将查找名为的参数str,并将其扩展为以下参数之一:, w, }# j9 e' \! f* [' |$ m
WHERE 1=0 -- if no values( q3 U) m- }8 x* Q( l( v, M
WHERE StringId = @str -- if exactly one value
! j+ i8 H+ N7 P3 a [. KWHERE StringId IN (@str0, @str1, ...) -- if more than one value" M& J( }, H }7 X
但简短的版本:删除括号。 |
|