-1

Hello I would like to select a list of cities from DB, with no duplicate names. After researching the issue I have tried multiple combinations using First and Distinct but I cannot seem to get the syntax right.

Any insight would be great. Below is my code

TABLE

Cities

PK Id

Name

IsActive

C#

var cities = await Db.Addresses.Where(x => x.IsActive == true).Distinct().ToListAsync();
RyeGuy
  • 4,213
  • 10
  • 33
  • 57
  • 5
    Well what is the problem? Do you still get duplicates? You know, **ALL** the values have to match for it to be distinct – musefan Jan 23 '17 at 13:39
  • 2
    can you share Db schema – abhi Jan 23 '17 at 13:40
  • Yes I still get duplicate object by name. Oh I did not know that. What I need to pass something into the distinct as a parameter to get it ONLY by name? – RyeGuy Jan 23 '17 at 13:41
  • 1
    have you tried working through the official documentation? https://msdn.microsoft.com/en-us/library/bb348436(v=vs.110).aspx – sous2817 Jan 23 '17 at 13:42
  • 1
    try get only names and then get distinct from them... something like `var cities = await Db.Addresses.Where(x => x.IsActive == true).Select(x=> x.Name).Distinct().ToListAsync();` – Nino Jan 23 '17 at 13:42
  • That solved it! Thanks a ton – RyeGuy Jan 23 '17 at 13:43
  • 1
    @Rye: You can use a group by, [something like this](http://stackoverflow.com/questions/14321013/distinct-in-linq-based-on-only-one-field-of-the-table). The point you need to think about is how do you decide which of the 'other' non distinct values you want? If you only want a list of names: `var cities = (from x in Db.Addresses where x.IsActive select new { x.Name } ).Distinct().ToList();` – musefan Jan 23 '17 at 13:43
  • 1
    Hi .. Distinct method does not take any parameters. Either you can create your own extension method which will be a complex logic to compare each object with rest of all in the list or you can use IEquatable interface as described here https://msdn.microsoft.com/en-us/library/bb348436(v=vs.110).aspx – Chetan Jan 23 '17 at 13:55

2 Answers2

2

Use DistinctBy(x=>x.Name)

var cities = await Db.Addresses.Where(x => x.IsActive == true).DistinctBy(x=>x.Name).ToListAsync();

Where DistincBy:

public static IEnumerable<t> DistinctBy<t>(this IEnumerable<t> list, Func<t, object> propertySelector)
{
    return list.GroupBy(propertySelector).Select(x => x.First());
}
Ryan Searle
  • 1,597
  • 1
  • 19
  • 30
1

You can try below code 

var cities = await Db.Addresses.Where(x => x.IsActive == true).Select(x=> x.Cities).Distinct().ToListAsync();
abhi
  • 1,059
  • 9
  • 19