get unique values from query to build Dictionary

Question

I want to build a combobox with key->postal and value->city to use as filter for my accomodations.

To limit the number of items in the list I only use the postals I have used when filling up the table tblAccomodations. For now I do not use a relational table with postals and city's although I'm thinking about an update later on.

Here I build my dictionary:

public static Dictionary<int, string> getPostals()
{
    Dictionary<int, string> oPostals = new Dictionary<int, string>();

    using (DBReservationDataContext oReservation = new DBReservationDataContext())
    {
        var oAllPostals = (from oAccomodation in oReservation.tblAccomodations
                            orderby oAccomodation.Name ascending
                            select oAccomodation);

        foreach (tblAccomodation item in oAllPostals)
        {
            oPostals.Add(int.Parse(item.Postal), item.City);
        }
    }
    return oPostals;
}

As expected I got an error: some Accomodations are located in the same city, so there are double values for the key. So how can I get a list of unique cities and postals (as key)? I tried to use

select oAccomodation.Postal.Distinct()

but that didn't work either.

UPDATE: I have found the main problem. There are multiple cities with the same postal ("Subcity"). So I'm gonna filter on "City" and not on "Postal".

score 1 · Answer 1 · answered Feb 16 '12 at 10:08

change

foreach (tblAccomodation item in oAllPostals)
        {
            oPostals.Add(int.Parse(item.Postal), item.City);
        }

to

    foreach (tblAccomodation item in oAllPostals.Distinct(x=>x..Postal)
             { 
if(!oPostals.ContainsKey(int.Parse(item.Postal)))
                 oPostals.Add(int.Parse(item.Postal), item.City);
             }

score 1 · Answer 2 · answered Feb 16 '12 at 10:12

BTW, if you have multiple cities in one postal (I am not sure if it is possible in your domain), which one you want to see?

If any of cities will do, then it is easy to just get the first one per postal:

var oAllPostals = oReservation.tblAccomodations
                      .OrderBy(x=>x.Name)
                      .ToLookup(x=>x.Postal, x=>x.City)
                      .ToDictionary(x=>x.Key, x.First());

In the same example if you do .ToList() or even .Distinct().ToList() instead of .First() you will have all of cities in the dictionary of Dictionary<Postal, List<City>>.

that was indeed the problem. I have multiple cities with the same postal. It is important to have them all so I can't use postal as key. — bflydesign, Feb 16 '12 at 10:54

score 1 · Accepted Answer · answered Feb 16 '12 at 10:12

1

I think your looking for 'Distinct'. Gather your list of all postals and then return myPostals.Distinct().

Hope than helps.

answered Feb 16 '12 at 10:12

Rob Smyth

1,768
11
19

I was indeed trying with Distinct() but that didn't work either. Now I have seen what the problem was. There are cities who have the same postal and I think the crashed on that. – bflydesign Feb 16 '12 at 10:50

score 1 · Answer 4 · edited Aug 21 '17 at 05:42

Using .containkey will exclude [1 (postal key) to n (cities relation)]. i.e since Key already exists next city (with the same postal key ) will not get into your dictionary.

However, if you want to map your postal to list of cities, you can represent a dictionary that can contain a collection of values like the following:

Dictionary < String[Postal]> , List < Cities>>

This way you'll have a dictionary that can have multiple values.

score 1 · Answer 5 · edited May 23 '17 at 12:20

Assuming the combination of postal + city is unique you could do the following:

public static Dictionary<int, string> getPostals()
{
    Dictionary<int, string> oPostals = new Dictionary<int, string>();

    using (DBReservationDataContext oReservation = new DBReservationDataContext())
    {
        var oAllPostals = (from oAccomodation in oReservation.tblAccomodations
                            orderby oAccomodation.Name ascending
                            select oAccomodation);

        foreach (tblAccomodation item in oAllPostals)
        {
            oPostals.Add((item.Postal + item.City).GetHashCode(), item.Postal + " " + item.City);
        }
    }
    return oPostals;
}

Edit:

If you want to use the selected value from the drop box then you can use the following:

public static Dictionary<int, Tuple<string, string>> getPostals()
{
    Dictionary<int, string> oPostals = new Dictionary<int, string>();

    using (DBReservationDataContext oReservation = new DBReservationDataContext())
    {
        var oAllPostals = (from oAccomodation in oReservation.tblAccomodations
                            orderby oAccomodation.Name ascending
                            select oAccomodation);

        foreach (tblAccomodation item in oAllPostals)
        {
            oPostals.Add((item.Postal + item.City).GetHashCode(), new Tuple<string, string>(item.Postal, item.City));
        }
    }
    return oPostals;
}

The way you bind the following depends on whether you're using asp.net, winforms etc. Here's an example for winforms.

you have multiple cities per postal but the combination is indeed unique. I suppose the GetHashCode gives you a unique int as result. But how can I implement that in my filter? That is not a postal anymore. — bflydesign, Feb 16 '12 at 10:57

get unique values from query to build Dictionary

5 Answers5