Transform SQL Query to Method Chain LINQ Syntax

Other Questions Notion
1

I need help converting this SQL statement into method-based LINQ syntax. The query joins two tables and finds duplicate records using a subquery.

SELECT p1.product_id, p1.product_name, s1.serial_code
FROM Product AS p1
INNER JOIN SerialCode AS s1 ON p1.product_id = s1.product_id  
WHERE (s1.serial_code IN
    (SELECT s1_sub.serial_code
     FROM Product AS p1_sub
     INNER JOIN SerialCode AS s1_sub ON p1_sub.product_id = s1_sub.product_id
     GROUP BY s1_sub.serial_code
     HAVING (COUNT(s1_sub.serial_code) > 1)))

I’m looking for the equivalent using dot notation LINQ methods like Join(), Where(), GroupBy(), etc. What would be the proper way to structure this query?

2

you could also do this in one query without splitting - just use Any() to replicate the subquery logic. Something like context.Products.Join(context.SerialCodes, p => p.product_id, s => s.product_id, (p,s) => new {p,s}).Where(ps => context.SerialCodes.GroupBy(sc => sc.serial_code).Any(g => g.Key == ps.s.serial_code && g.Count() > 1)) but honestly the split approach above is way cleaner.

3

Here’s how to convert your SQL query to method-based LINQ - first find the duplicate serial codes, then join back to get the full product details:

var duplicateSerials = context.Products
    .Join(context.SerialCodes, p => p.product_id, s => s.product_id, (p, s) => s.serial_code)
    .GroupBy(serial => serial)
    .Where(g => g.Count() > 1)
    .Select(g => g.Key);

var result = context.Products
    .Join(context.SerialCodes, p => p.product_id, s => s.product_id, (p, s) => new { p, s })
    .Where(ps => duplicateSerials.Contains(ps.s.serial_code))
    .Select(ps => new { ps.p.product_id, ps.p.product_name, ps.s.serial_code });

Splitting the duplicate detection from the final output makes it way more readable and follows the same structure as your SQL query.

4

I hit this exact problem last month. A window function approach works great here. You can do it all in one query with GroupJoin and SelectMany to flatten everything:

var result = context.Products
    .Join(context.SerialCodes, p => p.product_id, s => s.product_id, (p, s) => new { p, s })
    .GroupJoin(
        context.SerialCodes.GroupBy(sc => sc.serial_code).Where(g => g.Count() > 1),
        ps => ps.s.serial_code,
        duplicates => duplicates.Key,
        (ps, duplicates) => new { ps.p.product_id, ps.p.product_name, ps.s.serial_code })
    .SelectMany(x => x);

This mimics SQL structure better since it checks for duplicates inline instead of splitting into separate queries. Performance should be about the same as the split approach, but everything stays in one expression.