Transform SQL Query to Method-Chain LINQ Syntax

Other Questions Notion
1

I need help converting this SQL statement into method syntax LINQ. The query joins two tables and finds duplicate records using a subquery with GROUP BY and HAVING clauses.

SELECT p1.product_id, p1.product_name, s1.batch_code
FROM Product AS p1
INNER JOIN BatchSerial AS s1 ON p1.product_id = s1.product_id  
WHERE (s1.batch_code IN
    (SELECT s2.batch_code
     FROM Product AS p2
     INNER JOIN BatchSerial AS s2 ON p2.product_id = s2.product_id
     GROUP BY s2.batch_code
     HAVING COUNT(s2.batch_code) > 1))

I’m struggling with the nested subquery part and how to structure the joins properly in LINQ method syntax. Any guidance would be appreciated!

2

Your subquery pattern actually translates pretty well to method chaining once you get the structure down. Skip the two separate queries - you can handle this with one chain using Any() to replicate that IN clause:

var result = context.Products
    .Join(context.BatchSerials, p => p.product_id, s => s.product_id, (p, s) => new { p, s })
    .Where(x => context.Products
        .Join(context.BatchSerials, p2 => p2.product_id, s2 => s2.product_id, (p2, s2) => s2.batch_code)
        .GroupBy(bc => bc)
        .Where(g => g.Count() > 1)
        .Any(g => g.Key == x.s.batch_code))
    .Select(x => new { x.p.product_id, x.p.product_name, x.s.batch_code });

This mirrors your SQL structure more directly. The nested query becomes your Where() condition, and Any() replaces the IN operator. Won’t be as fast as the two-step approach, but it keeps your original query logic intact.

3

you could also do it in one query with GroupJoin and SelectMany if you want. something like context.Products.Join(context.BatchSerials...).GroupBy(x => x.s.batch_code).Where(g => g.Count() > 1).SelectMany(g => g) but honestly the two-step approach is way cleaner.

4

Break this conversion down into logical steps. First identify the duplicated batch codes, then use that to filter your main query.

Here’s my approach:

var duplicateBatchCodes = context.Products
    .Join(context.BatchSerials, p => p.product_id, s => s.product_id, (p, s) => s.batch_code)
    .GroupBy(bc => bc)
    .Where(g => g.Count() > 1)
    .Select(g => g.Key);

var result = context.Products
    .Join(context.BatchSerials, p => p.product_id, s => s.product_id, (p, s) => new { p, s })
    .Where(x => duplicateBatchCodes.Contains(x.s.batch_code))
    .Select(x => new { x.p.product_id, x.p.product_name, x.s.batch_code });

Separating complex subqueries into their own variables makes the code way more readable and easier to debug. The Join method takes outer sequence, inner sequence, outer key selector, inner key selector, and result selector as parameters.