How can i return a list with 1 property not being duplicated?

Question

I have a method that pulls a list of states and cities from a database. The states are unique, but there can be many cities in that state. What my method currently does is return each state, and city pair as an individual item. What I need it to do is have a state with many cities.

Currently Returns

oh- cincinnati

oh- cleveland

oh- findlay

in- indianapolis

what I need it to Return

oh -cincinnati,cleveland,findlay

in- indianapolis

MODEL

public class Location
{
    public string State { get; set; }
    public string city { get; set; }

}

REPOSITORY

public HashSet<Location> getlocation()
    {
        HashSet<Location> myHashset = new HashSet<Location>();

        const string storedProc = "someProc";

        dynamic locations;


        using (var conn = DbFactory.myConnection())
        {
            locations = conn.Query(storedProc, commandType: CommandType.StoredProcedure);

        }

        foreach (var location in locations)
        {

                myHashset.Add(new location{State = location.state,City = location.city});


        }
          return myHashset
    }

Answer 1

this should do it

var Result = myHashset.GroupBy(r => r.State)
        .Select(g => new Location
        {
            State = g.Key,
            city = String.Join(", ", g.Select(r => r.city))
        });

maybe you don't want to store this into a new Location object. I'd use a Dictionary

Update - Dictionary

Dictionary<string,string> Result = myHashset.GroupBy(r => r.State)
.ToDictionary(g => g.Key, g => String.Join(", ", g.Select(r => r.city)));