Converting SQL to LINQ expression

Question

I have a puzzling problem I am trying to solve. I have a list of users (Identities table) that is related through a foreign key to a Sessions table (1 to 0/many)

What I am trying to do is find the most recent closed session for a user. Normally I figure out the SQL first, then try to translate into LINQ. I am FAR from an expert in LINQ, and my latest query is giving me fits.

Here is the SQL I am working with.

IdentityId is the PK in the Identities table - the FK to the Sessions table

        select s.* from Identities i 
        join Session s on s.IdentityID = i.IdentityID 
        where s.ID in
           (select top 1 ID from Session a 
              where a.IdentityID = s.IdentityID 
              order by a.CreateDate desc
           )

This gives me exactly what I want; 1 session row per identity row AND, it is the most recent session (CreateDate is the most recent)

I select the results into a DTO class to return.AsQueryable to the client. Using Silverlight unfortunately... My select statement instead of select * would be — Brian Ross, Feb 20 '15 at 17:25
select new IdentityLastLogin_DTO { IdentityID = i.IdentityID, IdentityName = i.LastName + ", " + i.FirstName, LastLogin = s.CreateDate, LastLoginEndDate = s.EndDate != null ? s.EndDate : null, LastMachine = s != null ? s.ClientHostname : null }) Sorry - hit Enter before I waqs done... — Brian Ross, Feb 20 '15 at 17:26
"This gives me exactly what I want" - I fail to see a question, or even a problem then. Perhaps a *problem description* or *actual question* could be added .. — user2864740, Feb 20 '15 at 17:42
my problem is that it needs to be in LINQ and I am not that good at LINQ. Basic selects and filters I can do, but this type of subselect is way beyond my expertise. — Brian Ross, Feb 20 '15 at 18:01

score 0 · Answer 1 · answered Feb 20 '15 at 17:25

Think you want a LET clause. Try something like:

from s in Session
group s by s.IdentityId into grp
let MaxSession = grp.Max(o => o.CreateDate)
from i in Identities
join s in grp on { i.IdentityId, MaxSession } equals { s.SessionId, s.CreateDate }
select i, s

score 0 · Answer 2 · answered Feb 20 '15 at 17:40

If I'm not mistaken, your SQL query can be re-written as follows:

SELECT s.*
FROM Session s
    Join 
    (
        SELECT IdentityId, MAX(CreateDate) as CreateDate
        FROM Session
        GROUP BY IdentityId
    ) a ON a.IdentityId = s.IdentityId

...which can be written like this in LINQ method syntax:

sessions.GroupBy(s => s.ID)
    .Select(g => g.OrderByDescending(a => a.CreateDate).First());

score -2 · Accepted Answer · edited May 23 '17 at 11:50

-2

Linqer would possibly fix it.

See this closed thread about converting sql-to-linq.

edited May 23 '17 at 11:50

Community

1
1

answered Feb 20 '15 at 17:38

Gustavo Rego

335
4
13

Converting SQL to LINQ expression

3 Answers3