.NET Where() Usage

beerygaz

I'm trying to build a table with duplicate records. My SQL statement reads:

(SELECT COUNT(*), [CameraID] 
FROM[dbo].[Camera] 
GROUP BY[CameraID] 
HAVING COUNT(*) > 1)

I have added this using the suggested syntax in the manual

.Where( q => q.Where("Camera.cameraid","<above sql statement>","=",false)

The page throws the following DataTables error:

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS

I'm afraid I don't know what that means - any help would be welcome

tangerine

That's a SQL error. You should try Googling it for more specialist info.

allan

Try using IN instead of = since you are using a sub select:

.Where( q => q.Where("Camera.cameraid","<above sql statement>","IN",false)

See also the documentation here which contains a similar example.

Allan

beerygaz

@tangerine - You're correct, thank you. I've looked into it further.

Because I can't see the whole SQL statement that is constructed it makes it hard to troubleshoot so I ended up creating an external function that just returns the results and passes them to a simple .Where method.

allan

If you enable the debug option by adding .Debug( true ) into the Editor chain (usually just before the .Process(...) method call, the JSON return from the server will include the generated SQL.

Allan