Wassim AZIRAR Wassim AZIRAR - 1 month ago 8
C# Question

Get unique row with many conditions

I have a history table of ordered products

+----+------------+--------+
| id | IdProduct | status |
+----+------------+--------+
| 1 | 100 | 1 |
| 2 | 100 | 2 |
| 3 | 100 | 3 |
| | | |
| 4 | 200 | 1 |
| 5 | 200 | 2 |
| | | |
| 6 | 300 | 1 |
| 7 | 300 | 2 |
+----+------------+--------+


I want to get only the products who have the status
2
but not
3


+----+------------+
| id | IdProduct |
+----+------------+
| 5 | 200 |
| | |
| 7 | 300 |
+----+------------+


How can I achieve this using a Linq request

Answer

Using linq to sql you can do:

var result = history.GroupBy(item => item.IdProduct)
                    .Where(grp => grp.Any(item => item.Status == 2) &&
                                 !grp.Any(item => item.Status == 3))
                    .Select(grp => new { 
                        IdProduct = grp.Key, 
                        Id = grp.Max(item => item.Id) 
                    });

Or:

var result = history.GroupBy(item => item.IdProduct)
                    .Where(grp => grp.Any(item => item.Status == 2) &&
                                 !grp.Any(item => item.Status == 3))
                    .Select(grp => grp.Where(item => ite.Status == 2).FirstOrDefault());

In your case both these should return the same because the max(id) correleted with the result you wanted

If you know each status exists only once then you can try the following. The idea is that status 3 items equal -1, 2 equals 1 and the rest 0. Only groups that have status 2 but not 3 will have the result of 1

var result = history.Select(item => new { Item = item, Valid = item.Status == 2 ? 1 : item.Status == 3 ? -1 : 0 })
                    .GroupBy(item => item.Item.IdProduct)
                    .Where(grp => grp.Sum(item => item.Valid) == 1) 
                    .Select(item => item.Item);