Using subquery's with MAX function in LINQ

Question

I'm new to Entity Framework, so I'm experiencing a lot of problems converting plain SQL-statements to LINQ. The following example is something I have to do a lot, but I can't seem to find a solution for it ...

SQL Statement:

SELECT * 
  FROM MyTable Table1
 WHERE Table1.Column1 = 1
   AND Table1.Column2 = 2
   AND Table1.SequenceNr = (SELECT MAX(Table2.SequenceNr) 
            FROM MyTable Table2
                  WHERE Table2.Column1 = 1
             AND Table2.Column2 = 2)

==> I have a table (not mine :-)) that contains 3 important columns to link with: 2 numbers, and a sequencenumber (to keep some kind of history). ==> I mostly need the record with the highest sequencenumber ("the last one"), and I solve it with the above query.

But how can I do this in Linq (VB)? I've already got someting like this:

Dim blah = (From oTmp In oDB.MyTable
           Where oTmp.Column1 = 1 And _
                 oTmp.Column2 = 2 And _
                 oTmp.SequenceNr = oDB.MyTable.Max(Function(x) x.SequenceNr)
          Select oTmp).ToList

Anyone any ideas? :)

Greetz, Jim

Answer 1

You just need to add a Where() call to that inner query:

oTmp.SequenceNr = oDB.MyTable.Where(Function(x) x.Column1 = 1 And x.Column2 =2)
                             .Max(Function(x) x.SequenceNr)