Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > LLBLGen Pro > LLBLGen Pro Runtime Framework> Is this is an issue?
 

Pages: 1
LLBLGen Pro Runtime Framework
Is this is an issue?
Page:1/1 

  Print all messages in this thread  
Poster Message
Viresh
User



Location:
Ahmedabad, India
Joined on:
28-Jan-2008 08:52:54
Posted:
31 posts
# 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.
Regards,

Viresh Shah
 
Top
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
14520 posts
# Posted on: 06-Jun-2008 12:04:01.  
Would using CoutRow makes a difference?

Code:
fields.DefineField(StageSongRatingFields.RatingId, 5, "TotalVotes", AggregateFunction.CountRow);


  Top
Viresh
User



Location:
Ahmedabad, India
Joined on:
28-Jan-2008 08:52:54
Posted:
31 posts
# Posted on: 06-Jun-2008 12:10:11.  
No Walaa. It's not making any difference. Sad
Regards,

Viresh Shah
 
Top
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
14520 posts
# Posted on: 06-Jun-2008 15:03:07.  
Which LLBLGen Pro runtime library version are you using?

  Top
Viresh
User



Location:
Ahmedabad, India
Joined on:
28-Jan-2008 08:52:54
Posted:
31 posts
# Posted on: 07-Jun-2008 06:37:16.  
I'm using v2.5.08.0228
Regards,

Viresh Shah
 
Top
goose
User



Location:
Central America
Joined on:
06-Aug-2007 18:21:05
Posted:
386 posts
# Posted on: 07-Jun-2008 21:52:44.  
try forcing the left join this way:
Quote:

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

bucket.Relations[3].CustomFilterReplacesOnClause = true;




gansodesoya  Top
Viresh
User



Location:
Ahmedabad, India
Joined on:
28-Jan-2008 08:52:54
Posted:
31 posts
# 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.
Regards,

Viresh Shah
 
Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37602 posts
# 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
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Viresh
User



Location:
Ahmedabad, India
Joined on:
28-Jan-2008 08:52:54
Posted:
31 posts
# 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.

Regards,

Viresh Shah
 
Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37602 posts
# 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);
?

Quote:

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 Regular Smiley. 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
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Viresh
User



Location:
Ahmedabad, India
Joined on:
28-Jan-2008 08:52:54
Posted:
31 posts
# Posted on: 12-Jun-2008 12:00:30.  
Hi Frans,

Nope. It didn't work.Sad Any other alternatives please.
Regards,

Viresh Shah
 
Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37602 posts
# Posted on: 12-Jun-2008 12:24:42.  
Viresh wrote:
Hi Frans,

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

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



Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Viresh
User



Location:
Ahmedabad, India
Joined on:
28-Jan-2008 08:52:54
Posted:
31 posts
# 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.
Regards,

Viresh Shah
 
Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37602 posts
# 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
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Viresh
User



Location:
Ahmedabad, India
Joined on:
28-Jan-2008 08:52:54
Posted:
31 posts
# 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 seaSad.
Regards,

Viresh Shah
 
Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37602 posts
# Posted on: 12-Jun-2008 13:51:38.  
In v2.5, I can't reproduce it (latest build of runtime lib, latest templates)
Code:

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
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Viresh
User



Location:
Ahmedabad, India
Joined on:
28-Jan-2008 08:52:54
Posted:
31 posts
# 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.
Regards,

Viresh Shah
 
Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37602 posts
# Posted on: 12-Jun-2008 15:22:37.  
Thanks, I'll build the repro with this info and get back to you.

Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37602 posts
# Posted on: 12-Jun-2008 15:39:36.  
I indeed also see the is not null:
Code:

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 Regular Smiley
Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37602 posts
# 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:
Code:
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:

Code:

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
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Viresh
User



Location:
Ahmedabad, India
Joined on:
28-Jan-2008 08:52:54
Posted:
31 posts
# Posted on: 13-Jun-2008 08:16:57.  
Huh..Finally it works. Thanks Frans and team for your efforts. Laugh
Regards,

Viresh Shah
 
Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37602 posts
# Posted on: 13-Jun-2008 08:54:43.  
Glad this works now Viresh Regular Smiley

Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Pages: 1  


Powered by HnD ©2002-2007 Solutions Design
HnD uses LLBLGen Pro

Version: 2.1.12172008 Final.