Joining on to a subquery...
I'm trying to join a table on to a subquery. This subquery returns an ID to join on and another column which is the result of a count aggregate. I cannot just do a simple where IN on the top resultset because I want to order the results by the aggregrate value.
To summarise in English what I want: I'm trying to get the top 5 most viewed albums, ordered by their popularity.
I can't understand the point of the obejctAlias argument of the FieldCompareSetPredicate constructor. If i leave it null it dies with a null ref exception on fetch. I want it to use the alias of "albumId" field but for some reason that field alias is ignored and it uses LPA_A2... plz help!
Here's where i'm at... failed with various "not bound" sql errors.
public static EntityCollection<AlbumEntity> Foo()
{
EntityField2 counter = AuditAlbumFields.AuditId;
counter.AggregateFunctionToApply = AggregateFunction.Count;
//counter.ObjectAlias = "AlbumInfo";
EntityField2 albumId = AuditAlbumFields.AlbumId;
albumId.ObjectAlias = "AlbumInfo";
GroupByCollection groupBy = new GroupByCollection();
groupBy.Add(albumId);
IRelationCollection crap = new RelationCollection();
crap.Add(AuditAlbumEntity.Relations.RelationToSuperTypeAuditEntity);
IPredicateExpression joinFilter = new PredicateExpression(
new FieldCompareSetPredicate(AlbumFields.AlbumId, null, albumId, null, SetOperator.In, AuditAlbumFields.EventOccurred == "VIEWED",
crap, "AlbumInfo", 0, null, false, groupBy)
);
IEntityRelation rel = AlbumEntity.Relations.AuditAlbumEntityUsingAlbumId;
////rel.AliasFKSide = "AlbumInfo";
rel.CustomFilterReplacesOnClause = true;
rel.CustomFilter = joinFilter;
//IEntityRelation rel = AuditAlbumEntity.Relations.AlbumEntityUsingAlbumId;
////rel.AliasFKSide = "AlbumInfo";
//rel.CustomFilterReplacesOnClause = true;
//rel.CustomFilter = joinFilter;
RelationPredicateBucket albumFilter = new RelationPredicateBucket();
albumFilter.Relations.Add(rel, "AlbumInfo", JoinHint.Inner);
SortExpression sorter = new SortExpression(counter | SortOperator.Descending);
EntityCollection<AlbumEntity> albums = new EntityCollection<AlbumEntity>();
using (DataAccessAdapter adapter = new DataAccessAdapter())
{
adapter.FetchEntityCollection(albums, albumFilter, 5, sorter);
}
return albums;
}
The nonfunctioning sql i'm getting atm:
exec sp_executesql N'SELECT [ToxicBeats].[dbo].[Album].[AlbumId], [ToxicBeats].[dbo].[Album].[Name], [ToxicBeats].[dbo].[Album].[Comment], [ToxicBeats].[dbo].[Album].[DateReleased], [ToxicBeats].[dbo].[Album].[Price], [ToxicBeats].[dbo].[Album].[UserId],
[ToxicBeats].[dbo].[Album].[MediaCategoryId], [ToxicBeats].[dbo].[Album].[CoverArtImageId], [ToxicBeats].[dbo].[Album].[IsAvailableForPurchase], [ToxicBeats].[dbo].[Album].[IsActive], [ToxicBeats].[dbo].[Album].[UpdatedBy], [ToxicBeats].[dbo].[Album].[UpdatedOn],
[ToxicBeats].[dbo].[Album].[CreatedBy], [ToxicBeats].[dbo].[Album].[CreatedOn] FROM (( [ToxicBeats].[dbo].[Album] INNER JOIN [ToxicBeats].[dbo].[AuditAlbum] [LPA_A2] ON ( ( [AlbumInfo].[AlbumId] IN (SELECT [LPA_A2].[AlbumId] FROM ( [ToxicBeats].[dbo].[Audit] INNER
JOIN [ToxicBeats].[dbo].[AuditAlbum] ON [ToxicBeats].[dbo].[Audit].[AuditId]=[ToxicBeats].[dbo].[AuditAlbum].[AuditId]) WHERE [ToxicBeats].[dbo].[Audit].[EventOccurred] = @EventOccurred1 GROUP BY [LPA_A2].[AlbumId])))) INNER JOIN [ToxicBeats].[dbo].[Audit] [LPA_A1]
ON [LPA_A1].[AuditId]=[LPA_A2].[AuditId]) ORDER BY AuditId DESC',N'@EventOccurred1 nvarchar(64)',@EventOccurred1=N'VIEWED'
Heres what I want (roughly)
SELECT Album.*
FROM
Album
JOIN (SELECT Count(Audit.AuditId) [Popularity], AuditAlbum.AlbumId [AlbumId] FROM Audit JOIN AuditAlbum ON Audit.AuditID = AuditAlbum.AuditId WHERE Audit.EventOccurred = 'VIEWED' GROUP BY AuditAlbum.AlbumId) [Stuff] ON [STUFF].AlbumId = Album.AlbumId
ORDER BY
[Stuff].Popularity DESC
...and now that I've written that I'm wondering why my code is using a fieldcomparesetpredicate 
Anyway it's late, home time. If someone could help (with code - not pointers to documentation plz) that'd be sweet.
Thanks
