Solved

Lambda OrderByDescending ID and Select Distinct Name?

Posted on 2014-11-20
14
200 Views
Last Modified: 2016-02-18
I need to select the distinct Names and order by descending ID.

[table]
ID            Name      Added
1		Jim	        2014-11-20 17:30:39.163
4		Ed	        2014-11-20 17:53:12.337
5		Zeke	2014-11-20 19:11:52.333
7		Jim   	2014-11-20 19:17:37.187
8		Bob	        2014-11-20 19:20:19.260
10		Joe	        2014-11-20 19:49:24.140
14		Jim	        2014-11-20 20:12:36.140
15		Ken   	2014-11-20 20:14:52.753
16		Ken	        2014-11-20 20:16:21.847

var nameList = context.History.OrderByDescending(o => o.ID)
                                                                                .Select(s => s.Name)
                                                                                .Distinct()
                                                                                .ToList();

This lambda always returns the names in 'alphabetical' order instead of id (16, 15, 14 ...)

Open in new window

0
Comment
Question by:WorknHardr
  • 6
  • 4
  • 3
  • +1
14 Comments
 
LVL 63

Assisted Solution

by:Fernando Soto
Fernando Soto earned 125 total points
ID: 40456659
Hi WorknHardr;

The issue is that the Distinct method returns an unordered sequence that contains no duplicate values. It uses the default equality comparer, Default, to compare values. So doing the ordering of the values in the query will NOT return the same ordering after the Distinct method does its operations on it.

Will the names that show up as duplicates have the same Id values?
0
 

Author Comment

by:WorknHardr
ID: 40456668
okay, just trying to understand it, thanks. Here what I'm using now:
  var nameList = (from c in context.History
                              orderby c.ID descending
                              select new { Name = c.Name }).Distinct().ToList();

Open in new window

0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 40456672
Will the names that show up as duplicates have the same Id values?
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 

Author Comment

by:WorknHardr
ID: 40456690
Not using id's, just the names. It's for a simple DropDownList...
0
 
LVL 75

Accepted Solution

by:
käµfm³d   👽 earned 125 total points
ID: 40457264
Untested, but how about:

var nameList = (from c in context.History
                group c by c.Name into g
                select new
                {
                    Name = g.Key,
                    ID = g.Min(item => item.ID)
                }).OrderByDescending(item => item.ID);

Open in new window

0
 
LVL 33

Expert Comment

by:it_saige
ID: 40457272
Do you want the names ordered at all or do you just want the list by descending id?

-saige-
0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 40457297
Hi WorknHardr;

You state this in your question, "I need to select the distinct Names and order by descending ID. ", and in your last post, "Not using id's, just the names. It's for a simple DropDownList...", so are you now saying that the ID's do not influence the order in which the names appear in the DropDownList?
0
 

Author Comment

by:WorknHardr
ID: 40457400
Names ordered by id (not alphabetical) is my goal. The dropdown is simple key (name) value (name), not int required.

The lambda always returned the names in alphabetical order, which I don't want. So linq works well in this case.
0
 

Author Comment

by:WorknHardr
ID: 40457404
Kaufmed: I'll try your code when I get back in the office, thx
0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 40457412
So ID's do play a roll in sorting the list of names. So my question again "Will the names that show up as duplicates have the same Id values?"
0
 

Author Comment

by:WorknHardr
ID: 40457421
Give me an example, I get your question.
0
 
LVL 33

Assisted Solution

by:it_saige
it_saige earned 250 total points
ID: 40457823
How is this:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace EE_Q28566886
{
	class DataItem
	{
		public int ID { get; set; }
		public string Name { get; set; }
		public DateTime Added { get; set; }
	}

	class Program
	{
		static List<DataItem> data = new List<DataItem>()
		{
			new DataItem() { ID = 1, Name = "Jim", Added = Convert.ToDateTime("2014-11-20 17:30:39.163") },
			new DataItem() { ID = 4, Name = "Ed", Added = Convert.ToDateTime("2014-11-20 17:53:12.337") },
			new DataItem() { ID = 5, Name = "Zeke", Added = Convert.ToDateTime("2014-11-20 19:11:52.333") },
			new DataItem() { ID = 7, Name = "Jim", Added = Convert.ToDateTime("2014-11-20 19:17:37.187") },
			new DataItem() { ID = 8, Name = "Bob", Added = Convert.ToDateTime("2014-11-20 19:20:19.260") },
			new DataItem() { ID = 10, Name = "Joe", Added = Convert.ToDateTime("2014-11-20 19:49:24.140") },
			new DataItem() { ID = 14, Name = "Jim", Added = Convert.ToDateTime("2014-11-20 20:12:36.140") },
			new DataItem() { ID = 15, Name = "Ken", Added = Convert.ToDateTime("2014-11-20 20:14:52.753") },
			new DataItem() { ID = 16, Name = "Ken", Added = Convert.ToDateTime("2014-11-20 20:16:21.847") }
		};

		static void Main(string[] args)
		{
			var distinctMethod = (from item in data orderby item.ID descending select item.Name).Distinct();
			var groupbyMethod = (from item in data
							 orderby item.ID descending
							 group item by item.Name into names
							 where names != null && names.Count() > 0
							 select names.First().Name);
			var kaufmedMethod = (from c in data
							 group c by c.Name into g
							 select new
							 {
								 Name = g.Key,
								 ID = g.Min(item => item.ID)
							 }).OrderByDescending(item => item.ID);

			Console.WriteLine("Using the distinct method:");
			foreach (var item in distinctMethod)
				Console.WriteLine(item);

			Console.WriteLine();
			Console.WriteLine("Using the group by method (produces the same results as distinct):");
			foreach (var item in groupbyMethod)
				Console.WriteLine(item);

			Console.WriteLine();
			Console.WriteLine("Using kaufmed's method:");
			foreach (var item in kaufmedMethod)
				Console.WriteLine(item);
			Console.ReadLine();
		}
	}
}

Open in new window


Produces the following results:Capture.JPG
Are any of these what you are looking for?

-saige-
0
 
LVL 33

Assisted Solution

by:it_saige
it_saige earned 250 total points
ID: 40457847
And in case you are wondering why Kaufmend's Jim is at the bottom of the list instead of right beneath Ken, it is because Kaufmed took into account that you wanted them ordered by id in descending order.  So the list get's essentially ordered twice.  The first ordering is by explicitly selecting the minimum id from the internal list of grouped items.  The second ordering is completed by the OrderByDescending Method.

This also could be rewritten to use expression based linq, as such:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace EE_Q28566886
{
	class DataItem
	{
		public int ID { get; set; }
		public string Name { get; set; }
		public DateTime Added { get; set; }
	}

	class Program
	{
		static List<DataItem> data = new List<DataItem>()
		{
			new DataItem() { ID = 1, Name = "Jim", Added = Convert.ToDateTime("2014-11-20 17:30:39.163") },
			new DataItem() { ID = 4, Name = "Ed", Added = Convert.ToDateTime("2014-11-20 17:53:12.337") },
			new DataItem() { ID = 5, Name = "Zeke", Added = Convert.ToDateTime("2014-11-20 19:11:52.333") },
			new DataItem() { ID = 7, Name = "Jim", Added = Convert.ToDateTime("2014-11-20 19:17:37.187") },
			new DataItem() { ID = 8, Name = "Bob", Added = Convert.ToDateTime("2014-11-20 19:20:19.260") },
			new DataItem() { ID = 10, Name = "Joe", Added = Convert.ToDateTime("2014-11-20 19:49:24.140") },
			new DataItem() { ID = 14, Name = "Jim", Added = Convert.ToDateTime("2014-11-20 20:12:36.140") },
			new DataItem() { ID = 15, Name = "Ken", Added = Convert.ToDateTime("2014-11-20 20:14:52.753") },
			new DataItem() { ID = 16, Name = "Ken", Added = Convert.ToDateTime("2014-11-20 20:16:21.847") }
		};

		static void Main(string[] args)
		{
			var kaufmedMethod = (from c in data
							 group c by c.Name into g
							 orderby g.Min(item => item.ID) descending
							 select new
							 {
								 Name = g.Key,
								 ID = g.Min(item => item.ID)
							 });

			Console.WriteLine("Using kaufmed's method:");
			foreach (var item in kaufmedMethod)
				Console.WriteLine(item);
			Console.ReadLine();
		}
	}
}

Open in new window


Now produces the following results:Capture.JPG
If you only want the names and don't care about the id's but want the results that Kaufmed's method returns, then you would do this:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace EE_Q28566886
{
	class DataItem
	{
		public int ID { get; set; }
		public string Name { get; set; }
		public DateTime Added { get; set; }
	}

	class Program
	{
		static List<DataItem> data = new List<DataItem>()
		{
			new DataItem() { ID = 1, Name = "Jim", Added = Convert.ToDateTime("2014-11-20 17:30:39.163") },
			new DataItem() { ID = 4, Name = "Ed", Added = Convert.ToDateTime("2014-11-20 17:53:12.337") },
			new DataItem() { ID = 5, Name = "Zeke", Added = Convert.ToDateTime("2014-11-20 19:11:52.333") },
			new DataItem() { ID = 7, Name = "Jim", Added = Convert.ToDateTime("2014-11-20 19:17:37.187") },
			new DataItem() { ID = 8, Name = "Bob", Added = Convert.ToDateTime("2014-11-20 19:20:19.260") },
			new DataItem() { ID = 10, Name = "Joe", Added = Convert.ToDateTime("2014-11-20 19:49:24.140") },
			new DataItem() { ID = 14, Name = "Jim", Added = Convert.ToDateTime("2014-11-20 20:12:36.140") },
			new DataItem() { ID = 15, Name = "Ken", Added = Convert.ToDateTime("2014-11-20 20:14:52.753") },
			new DataItem() { ID = 16, Name = "Ken", Added = Convert.ToDateTime("2014-11-20 20:16:21.847") }
		};

		static void Main(string[] args)
		{
			var kaufmedMethod = (from c in data
							 group c by c.Name into g
							 orderby g.Min(item => item.ID) descending
							 where g != null && g.Count() > 0
							 select g.Min(item => item.Name));

			Console.WriteLine("Using kaufmed's method:");
			foreach (var item in kaufmedMethod)
				Console.WriteLine(item);
			Console.ReadLine();
		}
	}
}

Open in new window


Now produces:Capture.JPG
-saige-
0
 

Author Closing Comment

by:WorknHardr
ID: 40458904
Using Kaufmed's first code posted.
Liking it_saige detailed explanation.
Liking Fernando Soto explanation.
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Hi all and welcome to my first article on Experts Exchange. A while ago, someone asked me if i could do some tutorials on object oriented programming. I decided to do them on C#. Now you may ask me, why's that? Well, one of the re…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question