Missing Top clause in ScalarQueryExpression

Posts   
 
    
El Barto
User
Posts: 64
Joined: 09-Nov-2006
# Posted on: 30-Aug-2007 16:53:04   

I'm using the 2.5 adapter version. When I create a dynamic list with 2 fields as scalar queries, I get a SQL error: "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."

The code to genenrate the dynamic list:


            RelationPredicateBucket bucketTip = new RelationPredicateBucket();
            bucketTip.Relations.Add(TipEntity.Relations.TipTextEntityUsingTipId);
            bucketTip.Relations.Add(TipEntity.Relations.AspnetUsersEntityUsingCreator, JoinHint.Left);
            bucketTip.Relations.Add(TipEntity.Relations.CompanyEntityUsingOwnerCompanyId);
            bucketTip.Relations.Add(TipEntity.Relations.TipTypeEntityUsingTipTypeId);
            bucketTip.Relations.Add(TipTypeEntity.Relations.TipTypeTextEntityUsingTipTypeId);
            bucketTip.Relations.Add(TipTypeEntity.Relations.TemplateEntityUsingTemplateId);


            bucketTip.PredicateExpression.Add(TipFields.Id == _tipId);
            bucketTip.PredicateExpression.AddWithAnd(_lang.TipLanguageExpression());
            bucketTip.PredicateExpression.AddWithAnd(_lang.TipTypeLanguageExpression());

            // security check
            if (!Roles.IsUserInRole(App_Code.SecurityGroup.global_reader.ToString())) {
                bucketTip.Relations.Add(TipEntity.Relations.SecurityEntityUsingTipId);
                bucketTip.PredicateExpression.AddWithAnd(new FieldCompareRangePredicate(SecurityFields.Role, null, Roles.GetRolesForUser()));
            }

            ResultsetFields fieldsTip = new ResultsetFields(16);
            fieldsTip.DefineField(TipFields.Id, 0, "TipID");
            fieldsTip.DefineField(TipTextFields.Title, 1, "TipTitle");
            fieldsTip.DefineField(TipTextFields.Body, 2, "TipBody");
            fieldsTip.DefineField(TipFields.Views, 3, "TipViews");
            fieldsTip.DefineField(TipFields.Rating, 4, "TipRating");
            fieldsTip.DefineField(TipFields.Rated, 5, "TipRated");
            fieldsTip.DefineField(TipFields.Ranking, 6, "TipRanking");
            fieldsTip.DefineField(TipFields.CreationDate, 7, "TipCreationDate");
            fieldsTip.DefineField(AspnetUsersFields.UserName, 8, "UserName");
            fieldsTip.DefineField(TipTypeFields.IconUrl, 9, "TipTypeIconUrl");
            fieldsTip.DefineField(TipTypeTextFields.Name, 10, "TipTypeName");
            fieldsTip.DefineField(TipTypeTextFields.Description, 11, "TipTypeDescription");
            fieldsTip.DefineField(TemplateFields.ControlName, 12, "TemplateControlName");
            fieldsTip.DefineField(CompanyFields.Name, 13, "OwnerCompanyName");

            // get last change
            RelationCollection relCol = new RelationCollection();
            relCol.Add(ChangeHistoryEntity.Relations.AspnetUsersEntityUsingCreator);
            
fieldsTip.DefineField(new EntityField2("ChangerUserName", new ScalarQueryExpression(AspnetUsersFields.UserName, (ChangeHistoryFields.TipId == _tipId), relCol, new SortExpression(ChangeHistoryFields.CreationDate | SortOperator.Descending))), 14);
            
fieldsTip.DefineField(new EntityField2("TipChangeDate", new ScalarQueryExpression(ChangeHistoryFields.CreationDate, (ChangeHistoryFields.TipId == _tipId), null, new SortExpression(ChangeHistoryFields.CreationDate | SortOperator.Descending))), 15);


The generated SQL looks like this:


 SELECT DISTINCT 
                     Tip.ID AS TipID, TipText.Title AS TipTitle, TipText.Body AS TipBody, Tip.Views AS TipViews, Tip.Rating AS TipRating, Tip.Rated AS TipRated, 
                      Tip.Ranking AS TipRanking, Tip.CreationDate AS TipCreationDate, aspnet_Users.UserName, TipType.IconUrl AS TipTypeIconUrl, TipTypeText.Name AS TipTypeName, 
                      TipTypeText.Description AS TipTypeDescription, Template.ControlName AS TemplateControlName, Company.Name AS OwnerCompanyName,
                          (SELECT    aspnet_Users.UserName
                            FROM          aspnet_Users INNER JOIN
                                                   ChangeHistory ON aspnet_Users.UserId = ChangeHistory.Creator
                            WHERE     (ChangeHistory.TipID = @TipId1)) AS ChangerUserName,
                          (SELECT    CreationDate
                            FROM          ChangeHistory
                            WHERE     (TipID = @TipId2)) AS TipChangeDate
FROM         Tip INNER JOIN
                      TipText ON Tip.ID = TipText.TipID LEFT OUTER JOIN
                      aspnet_Users ON aspnet_Users.UserId = Tip.Creator INNER JOIN
                      Company ON Company.ID = Tip.OwnerCompanyID INNER JOIN
                      TipType ON TipType.ID = Tip.TipTypeID INNER JOIN
                      TipTypeText ON TipType.ID = TipTypeText.TipTypeID INNER JOIN
                      Template ON Template.ID = TipType.TemplateID
WHERE    (Tip.ID = @Id3) AND (TipText.Language = dbo.GetTipLanguageParameter(TipText.TipID, @LOcdeeb7c24)) AND 
                      (TipTypeText.Language = dbo.GetTipTypeLanguageParameter(TipTypeText.TipTypeID, @LOcdeeb7c25))

Note the last 2 fields in the select list of the query do not contain the TOP 1 clause. I'm affraid it is something that I do wrong but I don't know where to start searching for the problem.

El Barto
User
Posts: 64
Joined: 09-Nov-2006
# Posted on: 30-Aug-2007 20:15:09   

Duh RTFM. sunglasses . It appears to be a new feature in v2.5 for the ScalarQueryExpression constructor to have an extra overload to force a TOP 1.