c# subsonic 2.2 : many-to-many relationship and pagination problem

Question

i have 3 tables (Categories, Articles and ArticleCategories). The ArticleCategories has a combined PK and concists of:

1. ArticleID (PK, FK, int)
2. CategoryID (PK, FK, int)

Now when I'm using the subsonic paging methods I'm running into some very strange behaviour. It returns double the amount of records then I expect (the 2 PK's?). The issue is resolved by either removing the paging completely or by adding a PK field called ArticleCategoryID.

        SubSonic.Query qry = new SubSonic.Query(DAL.ArticleCategory.Schema);
        qry.AddWhere(DAL.ArticleCategory.Columns.CategoryID, filterid);

        //Pagesize
        qry.PageSize = Classes.Settings.PageSizes.GetAdminPageSize();

        //Pageindex
        if (pageindex > 0)
            qry.PageIndex = pageindex;
        else
            qry.PageIndex = 1;

The above code works for all other tables but it fails with a many-to-many relationship. I could resort to adding the extra (and single) PK field but that doesn't feel right.

Maybe I'm missing something obvious here so I apologize for that in advance :)

Thank you for your time Kind regards, Mark

Answer 1

You may also read the answer of Rob Conery in this question - it's about SubSonic 3, but since it says that Rob thinks a many-to-many table should have a single column primary key to identify uniquely a row in any table, I don't think he implemented composite key support in SubSonic 2.

I personally always add a PK column to many-to-many tables because it simplifies a lot of the CRUD code. Feels right to me - and if you are able to do so, I recommend to go for it.

I couldn't find the official documentation, but I also remember (and ran into problems...) that one should always use a PK column as the first column in a view, otherwise paging does not work correctly.

The paging problem may also depend on the database version. The paging implementation with SQL Server 2000 for example is a hack (table variables etc) due to the lack of appropriate instructions in SQL Server. Later DB versions offer better ways to implement paging and may not depend on a single column PK.

Last of all, not really about this question, and again a personal preference: I try to avoid SubSonic.Query and use SubSonic.SQLQuery instead. SqlQuery was added later on and is better than Query (see also this question)