Having trouble getting this query into Linq

jwcoleman87

Well-known member
Joined
Oct 4, 2014
Messages
124
Programming Experience
Beginner
VB.NET:
Select TTUser.Cell, count(distinct serialNumber) as Total From TTRepairhistory
join TTUser on ttUser.TTUserID = TTRepairHistory.UserID
where date > '12/5/2014'
and action = 'Final Test'
group by Cell
order by  Total DESC

Here is what I'm doing right now, but unfortunately I need distinct serial numbers
            Dim FinishedRepairs = (From t In db.TTRepairHistories _
                                   Join u In db.TTUsers On u.TTUserID Equals t.UserID
                                   Where t.Action = "Final Test"
                                   Where t.Date > DateTime.Today() _
                                   Group t By u.Cell Into Count() _
                                   Order By Count Descending)


I'm not sure how to translate the SQL I wrote into the Linq statement.

Can someone help me?
 
Fortunately I've been tenacious enough to research these things on my own.

I think my solution is to create a query for distinct serial numbers going to final test, and then performing a lambda for count on that query.
 
If I double up the queries, this turns out a successful solution:

            Dim distinctRepairs = (From t In db.TTRepairHistories _
                                  Where t.Action = "Final Test" _
                                  Where t.Date > "11/12/2014" _
                                  Select t.UserID, t.SerialNumber Distinct)

            Dim RepairsByCell = From t In distinctRepairs _
                                Join r In db.TTUsers On r.TTUserID Equals t.UserID
                                Where r.Cell = (From user In db.TTUsers Where user.TTUserID = TriageTech Select user.Cell).FirstOrDefault
                                Group t By t.UserID Into Count()
 
Back
Top