LINQ join with distinct resultset

Question

I have a LINQ question. I am not great with linq. I have two classes:

[Person]
string FirstName {get;set;}
string LastName {get;set;}
IEnumerable<State> LastName {get;set;}

[State]
int StateID {get;set;}
string StateName {get;set;}

I would like to write a LINQ query that would return a distinct list of states for all "Person" classes. An example in sql would be

SELECT DISTINCT s.StateID
FROM Person p
JOIN States s ON (p.StateID = s.StateID)

Any help on this would be greatly appreciated.

Answer 1

Try this:

var stateList = (from s in context.States
          join p in context.Persons on s.StateId equals p.StateId
          select s).Distinct();

Answer 2

Linq distinct is annoying, you have to implement the IEqualityComparer interface on the class you want to Distinct(), then the way you get your distinct selection you're referring to would be:

IEnumerable<Person> people = GetPeople();
people.SelectMany((person) => person.LastName).Distinct();

SelectMany() flattens the result unioning the enumerables so you don't get IEnumerable<IEnumerable<State>> but get IEnumerable<State>

When implementing the IEqualityComparer know that Distinct() does validate both the results from Equals() are equivalent, and the results from GetHashCode() are equivalent. I think in your case you want to implement the comparer on the State class.

Might look something like this:

public class State : IEqualityComparer<State>
{
    int StateID {get;set;} 
    string StateName {get;set;} 

    public bool Equals(State x, State y) { return x.StateID == y.StateID && x.StateName == y.StateName; }
    public int GetHashCode(State obj) { return obj.StateId; }
}

Remember, your distinct() will do nothing for you if you don't implement the IEqualityComparer.

Answer 3

Like this:

people.SelectMany(p => p.States).Distinct();

Note that you will need to correctly implement Equals and GetHashCode in the State class. (Unless you're using LINQ-to-SQL or entities)

If you just want the IDs, you don't need to implement Equals / GetHashCode; you can simply call

people.SelectMany(p => p.States).Select(s => s.StateId).Distinct();