Posted
on Feb 7, 2012
(permalink)
Hi all,
i am facing problem doing group on table how can i write this sql query into linq c#
select
UserProfile.ID,
UserProfile.FirstName+' '+UserProfile.LastName UserName,
IsNull(CurrentBid.[Current],0)CurrentBid,
IsNull(WinnerBid.Winner,0)WinnerBid,
IsNull(LoseBid.Lose,0)LoseBid,
IsNull(CancelledBid.Cancel,0)CancelledBid
from UserProfile
left join
(
select count(BidTransactions.ID) [Current],UserID
from BidTransactions where
IsCloseBid= null or IsCloseBid = 0
group by UserID)CurrentBid on CurrentBid.UserID = UserProfile.ID
left Join (
select count(BidTransactions.ID) [Winner] ,UserID
from BidTransactions where
IsCloseBid= 1 AND IsWinnerBid = 1
group by UserID)WinnerBid on WinnerBid.UserID = CurrentBid.UserID
left Join(
select count(BidTransactions.ID) [Lose],UserID
from BidTransactions where
IsCloseBid= 1 AND IsWinnerBid = 0
group by UserID)LoseBid on LoseBid.UserID = CurrentBid.UserID
left join(
select count(BidTransactions.ID) [Cancel],UserID
from BidTransactions where
IsCancelled = 1
group by UserID) CancelledBid on CancelledBid.UserID = CurrentBid.UserID
where UserProfile.RoleName in ('Sellers','Bidders')