CODEWITHSUNDEEP
c#sqllinqt-sqllambda
Scho
Scho

Reputation: 371

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

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( );

Upvotes: 0

Views: 325

Answers (1)

Mehrdad Dowlatabadi
Mehrdad Dowlatabadi

Reputation: 1335

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);

Upvotes: 3

Related Questions