Solved

Lambda OrderByDescending ID and Select Distinct Name?

Posted on 2014-11-20
14
187 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 62

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 62

Expert Comment

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

Author Comment

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

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 32

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 62

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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

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 62

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 32

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 32

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Summary: Persistence is the capability of an application to store the state of objects and recover it when necessary. This article compares the two common types of serialization in aspects of data access, readability, and runtime cost. A ready-to…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

707 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now