mjhjmtu

I am trying to group a list of DTOs which contain alternate family pairs to group them in the following format to minimize duplication.

Here is the DTO structure which I have currently which has duplicate rows as you can see which can be grouped together based on reverse relation also.

+----------+------------+-----------+
| PersonId | RelativeId | Relation |
+----------+------------+-----------+
| 1 | 2 | "Son" |
| 2 | 1 | "Father" |
| 1 | 3 | "Mother" |
| 3 | 1 | "Son" |
| 2 | 3 | "Husband" |
| 3 | 2 | "Wife" |
+----------+------------+-----------+

into something like this:

+----------+------------+-----------+-----------------+
| PersonId | RelativeId | Relation | ReverseRelation |
+----------+------------+-----------+-----------------+
| 1 | 2 | "Son" | "Father" |
| 1 | 3 | "Mother" | "Son" |
| 2 | 3 | "Husband" | "Wife" |
+----------+------------+-----------+-----------------+

Code which I am trying:

Program.cs

class Program

 static void Main(string args)
 
 List<RelationDTO> relationDTOList = new List<RelationDTO>
 
 new RelationDTO PersonId = 1, RelativeId = 2, Relation = "Son" ,
 new RelationDTO PersonId = 2, RelativeId = 1, Relation = "Father" ,

 new RelationDTO PersonId = 1, RelativeId = 3, Relation = "Mother" ,
 new RelationDTO PersonId = 3, RelativeId = 1, Relation = "Son" ,

 new RelationDTO PersonId = 2, RelativeId = 3, Relation = "Husband" ,
 new RelationDTO PersonId = 3, RelativeId = 2, Relation = "Wife" ,
 ;

 var grp = relationDTOList.GroupBy(x => new x.PersonId ).ToList();
 

RelationDTO.cs

public class RelationDTO

 public int PersonId get; set; 
 public int RelativeId get; set; 
 public string Relation get; set; 

Relations.cs

public class Relations

 public int PersonId get; set; 
 public int RelativeId get; set; 
 public string Relation get; set; 
 public string ReverseRelation get; set; 

I'm not sure whether it is what you need:

public static void Main()

 List<RelationDTO> relationDTOList = new List<RelationDTO>
 
 new RelationDTO PersonId = 1, RelativeId = 2, Relation = "Son" ,
 new RelationDTO PersonId = 2, RelativeId = 1, Relation = "Father" ,

 new RelationDTO PersonId = 1, RelativeId = 3, Relation = "Mother" ,
 new RelationDTO PersonId = 3, RelativeId = 1, Relation = "Son" ,

 new RelationDTO PersonId = 2, RelativeId = 3, Relation = "Husband" ,
 new RelationDTO PersonId = 3, RelativeId = 2, Relation = "Wife" ,
 ;

 var grp = relationDTOList.Join(relationDTOList, 
 dto => dto.PersonId + "-" + dto.RelativeId, 
 dto => dto.RelativeId + "-" + dto.PersonId, 
 (dto1, dto2) => new Relations 
 
 PersonId = dto1.PersonId, 
 RelationId = dto1.RelativeId, 
 Relation = dto1.Relation, 
 ReverseRelation = dto2.Relation 
 ).Distinct(new MyEqualityComparer());

 foreach (var g in grp)
 Console.WriteLine("0,1,2,3", g.PersonId, g.RelationId, g.Relation, g.ReverseRelation);


public class MyEqualityComparer : IEqualityComparer<Relations>

 public bool Equals(Relations x, Relations y)
 
 x.PersonId + "-" + x.RelationId == y.RelationId + "-" + y.PersonId;
 

 public int GetHashCode(Relations obj)
 
 return 0;
 

You can use a join operation like

var result = relationDTOList
.Where(v => v.PersonId < v.RelativeId)
.Join(
 relationDTOList.Where(v => v.PersonId > v.RelativeId),
 v => new KeyPersonId = v.PersonId, RelativeId = v.RelativeId,
 v => new KeyPersonId = v.RelativeId, RelativeId = v.PersonId,
 (p, q) => new Relations
 
 PersonId = p.PersonId,
 RelativeId = p.RelativeId,
 Relation = p.Relation,
 ReverseRelation = q.Relation
 
);

The Key is:

public struct Key

 public int PersonId get; set; 
 public int RelativeId get; set; 

I doubt a bit that LINQ is the best choice here as a loop with lookup might be a bit more efficient. However if you really need LINQ, then you could do the following

var relations = from person in relationDTOList
 // Match on the exact pair of IDs
 join relative in relationDTOList on
 new person.PersonId, person.RelativeId equals
 new PersonId = relative.RelativeId, RelativeId = relative.PersonId 

 // Build the new structure
 let relation = new Relations 
 PersonId = person.PersonId,
 Relation = person.Relation,
 RelativeId = relative.PersonId,
 ReverseRelation = relative.Relation
 

 // Order the pairs to find the duplicates
 let ids = new person.PersonId, relative.PersonId.OrderBy(x => x).ToArray()
 group relation by new FirstPersonId = ids[0], SecondPersonId = ids[1] 
 into relationGroups

 // Select only the the first of two duplicates
 select relationGroups.First();

What this code does is joins the collection with itself on the matching pairs PersonId, RelativeId and then filters out the second record of each pair thus resulting in a collection where the first person found in the list will be considered as parent in the relation.

EDIT: The lookup method I was talking about:

var result = new List<Relations>();
while (relationDTOList.Any())

 var person = relationDTOList.First();
 relationDTOList.RemoveAt(0);

 var relative = relationDTOList.Where(x =>
 x.PersonId == person.RelativeId && x.RelativeId == person.PersonId)
 .Select((x, i) => new Person = x, Index = i).FirstOrDefault();

 if (relative != null)
 
 relationDTOList.RemoveAt(relative.Index);
 result.Add(new Relations 
 PersonId = person.PersonId,
 Relation = person.Relation,
 RelativeId = relative.Person.PersonId,
 ReverseRelation = relative.Person.Relation
 );
 

As a note, it empties your original list so you have to make a copy (list.ToList()) if you need it further in your code.

Running this code turned out to be about six times faster than the method with join I posted before. I also came up with the following grouping method which runs much faster than the join, however it's still slower than the lookup-and-remove method although they do a very similar thing.

var relations = relationDTOList.GroupBy(person =>
 person.PersonId < person.RelativeId
 ? new FirstPersonId = person.PersonId, SecondPersonId = person.RelativeId
 : new FirstPersonId = person.RelativeId, SecondPersonId = person.PersonId)

 .Select(group => new Relations 
 PersonId = group.First().PersonId,
 Relation = group.First().Relation,
 RelativeId = group.First().RelativeId,
 ReverseRelation = group.Last().Relation
 );

var query = relationDTOList.OrderBy(x=>x.PersonId).GroupJoin(relationDTOList,
p => p.PersonId,
a => a.RelativeId,
(p, al) =>
new

 p.PersonId,
 p.RelativeId,
 p.Relation,
 Parrent = al.Where(x => x.PersonId == p.RelativeId && x.RelativeId == p.PersonId).SingleOrDefault().Relation
 
 ).ToList();

You could Groupby your relations with a sorted Tuple of PersonId and RelativeId, then pick the first item as first relation and the second item as the reverse relation.

Demo:

using System;
using System.Collections.Generic;
using System.Linq;

namespace Example 

 public static class Program 

 public static void Main (string args) 

 List<RelationDTO> relationDTOList = new List<RelationDTO> 
 new RelationDTO PersonId = 1, RelativeId = 2, Relation = "Son" ,
 new RelationDTO PersonId = 2, RelativeId = 1, Relation = "Father" ,

 new RelationDTO PersonId = 1, RelativeId = 3, Relation = "Mother" ,
 new RelationDTO PersonId = 3, RelativeId = 1, Relation = "Son" ,

 new RelationDTO PersonId = 2, RelativeId = 3, Relation = "Husband" ,
 new RelationDTO PersonId = 3, RelativeId = 2, Relation = "Wife" ,
 ;

 // Group relations into list of lists
 var groups = relationDTOList
 .GroupBy (r => GetOrderedTuple (r.PersonId, r.RelativeId))
 .Select (grp => grp.ToList ()).ToList ();

 // Output original relations and their reverse relations
 foreach (var group in groups) 
 var relation = group.ElementAt (0);
 var reverseRelation = group.ElementAt (1);
 FormattableString relationOutput = $"PersonId=relation.PersonId RelativeId=relation.RelativeId Relation=relation.Relation ReverseRelation=reverseRelation.Relation";
 Console.WriteLine (relationOutput);
 
 

 private static Tuple<int, int> GetOrderedTuple (int n1, int n2) 
 if (n1 < n2) 
 return Tuple.Create (n1, n2);
 
 return Tuple.Create (n2, n1);
 
 

Output:

PersonId=1 RelativeId=2 Relation=Son ReverseRelation=Father
PersonId=1 RelativeId=3 Relation=Mother ReverseRelation=Son
PersonId=2 RelativeId=3 Relation=Husband ReverseRelation=Wife

This will do it. But it requires duplicates in the original list.

var result = relationDTOList
 .Where(v => v.PersonId < v.RelativeId)
 .GroupJoin(relationDTOList,
 p => p.PersonId,
 a => a.RelativeId,
 (p, al) =>
 new
 p.PersonId,
 p.RelativeId,
 p.Relation,
 ReverseRelation = al.Where( x => 
 x.PersonId == p.RelativeId &&
 x.RelativeId == p.PersonId )
 .SingleOrDefault()
 .Relation ).ToList();