Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 259
  • 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 SotoCommented:
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 SotoCommented:
Will the names that show up as duplicates have the same Id values?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help 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 SotoCommented:
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 SotoCommented:
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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