How use Distinct in linq & linq to NHibernate by some columns

Question

I have a Address class.

public class Address : RootEntityBase
{
    virtual public string Province { set; get; }
    virtual public string City { set; get; }        
    virtual public string PostalCode { set; get; }
}

By this default values:

var myList = new List<Address>
             {
               new Address {Province = "P1", City = "C1", PostalCode = "A"},
               new Address {Province = "P1", City = "C1", PostalCode = "B"},
               new Address {Province = "P1", City = "C1", PostalCode = "C"},

               new Address {Province = "P1", City = "C2", PostalCode = "D"},
               new Address {Province = "P1", City = "C2", PostalCode = "E"},

               new Address {Province = "P2", City = "C3", PostalCode = "F"},
               new Address {Province = "P2", City = "C3", PostalCode = "G"},
               new Address {Province = "P2", City = "C3", PostalCode = "H"},

               new Address {Province = "P2", City = "C4", PostalCode = "I"}
             };

I need to extraction distinct of this myList by two columns : Province & City

Namely similar to myExpertResult :

var myExpertResult = new List<Address>
                        {
                           new Address {Province = "P1", City = "C1"},
                           new Address {Province = "P1", City = "C2"},
                           new Address {Province = "P2", City = "C3"},
                           new Address {Province = "P2", City = "C4"}
                        }; 

So I use this code :

var list = myList.Select(x => new Address {City = x.City, Province = x.Province}).Distinct().ToList();

but my result is not valid because count of result is 9 namely all addresses.

Quivalent query in SQL is : select distinct Province , City from tblAddress

also I tested this query by linq to NHibernate.

var q = SessionInstance.Query<Address>();
        .Select(x => new Address { Province = x.Province, City = x.City }).Distinct().ToList();

But it is not support this query. Message of exception is : Expression type 'NhDistinctExpression' is not supported by this SelectClauseVisitor.

How can I do it?

Answer 1

You can use GroupBy:

var result = myList.GroupBy(a => new { a.Province, a.City })
      .Select(g => new Address { 
                  Province = g.Key.Province, 
                  City = g.Key.City 
              });

Or use anonymous type:

 myList.Select(a => new { 
            Province = a.Province,
            City = a.City
        })
      .Distinct();

By default, anonymous type uses value quality to compare, it is equivalent when all properties are equivalent.

Another way is to customer EqualityComparer which uses Province and City for Equal and GetHashCode method with another overload Distinct in here