LINQ - Nested WHERE IN query

Question

I have the following SQL query and I want to convert it to LINQ expression.

SELECT `a`.*
FROM `action` AS `a`
WHERE `a`.`id` IN (
    SELECT MAX(`a1`.`id`) AS id
    FROM `action` AS `a1`
    GROUP BY `a1`.`column1`
)

I am able to translate inner query with LINQ expression:

IQueryable query = from a1 in db.Action
                   group a1 by new { a1.column1 } into grp_a1
                   select new
                   {
                       id = grp_a1.Max(c => c.id)
                   }

But how to pass the inner query as an input to the outer query?

Answer 1

You can use LINQ .Any() to check whether id is in the array of nested query.

IQueryable query = from a in db.Action
                   where 
                   (
                       from a1 in db.Action
                       group a1 by new { a1.column1 } into grp_a1
                       select new
                       {
                           id = grp_a1.Max(c => c.id)
                       }
                   ).Any(x => x.id == a.id)
                   select a;