linq using where contains groupby select and orderby [Answered]RSS

• • Reply
  ragavalli

  Member

  22 Points

  180 Posts

  linq using where contains groupby select and orderby

  Mar 15, 2017 04:15 PM|ragavalli|LINK

  Requirement:

  1) Get the distinct nationalcodes from the table TestConcodes

  2) Get the names of the codes obtained in the step above by querying the table refCodes, but refCodes table might have more than one record for each code, so I just need to pick one row for each code and then to order by based on code text.

  I have written the code as below and it seems to be working, but I am wondering is there any flaw in this or can there be any better linq code to get my requirement.

                       

  var records = context.TestConcodes.Select(c => c.NationalCode).Distinct();
  
                      if (records.Count() > 0)
                      {
                          var results = context.refCodes
                              .Where(c => records.Contains(c.CodeValue))
                              .GroupBy(a => a.CodeValue)
                              .Select(g =>g.FirstOrDefault())
                              .OrderBy(c => c.CodeText).ToList();
  					}
  					
  					ddlOutput.DataSource = results;
                              ddlOutput.DataTextField = "CodeText";
                              ddlOutput.DataValueField = "CodeValue";
                    }

• • Reply
  RichardY

  Contributor

  6874 Points

  1980 Posts

  Re: linq using where contains groupby select and orderby

  Mar 15, 2017 07:11 PM|RichardY|LINK

  Not a bad way of doing it.  I use a DistinctBy<>() generic method which seems to work a slight bit faster than the groupby method.

  I did not write this method and I wish I remembered who did so I could give proper credit:

          public static IEnumerable<TSource> DistinctBy<TSource, TKey>(this IEnumerable<TSource> source, Func<TSource, TKey> keySelector)
          {
              HashSet<TKey> seenKeys = new HashSet<TKey>();
              foreach (TSource element in source)
              {
                  if (seenKeys.Add(keySelector(element)))
                  {
                      yield return element;
                  }
              }
          }

  You could use it:

         var results = context.refCodes
                              .Where(c => records.Contains(c.CodeValue))
                              .DistinctBy(a => a.CodeValue)
                              .OrderBy(c => c.CodeText).ToList();