Select Distinct returns all rows not Distinct items RSS

• • Reply
  

  Theguzu

  Member

  77 Points

  139 Posts

  Select Distinct returns all rows not Distinct items

  Mar 18, 2012 02:41 AM|LINK

  I am using the following code to try to select distinct items from database but it returns multiple items of the same value. Can somebody tell me what I am doing wrong?

  The code is

  List<MyList> mylist = db.MyLists.Where(c => c.Name.StartsWith(prefixText)).Distinct().ToList();

   

  and Result for say "ABC" is

  ABC
  ABC
  ABC
  ABCD
  ABCD
  ABCD
  ABCE
  and so on

• • Reply
  

  me_ritz

  Star

  9337 Points

  1447 Posts

  Re: Select Distinct returns all rows not Distinct items

  Mar 18, 2012 03:28 AM|LINK

  Try the code below:

  List<MyList> mylist = db.MyLists.Where(c => c.Name.StartsWith(prefixText)).ToList();
  var distinctRec = (from l in
                          (
                           from p in mylist
                           select new {p.Name})
                     select new {l.Name}).Distinct().ToList();

  Please mark the post as "Answer" that helps you.
• • Reply
  

  Theguzu

  Member

  77 Points

  139 Posts

  Re: Select Distinct returns all rows not Distinct items

  Mar 18, 2012 03:08 PM|LINK

  Thank you for you response. I believe your solution is correct  because I am selecting distinct across all columns not the name only. Can it be done in the original selection on first line? How do I modify the code to select the name column only in the first line? I think that will solve theproblem.

• • Reply
  

  me_ritz

  Star

  9337 Points

  1447 Posts

  Re: Select Distinct returns all rows not Distinct items

  Mar 19, 2012 04:51 AM|LINK

  If you want to do it in first line, you need to define a IEqualityComparer
  in order to pass to the Distinct function.

  CS Code:
  ---------
  Comparer->
  
  class MyListComparer : IEqualityComparer<MyList>
  {
      public bool Equals(MyList x, MyList y)
      {
          return x.Name.Equals(y.Name);
      }
  
      public int GetHashCode(MyList obj)
      {
          return obj.Name.GetHashCode();
      }
  
  }
  
  ***********
  expression for selecting distinct record ->
  
  var distinctRec = db.MyLists.Where(c => c.Name.StartsWith(prefixText)).AsEnumerable().Distinct(new MyListComparer());

  Please mark the post as "Answer" that helps you.
  • Marked as answer by Theguzu on Mar 19, 2012 11:58 AM
• • Reply
  

  Theguzu

  Member

  77 Points

  139 Posts

  Re: Select Distinct returns all rows not Distinct items

  Mar 19, 2012 11:58 AM|LINK

  Thank you.