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.