• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 269
  • Last Modified:

Lambda OrderByDescending ID and Select Distinct Name?

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
WorknHardr
Asked:
WorknHardr
  • 6
  • 4
  • 3
  • +1
4 Solutions
 
Fernando SotoRetiredCommented:
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
 
WorknHardrAuthor Commented:
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
 
Fernando SotoRetiredCommented:
Will the names that show up as duplicates have the same Id values?
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
WorknHardrAuthor Commented:
Not using id's, just the names. It's for a simple DropDownList...
0
 
käµfm³d 👽Commented:
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
 
it_saigeDeveloperCommented:
Do you want the names ordered at all or do you just want the list by descending id?

-saige-
0
 
Fernando SotoRetiredCommented:
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
 
WorknHardrAuthor Commented:
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
 
WorknHardrAuthor Commented:
Kaufmed: I'll try your code when I get back in the office, thx
0
 
Fernando SotoRetiredCommented:
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
 
WorknHardrAuthor Commented:
Give me an example, I get your question.
0
 
it_saigeDeveloperCommented:
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
 
it_saigeDeveloperCommented:
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
 
WorknHardrAuthor Commented:
Using Kaufmed's first code posted.
Liking it_saige detailed explanation.
Liking Fernando Soto explanation.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

  • 6
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now