- Home
- LLBLGen Pro
- Bugs & Issues
Missing Top clause in ScalarQueryExpression
Posts
Posts: 64
Joined: 09-Nov-2006
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.