Subsonic - Bit operation in Where Clause

Question

I'm trying to make something like this:

int count = new Select().From(tblSchema).Where("Type & 1").IsEqualTo("1").GetRecordCount();

And the error message is:

Incorrect syntax near '&'.

Must declare the scalar variable "@Deleted".

Is it possible to do that with SubSonic?

Answer 1

It is a little fuzzy as to what you are trying to accomplish but here is a best guess.

int count = new Select().From(tbl.Schema).Where(tbl.TypeColumn).IsEqualTo(true).GetRecordCount();

Answer 2

Must declare the scalar variable "@Deleted"

The second error would be caused by using logical deletes on the table you are querying (the table has an isDeleted or Deleted column).

But I'm looking through the code, I'm not sure how that parameter is getting in there. The SqlQuery.GetRecordCount method doesn't call CheckLogicalDelete(), from what I can tell. Is that error message unrelated?

Answer 3

Thanks for the response. =)

I'm sorry, I have several fields which I would like to perform the same operation on.
I didn't pasted the error message produced by the code.

It's supposed to be:

Must declare the scalar variable "@Type"

The error message was for:

int count = new Select().From(tblSchema).Where("Deleted & 2").IsEqualTo("2").GetRecordCount();

What I'm trying to accomplish here is putting several values into one field. For example, the value for Deleted field could be:

• 1 = Deleted by receiver
• 2 = Deleted by sender
• 4 = Deleted by administrator

So, if both receiver and sender have deleted the records, the Deleted value would be 3.

As for the Type column, I have 1, 2, 4, 8, 16, 32, 64, and 128.
I could have put it on another table and used many-to-many relationship, but I thought this was simpler.

The SQL query I'd like do produce is something like:

SELECT * FROM items WHERE Deleted & 2 = 2

That's why I can't use Where(tbl.TypeColumn).

Answer 4

This seems to be a bug in the way SubSonic is naming it's parameters when it generates the SQL to be executed.

What's happening is that SubSonic is looking at "Type & 1" and then creating a parameter to compare against called @Type&10 which is not a valid SQL parameter name. So you'll end up with the following SQL from your original query. You should submit a bug to http://code.google.com/p/subsonicproject/

Meanwhile you can workaround the bug for now by using an inline query:

http://subsonicproject.com/docs/Inline_Query_Tool