CODEWITHSUNDEEP
linq-to-sqlt-sqlgroup-by
Paul Rowland
Paul Rowland

Reputation: 8352

Convert a Sql query with group by clause and/or aggregate function into linq to sql?

I have a table Auditing logins, I'd like to just extract the latest login for each user, which the below sql accomplishes.

How would I format the sql query into a linq to sql query?

SELECT * FROM AuditTable adt1
    inner join UserTable usr
        on adt1.[UserName] = usr.[User_Id]
WHERE [TimeStamp] = ( SELECT MAX([TimeStamp])
                      FROM AuditTable adt2
                      WHERE adt1.UserName = adt2.UserName)

Another way of writing the query in sql being;

SELECT adt1.[UserName], usr.First_Name, max([TimeStamp]) as [TimeStamp]
FROM AuditTable adt1
    INNER JOIN UserTable usr
        on adt1.[UserName] = usr.[User_Id]
GROUP BY adt1.[UserName] ,usr.First_Name

Upvotes: 0

Views: 950

Answers (2)

Paul Rowland
Paul Rowland

Reputation: 8352

I got it working... the answer was to use the let keyword.

as an aside I highly recommend using LinqPad when trying to work these things out.

from adt in AuditTable
    join usr in UserTable
        on adt.UserName equals usr.User_Id
group adt by 
    new { adt.UserName, adt.Roles, usr.First_Name, usr.Last_Name } 
into g
let LastAccessed = g.Max(a => a.TimeStamp)
select new 
    { UserName = g.Key.UserName,
      Roles = g.Key.Roles,
      FirstName = g.Key.First_Name,
      LastName = g.Key.Last_Name,
      TimeStamp = LastAccessed
    }

Upvotes: 0

KristoferA
KristoferA

Reputation: 12397

query #2:
from adt1 in dc.AuditTable
join usr in dc.UserTable on adt1.UserName == usr.UserID
group adt1 by adt1.username int ag
select new { UserName = ag.Key, TSMax = ag.Max(ts => adt1.TimeStamp) }

Why the join to the user table? Filter?

Upvotes: 2

Related Questions