Linq distinct based on two columns

Question

I've a requirement where I need to fetch the unique records with same combination in 2 columns. My data would be Like CA(Column A) and CB(Column B) with some data

CA CB
1 2
1 2
3 4
5 6
2 1
1 6
1 6
5 1

Let's say, I need to fetch records with value 1 from both the columns which should be unique.

So, My End result should be like :

1 2
1 6
5 1

Here I should not get the record 2,1 because the combination already exists as 1,2 in the first record.

Here's the query I've tried :

var recentchats = (from s in MessagesCollection.AsQueryable()
                    where (s.@from == mytopic || s.to == mytopic) 
                    orderby s._id descending
                    select s).DistinctBy(x => x.from).Take(10).ToList();

I've used moreLinq extension for DistinctBy, because I need the whole record.(sorry for bad formatting and english!!!)

Here, My actual requirement is getting the recent chats of a user

Answer 1

Since the where has already made sure one of the two values is always the same, you can use the sum in distinctBy. (e.g. 1 + 2 is equal to 2 + 1)

DistinctBy(x => x.from + x.to)

Without the where, you can use Min and Max to still get unique pairs.

DistinctBy(x => new { Min=Math.Min(x.from, x.to), Max=Math.Max(x.from, x.to) })