Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Group data having unique keys and values

I have a following set of numbers:

1  137
1  143
11 37
11 46
11 132
46 65
46 139
69 90

Now, I need to group the data by the first value in a way that no group key is present in the group values. So, for instance, if I were to simply group data, I'd get this result:

1  137
   143
11 37
   46
   132
46 65
   139
69 90

46 here is a group key in the third group and a group value in the second group. In this case I need to merge the group values of the third group into a second group and remove the third group. The end result of the grouping should look like this:

1  137
   143
11 37
   46
   132
   65
   139
69 90

I'm relatively new to C#, so I was wondering if there's a fancy way to do it using LINQ.

like image 738
Cryman Avatar asked Nov 06 '17 13:11

Cryman


People also ask

What is a unique key in SQL?

A unique key is a constraint in SQL which helps in uniquely identifying a record in the data table. It is can be considered somewhat similar to the Primary key as both of them guarantees the uniqueness of a record. But unlike primary key, a unique key can accept NULL values and it can be used on more than one column of the data table.

How to count the number of unique values in a group/column?

It will give the unique values present in that group/column. For counting the number of unique values, we have to first initialize the variable let named as ‘count’ as 0, then have to run the for loop for ‘unique_values’ and count the number of times loop runs and increment the value of ‘count’ by 1

What is the difference between unique key and primary key?

It is can be considered somewhat similar to the Primary key as both of them guarantees the uniqueness of a record. But unlike primary key, a unique key can accept NULL values and it can be used on more than one column of the data table.

What is the difference between foreign keys and unique keys?

That’s true for Foreign Keys as well, because Foreign Keys are just Primary Keys that are being referenced in a table other than the one they originate in. Each table can only have one Primary Key, but there can be more than one of both Foreign and Unique Keys. Unique Keys are permitted to have one missing value, but not more than that.


1 Answers

Try this LINQ solution:

var numbers = new List<Number>
{
    new Number {X = 1, Y = 137},
    new Number {X = 1, Y = 143},
    new Number {X = 11, Y = 37},
    new Number {X = 11, Y = 46},
    new Number {X = 11, Y = 132},
    new Number {X = 46, Y = 65},
    new Number {X = 46, Y = 139},
    new Number {X = 69, Y = 90}
};
var result = numbers.GroupBy(c => c.X);
var result2 = numbers.FirstOrDefault(c => result.Select(d => d.Key).Contains(c.Y));

var finalResult = numbers.Where(x => x.X == result2?.Y)
                         .Select(x =>  { x.X = result2.X;x.Y = x.Y; return x; } )
                         .Union(numbers.Where(c => c.X != result2?.Y)).GroupBy(c => c.X ,
                         (key, element) => new
                         {
                             Key = key,
                            Element = element.Select(c => c.Y).ToList()
                         });

The result:

enter image description here

like image 75
Salah Akbari Avatar answered Oct 06 '22 01:10

Salah Akbari