Debug a Subsonic Select Query

Question

I've got a Subsonic query that isn't returning any values. I think the problem is in my where clause, although I'm not sure why.

What I'm really looking for is a way to debug the query to see the SQL that's actually being spit out by Subsonic. I know there was a way to do this with the Query object with Inspect(), but I'm using Select objects (or could also probably use SQLQuerys) because I need joins. Is there any inspect() type option for a Subsonic Select?

Here's the code I'm using:

Dim qry As New [Select]("Contract_NO")
  qry.From(<table1>.Schema)
  qry.InnerJoin(<table2>.<table2columnA>, <table1>.<table1columnA)
  qry.Where(NonInfoleaseLessor.Columns.LessorCode).Like("mystring")

If I comment out the where line, I get a full list of results. It doesn't like something about it, but I've manually run the query at the database with that where clause, and it works. How can I see what the difference is?

Answer 1

It might have something to do with your choice of clause, or which column name you are using. Subsonic has a couple of column name field

OBJECT.xyzColumn

OBJECT.xyzColumn.QualifiedName

OBJECT.Columns.xyz

I have had to play with these in the past to get the values I wanted.

Answer 2

The problem with your query is that you should be using Contains("mystring") instead of Like("mystring").

The best way to see the SQL is to use the BuildSqlStatement() method of the query.

Answer 3

Use [a] profiler to see what SQL is actually being executed against the database.

As Adam spotted:

.Like("mystring")

should most probably be

.Like("%mystring%")

Answer 4

please try using Like("%mystring%")