SelectMulti with parameters

ServiceStack.OrmLite
1

I’m building a query using q.Where("field = @fieldValue") expressions, and finally a db.SelectMulti<T1,T2>(q).

However there is nowhere to insert the parameter in the SelectMulti. And it doesn’t work to add it as second parameter in the Where() like this:

q.Where("field = @fieldValue", new { fieldValue = 2 })

does put the parameter into the db.Parameters, but it seems like it’s not inserted propertly into the final query, as I’m getting this exception:
(from SQLite): Message "unknown error\r\nInsufficient parameters supplied to the command"

I have noticed with another example I had, that the parameters had to be added at the very end. My example was using db.Column(), and an earlier raw-sql based Where(). In that case I had to convert to SQL Query, then call db.Column(sql, new { param1, param2} ), which worked.

But for SelectMany I can’t find a way to insert parameters, so I can’t try that.

I’ve noticed in the debugger (using q.ToSelectStatement() to inspec what would have been sent) that the parameters are named @1, @2, and so on, and these match with the parameters that are added using the typed API, but not for the parameter added using raw SQL, which in the q.Parameters have been renamed to @3, but in the SQL statement it still has its original name.

2

Can you provide the full query that you’re using, e.g.

OrmLiteUtils.PrintSql();

var q = q.From<T1>().Join<T2>()
    .Where("field1 = @field1 AND field2 = @field2", new { field1, field2 });
db.SelectMulti<T1,T2>(q);

and paste the SQL Output printed to the console.

Rather than a long description of what you’re doing, it’s better to provide a repro we can run & see the issue instead. Ideally something that can be executed on https://gist.cafe

3

Sorry, I was lazy and tired at the end of the day. Here’s an example:

https://gist.cafe/103db5279aa2ea7618f9c345821d99b9

4

It’s because the expression for using q.Where(stringFormat, params) using C# string format:

db.Select(db.From<MyType>().Where("field1 = {0}", "Hello")).PrintDump();

Will look to add more examples in the docs to show this.

You can use anonymous object syntax with the .Select SQL Expression APIs, e.g:

db.Select<MyType>("field1 = @theVariable", new { theVariable = "Hello" }).PrintDump();

Note: if the generated SQL contains params you’ll need to provide the parameters as well, e.g:

var q = db.From<MyType>().Where("field1 = {0}", "Hello");
db.Select<MyType>(q.ToSelectStatement(), q.Params).PrintDump();

Other working examples:

db.SqlList<MyType>("SELECT * FROM MyType WHERE field1 = @theVariable", new { theVariable = "Hello" }).PrintDump();

db.Select(db.From<MyType>().Where(x => x.field1 == "Hello")).PrintDump();
1 Like
5

Ah, the C# string syntax was the key. That solved it.

I’ve updated the gist with a 4th working example based on your answer, for future readers.

1 Like
6

Thx, but it’s better to copy + paste the code into the answer so it’s more visible and preserved if the gist is ever deleted:

// fix, based on @mythz' answer is that the Where() uses C# string format:
var q = db.From<MyType>().Where("field1 = {0}", "Hello");
var results = db.Select(q);