how do i write sql query into linq

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')

Hi,

 For an introduction to Linq to SQL refer to http://msdn.microsoft.com/en-us/library/bb425822.aspx#linqtosql_topic3 .

In Sitefinity directly executing queries to the database will not produce the desired results. For the purpose refer to Sitefinity API which is built in the manner of Linq to SQL. Here is a link to Users Api.