SubSonic .Paged() query returns duplicate records

Question

Using a SubSonic (2.2) SqlQuery object, I am querying a view that contains distinct rows from another table. The results of the query, however, contain multiple rows for certain rows in the view. It appears to be because of a join on a temporary table in the query generated to achieve paging. How can I avoid this duplication of rows?

Bonus points: I have to use the view because SubSonic can't do .Paged() and .Distinct() at the same time. Why not?

Answer 1

If I remember correctly you have to use distinct on the right position.

var query = DB.Select().From<Products>()
              .Where(Products.CategoryColumn).IsEqualTo(5).Distinct();

var query = DB.Select().Distinct().From<Products>()
              .Where(Products.CategoryColumn).IsEqualTo(5);

Both statements compile but the first generates invalid sql code. A good starting point for debugging SubSonic SqlQueries is to generate the output:

var sql = query.BuildSqlStatement();

Another solution could be to use Group instead of distinct so you can avoid the view in the first place.