LINQ - Nested WHERE IN query-CodePudding

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?

CodePudding user response：

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;