How to select Distinct rows from the Database

Question

I am trying to select the distinct rows from the database using the code given below. When i run this query it does not return the result what i am expected. I need distinct city names into dropdown control.

using (var context = new CountryEntities())
        {
            var city = (from u in context.Cities
                        where u.StateId == StateID
                        select new
                        {
                            cityId = u.CityId,
                            cityName = u.CityName

                        }).Distinct();


            cboCity.DataSource = city.ToList();
            cboCity.DataValueField = "CityId";
            cboCity.DataTextField = "CityName";
            cboCity.DataBind();
            cboCity.Items.Insert(0, new ListItem("--Select--", "0"));
        }

My database table is

enter image description here

What should i do. Please help me. Thanks in advance.

Are there multiple cities with the same `Name` but a different `ID`? — D Stanley, Nov 25 '13 at 20:19
So which CityId do you use if you have two cities with the same name? That's the real question. — System Down, Nov 25 '13 at 20:21
Within the same state, though, are there cities with the same name? — Sergey Kalinichenko, Nov 25 '13 at 20:21
No actually i am storing Pincode also. So there may be multiple cities with the same pincode. — Jaan, Nov 25 '13 at 20:23

score 2 · Answer 1 · answered Nov 25 '13 at 20:27

If you must ensure that there are no duplicate city names, you can do it like this:

var city = context.Cities
    .Where(u => u.StateId == StateID)
    .GroupBy(u => u.CityName)
    .Select(g => new {
        cityId = g.First().CityId,
        cityName = g.Key
    });

However, this has the consequence of throwing away some cities with duplicate names, because only one cityId is going to get used.

If you wish to overcome this limitation, you would need to introduce another column for disambiguation of the city - say, a area name or a postal code. Then you would need to change the code to append this disambiguation marker to names of cities that cannot be uniquely identified by their name alone.

How to select Distinct rows from the Database

1 Answers1