CODEWITHSUNDEEP
servicestackormlite-servicestack
Nick
Nick

Reputation: 1424

Exception in OrmLite: Must declare the scalar variable

Our code has a SqlExpression, which at its bare minimum is something like:

var q = db.From<Users>();

q.Where(u => u.Age == 25);

totalRecords = db.Scalar<int>(q.ToCountStatement());

q.ToCountStatement() generates the following query:

SELECT COUNT(*) FROM "Users" WHERE ("Age" = @0)

However, db.Scalar() throws an exception: Must declare the scalar variable "@0". This has started occurring in recent versions (tested in 4.0.54). The same code was working fine until v4.0.50. I've checked the release notes, but couldn't find a related change.

Even passing a parameter throws the same exception:

totalRecords = db.Scalar<int>(q.ToCountStatement(), 25);

Is it a bug, or my oversight?

Secondly, is it possible to get q.ToCountStatement() to generate a more optimized query with COUNT(Age) or COUNT([PrimaryKey]) instead of COUNT(*)?

Upvotes: 1

Views: 653

Answers (1)

mythz
mythz

Reputation: 143399

Now that OrmLite defaults to parameterized queries you also need to provide the queries db parameters when executing a query (if you've specified any params), e.g:

var q = db.From<Users>().Where(u => u.Age == 25);
var count = db.Scalar<int>(q.ToCountStatement(), q.Params);

You can also use OrmLite's explicit Count() API's, e.g:

db.Count<User>(x => x.Age == 25);

Or with a typed SqlExpression:

var q = db.From<User>().Where(x => x.Age == 25);
db.Count(q);

Otherwise another way to specify db params is to use an anonymous object, e.g:

db.Scalar<int>("SELECT COUNT(*) FROM Users WHERE Age=@age", new { age = 25});

Upvotes: 1

Related Questions