Solved

Lambda OrderByDescending ID and Select Distinct Name?

Posted on 2014-11-20
14
194 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Introduction This article series is supposed to shed some light on the use of IDisposable and objects that inherit from it. In essence, a more apt title for this article would be: using (IDisposable) {}. I’m just not sure how many people would ge…
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

832 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