How to convert this SQL query to LINQ or Lambda expression in C#?

Question

I have the following simple table:

table : Inventory
+-------+-----------+-----------+
|   Id  | ProductId |   cost    |
+-------+-----------+-----------+
|   1   |   1       |     10    |
|   2   |   2       |     55    |
|   3   |   1       |     42    |
|   4   |   3       |     102   |
|   5   |   2       |     110   |
+-------+-----------+-----------+

I have the following SQL query:

SELECT T.Id
FROM Inventory AS T INNER JOIN
                             (SELECT ProductId
                               FROM Inventory
                               GROUP BY ProductId
                               HAVING (COUNT(*) > 1)) AS S ON T.ProductId = S.ProductId

This works to give me all of the Ids where a duplicate ProductId exists. Using the above table, this query would return Ids { 1,2,3,5 }, which is exactly what I want.

I tried converting this into a Lambda expression, but it continually fails with the join. Can anyone get me started and point me in the right direction to write this expression?

This is what I have tried:

var q = inventory.Join( inventory.GroupBy( o => o.ProductId ).Where( o => o.Count( ) > 1 ), g => g.ProductId, gb => gb.Key, ( g, gb ) => g.Id ).ToList( );

LINQ is not a SQL replacement. It's a query language used on top of ORMs. ORMs in turn are not meant for reporting queries like this one. Which ORM are you using anyway? — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Feb 6, 2019 at 13:36
Finding duplicates in T-SQL is far easier using COUNT() OVER (PARTITION BY ProductID) or ROW_NUMBER() OVER (PARTITION BY ProductID ..) — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Feb 6, 2019 at 13:37
Don't know why you need both a GroupBy and Join. You can use just the GroupBy. — jdweng
– jdweng, Commented Feb 6, 2019 at 13:41
You're right. The join was the issue. I was mistakenly attempting to literally adapt T-SQL to LINQ. — Scho
– Scho, Commented Feb 6, 2019 at 13:57

Dmitrii Bychenko · Accepted Answer · 2019-02-06 14:30:19Z

3

You need to use somthing like this:

 var result = Inventory
   .GroupBy(x => x.ProductId)
   .Where(x => x.Count() > 1)
   .SelectMany(x => x.ToList())
   .Select(x => x.Id);

edited Feb 6, 2019 at 14:30

Dmitrii Bychenko

188k20 gold badges178 silver badges231 bronze badges

answered Feb 6, 2019 at 13:39

Mehrdad Dowlatabadi

1,3352 gold badges10 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

juharr Over a year ago

You don't need that ToList inside of the SelectMany.

Scho Over a year ago

Thanks @Mehrdad Dowlatabadi. You saved me a headache.

Mehrdad Dowlatabadi Over a year ago

@juharr Right, thank you for the point. np,glad i could help.

Collectives™ on Stack Overflow

How to convert this SQL query to LINQ or Lambda expression in C#?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related