CODEWITHSUNDEEP
c#linq-to-sql
sdm350
sdm350

Reputation: 397

LINQ Where in MAX

I currently have this linq statement:

from s in SubContentRevisions
where s.SubContentID.Equals("e3f319f1-65cc-4799-b84d-309941dbc1da")
&& s.RevisionNumber == (SubContentRevisions.Max(s1 => s1.RevisionNumber))
select s

which generates this SQL (according to LINQPad):

-- Region Parameters
DECLARE @p0 UniqueIdentifier = 'e3f319f1-65cc-4799-b84d-309941dbc1da'
-- EndRegion
SELECT [t0].[SubContentRevisionID], [t0].[SubContentID], [t0].[RevisionNumber], [t0].[RevisionText], [t0].[CreatedDate], [t0].[ModifiedDate]
FROM [SubContentRevision] AS [t0]
WHERE ([t0].[SubContentID] = @p0) AND ([t0].[RevisionNumber] = ((
    SELECT MAX([t1].[RevisionNumber])
    FROM [SubContentRevision] AS [t1]
    )))

How can I make it generate this SQL statement? I can't seem to find anything related anywhere. (I need it to add the where clause to the subquery)

-- Region Parameters
DECLARE @p0 UniqueIdentifier = 'e3f319f1-65cc-4799-b84d-309941dbc1da'
-- EndRegion
SELECT [t0].[SubContentRevisionID], [t0].[SubContentID], [t0].[RevisionNumber], [t0].[RevisionText], [t0].[CreatedDate], [t0].[ModifiedDate]
FROM [SubContentRevision] AS [t0]
WHERE ([t0].[SubContentID] = @p0) AND ([t0].[RevisionNumber] = ((
    SELECT MAX([t1].[RevisionNumber])
    FROM [SubContentRevision] AS [t1]
    WHERE [SubContentID] = @p0 -- **********Adds the where clause**********
    )))

Upvotes: 1

Views: 263

Answers (2)

ChrisWue
ChrisWue

Reputation: 19020

How about adding the where clause to the subquery (max):

from s in SubContentRevisions
where s.SubContentID.Equals("e3f319f1-65cc-4799-b84d-309941dbc1da")
   && s.RevisionNumber == (SubContentRevisions
                                   .Where(s1 => s1.SubContentID.Equals(s.SubContentID))
                                   .Max(s1 => s1.RevisionNumber))
select s

Upvotes: 0

Jon Skeet
Jon Skeet

Reputation: 1500215

I think you want:

from s in SubContentRevisions
where s.SubContentID.Equals("e3f319f1-65cc-4799-b84d-309941dbc1da")
  && s.RevisionNumber == (SubContentRevisions.Where(s.SubContentID.Equals("..."))
                                             .Max(s1 => s1.RevisionNumber))
select s

Or, more clearly:

var specificSubContents = SubContentRevisions.Where(s => 
               s.SubContentID.Equals("e3f319f1-65cc-4799-b84d-309941dbc1da")

var query = from s in specificSubContents
            where s.RevisionNumber = s.Max(s1 => s1.RevisionNumber)
            select s;

Alternatively, it sounds like you could actually do:

var latest = (from s in SubContentRevisions
              where s.SubContentID.Equals("e3f319f1-65cc-4799-b84d-309941dbc1da")
              orderby s.RevisionNumber descending
              select s).FirstOrDefault();

Upvotes: 4

Related Questions