Wassim AZIRAR Wassim AZIRAR - 1 year ago 44
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 Source

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