CODEWITHSUNDEEP
c#sqllinqlinq-to-sqllinq-to-entities
Uwe Becker
Uwe Becker

Reputation: 123

Convert T-SQL statement into LINQ expression

i am quite new to linq and actually fighting to convert the following t-sql statement into an linq to sql or linq to entities expression:

SELECT TOP 1
    vm.EventID,
    vmm.VotingMatrixID,
    PersonAcceptedCount = SUM( CAST( vmm.IsAccepted AS INT) )

FROM    VotingMatrixMember vmm

JOIN    VotingMatrix vm
    ON vmm.VotingMatrixID = vm.ID

WHERE vm.EventID = 'abbe3077-24de-45d8-ac04-13dba97c1567'
    AND vm.Deleted = 0
    AND vmm.Deleted = 0

GROUP BY vm.EventID, vmm.VotingMatrixID

ORDER BY PersonAcceptedCount DESC

Upvotes: 1

Views: 357

Answers (2)

TriV
TriV

Reputation: 5148

Try this, can not test

        var firstItem = (
                        from vmm in db.VotingMatrixMember
                        join vm in db.VotingMatrix on vmm.VotingMatrixID equals vm.ID
                        where vm.EventID =  "abbe3077-24de-45d8-ac04-13dba97c1567"
                                                && vm.Deleted = 0
                                                && vmm.Deleted = 0
                        group new {vm, vmm} by new {vm.EventID, vmm.VotingMatrixID} into gr
                        select new
                        {
                            EventID = gr.Key.EventID,
                            VotingMatrixID = gr.Key.VotingMatrixID,
                            PersonAcceptedCount = gr.Sum(x => Convert.ToInt32(x.IsAccepted))
                        } 
                        into groupedItem 
                        orderby  groupedItem.PersonAcceptedCount descending 
                        select groupedItem                            
                         ).FirstOrDefault();

Upvotes: 1

Amy B
Amy B

Reputation: 110121

var query =
  from vm in dataContext.VotingMatrices
  where vm.EventId == eventId
  where vm.Deleted == 0
  from vmm in vm.VotingMatrixMembers
  where vmm.Deleted == 0
  group vmm by new { vm.EventId, vmm.VotingMatrixId } into g
  select new
  {
    g.Key.EventId,
    g.Key.VotingMatrixId,
    PersonAcceptedCount: g.Select(x => Convert.ToInt32(x.IsAccepted)).Sum()
  } into resultRow
  order by resultRow.PersonAcceptedCount descending
  select resultRow;

var row = query.FirstOrDefault();

Upvotes: 0

Related Questions