Use Linq to remove dulicates that vary by one column value

Question

I have a list, ApplesList

AppleId    Color
----------------
1          Green
1          Red
2          Red
3          Green
4          Red
4          Green

And would like to combine the list like so:

AppleId    Green   Red
------------------------
1           1       1
2           0       1
3           1       0
4           1       1

The duplicate AppleId records have been removed and the Color column has been split into two Boolean columns. How would this be done in Linq?

Edit What if I had another value I wanted to keep from the first list? So the first table would now be like this:

AppleId    Color     Value
-----------------------------
1          Green       Value1
1          Red         Value1
2          Red         Value2
3          Green       Value3
4          Red         Value4
4          Green       Value4

and the grouped table would be like this:

AppleId    Green   Red     Value
-----------------------------------
1           1       1       Value1
2           0       1       Value2
3           1       0       Value3
4           1       1       Value4

I tried using

var q = items.GroupBy(x => x.Id)
             .Select(group => new 
                     {
                         Id = group.Key, 
                         Green = group.Any(item => item.Color == "Green"), 
                         Red = group.Any(item => item.Color == "Red"),
                         Value = x.Value
                     });

and some other variations, but couldn't get it to work.

Have you tried anything yet? Have you got this as a SQL query? — tbddeveloper, Sep 08 '14 at 18:24
I tried to create two separate lists and then join them, but I didn't get very far. — navig8tr, Sep 08 '14 at 18:27

score 4 · Accepted Answer · edited Sep 08 '14 at 18:40

4

Using linq group by:

var items = new List<Item>
{
    new Item {Color = "Green", Id = 1},
    new Item {Color = "Red", Id = 1},
    new Item {Color = "Red", Id = 2},
    new Item {Color = "Green", Id = 3},
    new Item {Color = "Red", Id = 4},
    new Item {Color = "Green", Id = 4},
};

var q = items.GroupBy(x => x.Id)
             .Select(group => new 
                     {
                         Id = group.Key, 
                         Green = group.Any(item => item.Color == "Green"), 
                         Red = group.Any(item => item.Color == "Red") 
                     });

edited Sep 08 '14 at 18:40

ppalms

394
2
7
19

answered Sep 08 '14 at 18:27

brz

5,926
1
18
18

I think OP wants the counts, not a boolean "Any". – Asad Saeeduddin Sep 08 '14 at 18:30
From question: Color column has been split into two Boolean columns. – brz Sep 08 '14 at 18:32
Yeah you're right. +1 then, not sure why you got downvoted. – Asad Saeeduddin Sep 08 '14 at 18:32

Use Linq to remove dulicates that vary by one column value

1 Answers1