[SOLVED] LINQ – Nested WHERE IN query

Issue

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?

Solution

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;

Answered By – Yong Shun

Answer Checked By – Mildred Charles (BugsFixing Admin)

Leave a Reply

Your email address will not be published. Required fields are marked *