C#Subsonic 2.2:多对多关系和分页问题 [英] c# subsonic 2.2 : many-to-many relationship and pagination problem

问题描述

我有3个表格(类别，文章和ArticleCategories). ArticleCategories具有组合的PK和以下几点:

1. ArticleID(PK，FK，整数)
2. 类别ID(PK，FK，整数)

现在，当我使用亚音速分页方法时，我遇到了一些非常奇怪的行为.它返回的记录数量是我期望的两倍(2个PK?).通过完全删除分页或添加称为ArticleCategoryID的PK字段，可以解决此问题.

        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;

上面的代码适用于所有其他表，但是由于多对多关系而失败.我可以求助于添加额外的(和单个)PK字段，但这感觉不对.

也许我在这里遗漏了一些明显的东西，所以我提前对此表示歉意:)

谢谢您的时间 亲切的问候， 标记

解决方案

您也可以在此问题)

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

解决方案

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)

这篇关于C#Subsonic 2.2:多对多关系和分页问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！