Is this is an issue?

Posts   
 
    
Viresh
User
Posts: 31
Joined: 28-Jan-2008
# Posted on: 06-Jun-2008 10:50:45   

Hi,

I'm facing very weired problem while using dynamic list. Following is the code:

 DataAccessAdapter adapter = new DataAccessAdapter();
        ResultsetFields fields = new ResultsetFields(7);

        fields.DefineField(StageSongFields.StageId, 0);
        fields.DefineField(StageSongFields.SongId, 1);
        fields.DefineField(MediaItemFields.Name, 2, "Name");
        fields.DefineField(StageSongFields.IsApproved, 3);
        fields.DefineField(StageSongFields.IsWinner, 4);
        fields.DefineField(StageSongRatingFields.RatingId, 5, "TotalVotes", AggregateFunction.Count);
        fields.DefineField(StageSongRatingFields.Rating, 6, "Total", AggregateFunction.Sum);

        IRelationPredicateBucket bucket = new RelationPredicateBucket();
        bucket.Relations.Add(MediaItemEntity.Relations.SongEntityUsingSongId, JoinHint.Inner);
        bucket.Relations.Add(SongEntity.Relations.StageSongEntityUsingSongId, JoinHint.Inner);
        bucket.Relations.Add(StageSongEntity.Relations.StageEntityUsingStageId, JoinHint.Inner);
        **bucket.Relations.Add(StageSongEntity.Relations.StageSongRatingEntityUsingStageIdSongId, JoinHint.Left);**     

bucket.Relations.Add(StageEntity.Relations.ContestEntityUsingContestId, JoinHint.Inner);

        bucket.PredicateExpression.Add(StageSongFields.StageId == stageId);
        bucket.PredicateExpression.AddWithAnd(MediaItemFields.IsActive == true);
        bucket.PredicateExpression.AddWithAnd(MediaItemFields.IsApproved == true);

        IGroupByCollection groupByClause = new GroupByCollection();
        groupByClause.Add(fields[0]);
        groupByClause.Add(fields[1]);
        groupByClause.Add(fields[2]);
        groupByClause.Add(fields[3]);
        groupByClause.Add(fields[4]);

        ISortClause sortClause = new SortClause(fields[6], null, SortOperator.Descending);
        ISortExpression sortExpression = new SortExpression(sortClause);


        DataTable dynamicList = new DataTable();
        adapter.FetchTypedList(fields, dynamicList, bucket, NumberOfTopItems, sortExpression, true, groupByClause);

From above code you can see that there is a left join between StageSongEntity and StageSongRatingEntity. This left join doesn't have any effect while getting output because of the AND ( ( [LPA_L3].[RatingId] IS NOT NULL))statement appened in generated query by LLBL. Following is the query that is generated using above code and statement that is creating problem has marked in bold.

exec sp_executesql N'SELECT [ToxicBeats].[dbo].[StageSong].[StageId], [ToxicBeats].[dbo].[StageSong].[SongId], [LPA_L1].[Name], [ToxicBeats].[dbo].[StageSong].[IsApproved], [ToxicBeats].[dbo].[StageSong].[IsWinner], COUNT([LPA_L3].[RatingId]) AS [TotalVotes], SUM([LPA_L2].[Rating]) AS [Total] FROM (((((( [ToxicBeats].[dbo].[Song] INNER JOIN [ToxicBeats].[dbo].[MediaItem] [LPA_L1] ON
[ToxicBeats].[dbo].[Song].[SongId]=[LPA_L1].[SongId]) INNER JOIN [ToxicBeats].[dbo].[StageSong] ON
[ToxicBeats].[dbo].[Song].[SongId]=[ToxicBeats].[dbo].[StageSong].[SongId]) INNER JOIN [ToxicBeats].[dbo].[Stage] ON
[ToxicBeats].[dbo].[Stage].[StageId]=[ToxicBeats].[dbo].[StageSong].[StageId]) LEFT JOIN [ToxicBeats].[dbo].[StageSongRating] [LPA_L3] ON
[ToxicBeats].[dbo].[StageSong].[StageId]=[LPA_L3].[StageId] AND [ToxicBeats].[dbo].[StageSong].[SongId]=[LPA_L3].[SongId]) LEFT JOIN [ToxicBeats].[dbo].[Rating] [LPA_L2] ON [LPA_L2].[RatingId]=[LPA_L3].[RatingId]) INNER JOIN [ToxicBeats].[dbo].[Contest] ON
[ToxicBeats].[dbo].[Contest].[ContestId]=[ToxicBeats].[dbo].[Stage].[ContestId]) WHERE ( ( [ToxicBeats].[dbo].[StageSong].[StageId] = @StageId1 AND [LPA_L1].[IsActive] = @IsActive2 AND [LPA_L1].[IsApproved] = @IsApproved3) AND ( ( [LPA_L3].[RatingId] IS NOT NULL))) GROUP BY [ToxicBeats].[dbo].[StageSong].[StageId], [ToxicBeats].[dbo].[StageSong].[SongId], [LPA_L1].[Name], [ToxicBeats].[dbo].[StageSong].[IsApproved], [ToxicBeats].[dbo].[StageSong].[IsWinner] ORDER BY [Total] DESC',N'@StageId1 int,@IsActive2 bit,@IsApproved3 bit',@StageId1=1,@IsActive2=1,@IsApproved3=1

Please give me suggestions to get it resolved and to get my desired output. Any suggestion/help is highly appreciated.

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 06-Jun-2008 12:04:01   

Would using CoutRow makes a difference?

fields.DefineField(StageSongRatingFields.RatingId, 5, "TotalVotes", AggregateFunction.CountRow);
Viresh
User
Posts: 31
Joined: 28-Jan-2008
# Posted on: 06-Jun-2008 12:10:11   

No Walaa. It's not making any difference. cry

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 06-Jun-2008 15:03:07   

Which LLBLGen Pro runtime library version are you using?

Viresh
User
Posts: 31
Joined: 28-Jan-2008
# Posted on: 07-Jun-2008 06:37:16   

I'm using v2.5.08.0228

goose avatar
goose
User
Posts: 392
Joined: 06-Aug-2007
# Posted on: 07-Jun-2008 21:52:44   

try forcing the left join this way:

....., JoinHint.Left).CustomFilter = new PredicateExpression(StageSongRatingFields.StageId = StageSongFields.StageId); //keep adding your expressions here

bucket.Relations[3].CustomFilterReplacesOnClause = true;

Viresh
User
Posts: 31
Joined: 28-Jan-2008
# Posted on: 10-Jun-2008 13:06:55   

Hi,

Sorry, it didn't work. Following is the updated query that was generated using that.

After:

exec sp_executesql N'SELECT [ToxicBeats].[dbo].[StageVideo].[StageId], [ToxicBeats].[dbo].[StageVideo].[VideoId], [LPA_L1].[Name], [ToxicBeats].[dbo].[StageVideo].[IsApproved], [ToxicBeats].[dbo].[StageVideo].[IsWinner], COUNT([LPA_L3].[RatingId]) AS [TotalVotes], SUM([LPA_L2].[Rating]) AS [Total]

FROM (((((( [ToxicBeats].[dbo].[Video] INNER JOIN [ToxicBeats].[dbo].[MediaItem] [LPA_L1] ON
[ToxicBeats].[dbo].[Video].[VideoId]=[LPA_L1].[VideoId]) INNER JOIN [ToxicBeats].[dbo].[StageVideo] ON
[ToxicBeats].[dbo].[Video].[VideoId]=[ToxicBeats].[dbo].[StageVideo].[VideoId]) INNER JOIN [ToxicBeats].[dbo].[Stage] ON
[ToxicBeats].[dbo].[Stage].[StageId]=[ToxicBeats].[dbo].[StageVideo].[StageId]) LEFT JOIN [ToxicBeats].[dbo].[StageVideoRating] [LPA_L3] ON
(** ( [LPA_L3].[StageId] = [ToxicBeats].[dbo].[StageVideo].[StageId])**)) LEFT JOIN [ToxicBeats].[dbo].[Rating] [LPA_L2] ON
[LPA_L2].[RatingId]=[LPA_L3].[RatingId]) INNER JOIN [ToxicBeats].[dbo].[Contest] ON
[ToxicBeats].[dbo].[Contest].[ContestId]=[ToxicBeats].[dbo].[Stage].[ContestId])

WHERE ( ( [ToxicBeats].[dbo].[StageVideo].[StageId] = @StageId1 AND [LPA_L1].[IsActive] = @IsActive2 AND [LPA_L1].[IsApproved] = @IsApproved3) AND ( ( [LPA_L3].[RatingId] IS NOT NULL)))

GROUP BY [ToxicBeats].[dbo].[StageVideo].[StageId], [ToxicBeats].[dbo].[StageVideo].[VideoId], [LPA_L1].[Name], [ToxicBeats].[dbo].[StageVideo].[IsApproved], [ToxicBeats].[dbo].[StageVideo].[IsWinner]

ORDER BY [Total] DESC',N'@StageId1 int,@IsActive2 bit,@IsApproved3 bit',@StageId1=1,@IsActive2=1,@IsApproved3=1

Before:

exec sp_executesql N'SELECT [ToxicBeats].[dbo].[StageVideo].[StageId], [ToxicBeats].[dbo].[StageVideo].[VideoId], [LPA_L1].[Name], [ToxicBeats].[dbo].[StageVideo].[IsApproved], [ToxicBeats].[dbo].[StageVideo].[IsWinner], COUNT([LPA_L3].[RatingId]) AS [TotalVotes], SUM([LPA_L2].[Rating]) AS [Total] FROM (((((( [ToxicBeats].[dbo].[Video] INNER JOIN [ToxicBeats].[dbo].[MediaItem] [LPA_L1] ON
[ToxicBeats].[dbo].[Video].[VideoId]=[LPA_L1].[VideoId]) INNER JOIN [ToxicBeats].[dbo].[StageVideo] ON
[ToxicBeats].[dbo].[Video].[VideoId]=[ToxicBeats].[dbo].[StageVideo].[VideoId]) INNER JOIN [ToxicBeats].[dbo].[Stage] ON
[ToxicBeats].[dbo].[Stage].[StageId]=[ToxicBeats].[dbo].[StageVideo].[StageId]) LEFT JOIN [ToxicBeats].[dbo].[StageVideoRating] [LPA_L3] ON
**[ToxicBeats].[dbo].[StageVideo].[StageId]=[LPA_L3].[StageId] AND [ToxicBeats].[dbo].[StageVideo].[VideoId]=[LPA_L3].[VideoId]) **LEFT JOIN [ToxicBeats].[dbo].[Rating] [LPA_L2] ON [LPA_L2].[RatingId]=[LPA_L3].[RatingId]) INNER JOIN [ToxicBeats].[dbo].[Contest] ON
[ToxicBeats].[dbo].[Contest].[ContestId]=[ToxicBeats].[dbo].[Stage].[ContestId]) WHERE ( ( [ToxicBeats].[dbo].[StageVideo].[StageId] = @StageId1 AND [LPA_L1].[IsActive] = @IsActive2 AND [LPA_L1].[IsApproved] = @IsApproved3) AND ( ( [LPA_L3].[RatingId] IS NOT NULL))) GROUP BY [ToxicBeats].[dbo].[StageVideo].[StageId], [ToxicBeats].[dbo].[StageVideo].[VideoId], [LPA_L1].[Name], [ToxicBeats].[dbo].[StageVideo].[IsApproved], [ToxicBeats].[dbo].[StageVideo].[IsWinner] ORDER BY [Total] DESC',N'@StageId1 int,@IsActive2 bit,@IsApproved3 bit',@StageId1=1,@IsActive2=1,@IsApproved3=1

From the SQLs generated above, the only difference is the clause after AND flag because I added only one expression with LEFT join as you've written. But if I add another expression with AND in predicate it gives same result. i.e.

....., JoinHint.Left).CustomFilter = new PredicateExpression(StageSongRatingFields.StageId = StageSongFields.StageId); //keep adding your expressions here

bucket.Relations[3].CustomFilterReplacesOnClause = true;

I want to achieve this between above joins, if I put one of these problematic join in simple way:

SELECT DISTINCT STAGEVIDEO.VIDEOID FROM STAGEVIDEO LEFT JOIN STAGEVIDEORATING ON STAGEVIDEO.STAGEID=STAGEVIDEORATING.STAGEID **AND ** STAGEVIDEO.VIDEOID=STAGEVIDEORATING.VIDEOID

The only statment that's causing the problem is AND ( ( [LPA_L3].[RatingId] IS NOT NULL)). I don't understand the reason behind appending this statement.

Please suggest me another alternative. Thanks in advance.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 11-Jun-2008 10:39:49   

The IS NOT NULL clause is added because it's a typefilter. You've specified a field in a derived entity in a hierarchy of type TargetPerEntity, and this causes the code to add a type filter, as you want StageSongRating's values only.

If you don't want that, specify the type in which the field is defined in. This means that because it's a PK field, you should specify the supertype's PK field instead of the subtype's PK field at: fields.DefineField(StageSongRatingFields.RatingId, 5, "TotalVotes", AggregateFunction.Count);

THe framework will, with your current code, try to fetch StageSongRatings only, as you specified you wanted THAT entity's value of RatingId. I don't know your exact object model, but I guess RatingId is a PK and therefore the same value as the supertype's (root of the hierarchy) PK.

Could you please elaborate more about the inheritance hierarchies involved?

Frans Bouma | Lead developer LLBLGen Pro
Viresh
User
Posts: 31
Joined: 28-Jan-2008
# Posted on: 11-Jun-2008 14:04:42   

Hi Frans,

Thanks for your reply. You caught it right. There are three tables.

  1. Rating RatingId - PK

  2. StageSong StageId and SongId - PK

  3. StageSongRating inherits Rating RatingId - PK StageId and SongId (Both) - FK to StageSong

I changed according to your suggestion. i.e.

fields.DefineField(RatingFields.RatingId, 5, "TotalVotes", AggregateFunction.Count);

But it's giving The multi-part identifier "ToxicBeats.dbo.Rating.RatingId" could not be bound. as it cannot find Rating.RatingId in relations.

I've not changed anything in relations as I couldn't get Rating table directly. Please correct me.

I really appreciate the response time for this forum and dedication of you guys in solving people's query. Full marks for that. Thanks again.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 11-Jun-2008 19:28:58   

Viresh wrote:

Hi Frans,

Thanks for your reply. You caught it right. There are three tables.

  1. Rating RatingId - PK

  2. StageSong StageId and SongId - PK

  3. StageSongRating inherits Rating RatingId - PK StageId and SongId (Both) - FK to StageSong

I changed according to your suggestion. i.e.

fields.DefineField(RatingFields.RatingId, 5, "TotalVotes", AggregateFunction.Count);

But it's giving The multi-part identifier "ToxicBeats.dbo.Rating.RatingId" could not be bound. as it cannot find Rating.RatingId in relations.

I've not changed anything in relations as I couldn't get Rating table directly. Please correct me.

It's a bit problematic I think without aliases. Could you try: instead of: bucket.Relations.Add(StageSongEntity.Relations.StageSongRatingEntityUsingStageIdSongId, JoinHint.Left);
do: bucket.Relations.Add( StageSongEntity.Relations.StageSongRatingEntityUsingStageIdSongId, "SSR", JoinHint.Left);

and instead of: fields.DefineField(RatingFields.RatingId, 5, "TotalVotes", AggregateFunction.Count); do: fields.DefineField(RatingFields.RatingId.SetObjectAlias("SSR"), 5, "TotalVotes", AggregateFunction.Count);

and instead of: fields.DefineField(StageSongRatingFields.Rating, 6, "Total", AggregateFunction.Sum); do: fields.DefineField(StageSongRatingFields.Rating.SetObjectAlias("SSR"), 6, "Total", AggregateFunction.Sum); ?

I really appreciate the response time for this forum and dedication of you guys in solving people's query. Full marks for that. Thanks again.

Thanks, Viresh simple_smile . Sorry that it still takes so long to get your solution out. With these inheritance hierarchies it's a bit difficult to reproduce the exact same situation.

Frans Bouma | Lead developer LLBLGen Pro
Viresh
User
Posts: 31
Joined: 28-Jan-2008
# Posted on: 12-Jun-2008 12:00:30   

Hi Frans,

Nope. It didn't work.cry Any other alternatives please.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 12-Jun-2008 12:24:42   

Viresh wrote:

Hi Frans,

Nope. It didn't work.cry Any other alternatives please.

What does 'it didn't work' mean? Crash? No data? Same query?

Frans Bouma | Lead developer LLBLGen Pro
Viresh
User
Posts: 31
Joined: 28-Jan-2008
# Posted on: 12-Jun-2008 12:27:35   

Oops Sorry for incomplete answer. Actually It's giving the same result. I'm not getting any error but left join doesn't seem working.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 12-Jun-2008 13:01:30   

Viresh wrote:

Oops Sorry for incomplete answer. Actually It's giving the same result. I'm not getting any error but left join doesn't seem working.

Hmm. The problem is a bit that the code has to filter on a type, but that can only be done by checking if the ID (pk) is null or not. However, that field CAN be null due to the left join. so NULL in this case has 2 meanings, and the filter tries to get rid of 1 to be certain that there's no ambiguity.

This code has been rewritten / refactored in v2.6. I'll see if it works there. I hoped that to trick it with the aliased root PK field it wouldn't emit the filter, but apparently it does.

Frans Bouma | Lead developer LLBLGen Pro
Viresh
User
Posts: 31
Joined: 28-Jan-2008
# Posted on: 12-Jun-2008 13:06:05   

Thanks guys for your efforts. Please let me know once you are finish with that. I'm in the middle of a seacry .

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 12-Jun-2008 13:51:38   

In v2.5, I can't reproduce it (latest build of runtime lib, latest templates)


ResultsetFields fields = new ResultsetFields(2);
fields.DefineField(CompanyCarFields.Brand, 0);
fields.DefineField(BoardMemberFields.Id.SetAggregateFunction(AggregateFunction.Count), 1, "NumberOfBoardMembers");
RelationPredicateBucket filter = new RelationPredicateBucket();
filter.Relations.Add(CompanyCarEntity.Relations.BoardMemberEntityUsingCompanyCarId, JoinHint.Left);

GroupByCollection groupBy = new GroupByCollection();
groupBy.Add(fields[0]);
DataTable results = new DataTable();
adapter.FetchTypedList(fields, results, filter, 0, null, true, groupBy);

BoardMember is a derived type of manager which is a derived type of employee, hierarchy is of targetperentity. No type filter is added: Query: SELECT [InheritanceOne].[dbo].[CompanyCar].[Brand], COUNT([LPA_L3].[BoardMemberID]) AS [NumberOfBoardMembers] FROM ((( [InheritanceOne].[dbo].[CompanyCar] LEFT JOIN [InheritanceOne].[dbo].[BoardMember] [LPA_L3] ON [InheritanceOne].[dbo].[CompanyCar].[CarID]=[LPA_L3].[CompanyCarID]) LEFT JOIN [InheritanceOne].[dbo].[Manager] [LPA_L2] ON [LPA_L2].[ManagerID]=[LPA_L3].[BoardMemberID]) LEFT JOIN [InheritanceOne].[dbo].[Employee] [LPA_L1] ON [LPA_L1].[EmployeeID]=[LPA_L2].[ManagerID]) GROUP BY [InheritanceOne].[dbo].[CompanyCar].[Brand]

so I'm a bit puzzled why it goes wrong in your situation...

Frans Bouma | Lead developer LLBLGen Pro
Viresh
User
Posts: 31
Joined: 28-Jan-2008
# Posted on: 12-Jun-2008 14:19:33   

Okay Frans. Let's make it simple. We'll use only two tables (actuallu it will be 3 as it's inheritance case).

Following is the table structure:

  1. Rating RatingId - int - PK Rating - int

  2. StageSong StageId and SongId - int - PK

  3. StageSongRating inherits Rating RatingId - int - PK and FK to Rating StageId and SongId (Both) - int - FK to StageSong

Following is the updated code:

        DataAccessAdapter adapter = new DataAccessAdapter();
        ResultsetFields fields = new ResultsetFields(3);

        fields.DefineField(StageSongFields.SongId, 0);
        fields.DefineField(StageSongRatingFields.RatingId, 1, "TotalVotes", AggregateFunction.Count);
        fields.DefineField(StageSongRatingFields.Rating, 2, "Total", AggregateFunction.Sum);

        IRelationPredicateBucket bucket = new RelationPredicateBucket();
        bucket.Relations.Add(StageSongEntity.Relations.StageSongRatingEntityUsingStageIdSongId, JoinHint.Left);

        bucket.PredicateExpression.Add(StageSongFields.StageId == stageId);

        IGroupByCollection groupByClause = new GroupByCollection();
        groupByClause.Add(fields[0]);

        ISortClause sortClause = new SortClause(fields[2], null, SortOperator.Descending);
        ISortExpression sortExpression = new SortExpression(sortClause);


        DataTable dynamicList = new DataTable();
        adapter.FetchTypedList(fields, dynamicList, bucket, NumberOfTopItems, sortExpression, true, groupByClause);

Following is the query generated from above code:

exec sp_executesql N'SELECT [ToxicBeats].[dbo].[StageSong].[SongId], COUNT([LPA_L2].[RatingId]) AS [TotalVotes], SUM([LPA_L1].[Rating]) AS [Total]

FROM (( [ToxicBeats].[dbo].[StageSong] LEFT JOIN [ToxicBeats].[dbo].[StageSongRating] [LPA_L2] ON
[ToxicBeats].[dbo].[StageSong].[StageId]=[LPA_L2].[StageId] AND [ToxicBeats].[dbo].[StageSong].[SongId]=[LPA_L2].[SongId]) LEFT JOIN [ToxicBeats].[dbo].[Rating] [LPA_L1] ON [LPA_L1].[RatingId]=[LPA_L2].[RatingId]) WHERE ( ( [ToxicBeats].[dbo].[StageSong].[StageId] = @StageId1) AND ( ( [LPA_L2].[RatingId] IS NOT NULL))) GROUP BY [ToxicBeats].[dbo].[StageSong].[SongId] ORDER BY [Total] DESC',N'@StageId1 int',@StageId1=1

It's not making any difference.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 12-Jun-2008 15:22:37   

Thanks, I'll build the repro with this info and get back to you.

Frans Bouma | Lead developer LLBLGen Pro
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 12-Jun-2008 15:39:36   

I indeed also see the is not null:


SELECT [TestDBLLBLGen].[dbo].[StageSong].[SongId], COUNT([LPA_L2].[RatingId]) AS [TotalVotes], SUM([LPA_L1].[Rating]) AS [Total] FROM (( [TestDBLLBLGen].[dbo].[StageSong]  LEFT JOIN [TestDBLLBLGen].[dbo].[StageSongRating] [LPA_L2]  ON  [TestDBLLBLGen].[dbo].[StageSong].[StageId]=[LPA_L2].[StageId] AND [TestDBLLBLGen].[dbo].[StageSong].[SongId]=[LPA_L2].[SongId]) LEFT JOIN [TestDBLLBLGen].[dbo].[Rating] [LPA_L1]  ON  [LPA_L1].[RatingId]=[LPA_L2].[RatingId]) WHERE ( ( [TestDBLLBLGen].[dbo].[StageSong].[StageId] = @StageId1) AND ( ( [LPA_L2].[RatingId] IS NOT NULL))) GROUP BY [TestDBLLBLGen].[dbo].[StageSong].[SongId] ORDER BY [Total] DESC

I'll see how I can get rid of that simple_smile

Frans Bouma | Lead developer LLBLGen Pro
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 12-Jun-2008 15:45:24   

Got it.

Instead of: fields.DefineField(StageSongRatingFields.RatingId, 1, "TotalVotes", AggregateFunction.Count); fields.DefineField(StageSongRatingFields.Rating, 2, "Total", AggregateFunction.Sum);

do: fields.DefineField(RatingFields.RatingId.SetObjectAlias("R"), 1, "TotalVotes", AggregateFunction.Count); fields.DefineField(RatingFields.Rating.SetObjectAlias("R"), 2, "Total", AggregateFunction.Sum);

and instead of: bucket.Relations.Add(StageSongEntity.Relations.StageSongRatingEntityUsingStageIdSongId, JoinHint.Left);

do: bucket.Relations.Add(StageSongEntity.Relations.StageSongRatingEntityUsingStageIdSongId, "R", JoinHint.Left);

this gives the right results:

SELECT [TestDBLLBLGen].[dbo].[StageSong].[SongId], COUNT([LPA_R1].[RatingId]) AS [TotalVotes], SUM([LPA_R1].[Rating]) AS [Total] FROM (( [TestDBLLBLGen].[dbo].[StageSong]  LEFT JOIN [TestDBLLBLGen].[dbo].[StageSongRating] [LPA_R2]  ON  [TestDBLLBLGen].[dbo].[StageSong].[StageId]=[LPA_R2].[StageId] AND [TestDBLLBLGen].[dbo].[StageSong].[SongId]=[LPA_R2].[SongId]) LEFT JOIN [TestDBLLBLGen].[dbo].[Rating] [LPA_R1]  ON  [LPA_R1].[RatingId]=[LPA_R2].[RatingId]) WHERE ( ( [TestDBLLBLGen].[dbo].[StageSong].[StageId] = @StageId1)) GROUP BY [TestDBLLBLGen].[dbo].[StageSong].[SongId] ORDER BY [Total] DESC

Complete routine:


DataAccessAdapter adapter = new DataAccessAdapter();
ResultsetFields fields = new ResultsetFields(3);

fields.DefineField(StageSongFields.SongId, 0);
fields.DefineField(RatingFields.RatingId.SetObjectAlias("R"), 1, "TotalVotes", AggregateFunction.Count);
fields.DefineField(RatingFields.Rating.SetObjectAlias("R"), 2, "Total", AggregateFunction.Sum);

IRelationPredicateBucket bucket = new RelationPredicateBucket();
bucket.Relations.Add(StageSongEntity.Relations.StageSongRatingEntityUsingStageIdSongId, "R", JoinHint.Left);

bucket.PredicateExpression.Add(StageSongFields.StageId == 1);

IGroupByCollection groupByClause = new GroupByCollection();
groupByClause.Add(fields[0]);

ISortClause sortClause = new SortClause(fields[2], null, SortOperator.Descending);
ISortExpression sortExpression = new SortExpression(sortClause);

DataTable dynamicList = new DataTable();
adapter.FetchTypedList(fields, dynamicList, bucket, 0, sortExpression, true, groupByClause);

foreach(DataRow row in dynamicList.Rows)
{
    Console.WriteLine("{0} | {1} | {2}", row[0], row[1], row[2]);
}

The changes I made have the result that there's no direct reliance to StageSongRating in the selectlist (resultsetfields) and therefore no need to add the typefilter.

Frans Bouma | Lead developer LLBLGen Pro
Viresh
User
Posts: 31
Joined: 28-Jan-2008
# Posted on: 13-Jun-2008 08:16:57   

Huh..Finally it works. Thanks Frans and team for your efforts. smile

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 13-Jun-2008 08:54:43   

Glad this works now Viresh simple_smile

Frans Bouma | Lead developer LLBLGen Pro