fork download
copy 
  1. using System;
  2. using System.Collections;
  3. using System.Collections.Generic;
  4. using System.Linq;
  5.  
  6. public class Test
  7. {
  8. 	public static void Main()
  9. 	{
  10. 		var destinctList = new List<DestinctItem>()
  11. 		{
  12. 		new DestinctItem() { ClientName = "Alpha", ClientID = 1, ClientProductName="AAA" },
  13. 		new DestinctItem() { ClientName = "Bravo", ClientID = 2, ClientProductName="BBB" }
  14. 		};
  15.  
  16. 		var someContext_ClientAlias = new List<ClientAlias>()
  17. 		{
  18. 			new ClientAlias() { Name = "Alpha", ClientID = 1, CreationDate = DateTime.Now.AddDays(-7) }
  19. 		};
  20.  
  21. 		var someContext_ClientProductAlias = new List<ClientProductAlias>()
  22. 		{
  23. 			new ClientProductAlias() { Name = "AAA", ClientID = 1, ClientProductID = 101, CreationDate = DateTime.Now.AddDays(-7) }
  24. 		};
  25.  
  26. 		var data = (from list in destinctList  
  27. 		join ca in someContext_ClientAlias on list.ClientName equals ca.Name into list_client_join  
  28. 		from list_client in list_client_join.DefaultIfEmpty()  
  29. 		join cpa in someContext_ClientProductAlias on new { ClientID = (long)list.ClientID, Name = list.ClientProductName } equals  
  30. 		new { cpa.ClientID, cpa.Name } into j1  
  31. 		// maybe needs the following: 
  32. 		where j1.Any () 
  33. 		from j2 in j1.DefaultIfEmpty()  
  34. 		orderby list_client.CreationDate descending  
  35. 		orderby j2.CreationDate descending  
  36. 		select new { ClientID = list_client.ClientID,   
  37. 			ClientName = list.ClientName,   
  38. 			ClientProductID = j2.ClientProductID,   
  39. 			ClientProductName = list.ClientProductName }
  40. 			).ToList();  	
  41.  
  42. 		Console.WriteLine("First query:");
  43. 		foreach(var result in data) Console.WriteLine(result);
  44.  
  45. 		// alternate query
  46. 		var data2 = (from list in destinctList  
  47. 		let ca = someContext_ClientAlias
  48. 			.OrderByDescending (cca => cca.CreationDate)
  49. 			.FirstOrDefault (cca => cca.Name == list.ClientName)
  50. 		let cca = someContext_ClientProductAlias
  51. 			.OrderByDescending (ccpa => ccpa.CreationDate)
  52. 			.FirstOrDefault (ccpa => int.Equals(ccpa.ClientID,
  53. 				ca == null ? -1 : ca.ClientID) && 
  54. 				string.Equals(ccpa.Name,list.ClientProductName))
  55. 		select new
  56. 		{
  57. 			ClientID = ca != null ? ca.ClientID : -1,
  58. 			ClientName = list.ClientName,
  59. 			ClientProductID = cca != null ? cca.ClientProductID : -1,
  60. 			ClientProductName = list.ClientProductName 
  61. 		}
  62. 		).ToList();  	
  63.  
  64. 		Console.WriteLine("Second query:");
  65. 		foreach(var result in data2) Console.WriteLine(result);
  66. 	}	
  67. }
  68.  
  69.  
  70. public class DestinctItem
  71. {
  72. 	public long ClientID { get; set; } // matches CPA.ClientID
  73. 	public string ClientName { get; set; } // matches ClieantAlias.Name
  74. 	public string ClientProductName { get; set; } // matches CPA.Name
  75. }
  76.  
  77. public class ClientAlias
  78. {
  79. 	public long ClientID { get; set; } 
  80. 	public string Name { get; set; } // matches DistinctItem.Name
  81. 	public DateTime CreationDate {get; set;}
  82. }
  83.  
  84. public class ClientProductAlias
  85. {
  86. 	public long ClientID { get; set; } // matches DestinctItem.ClientID
  87. 	public long ClientProductID { get; set; } 
  88. 	public string Name { get; set; } // matches Destinct.ClientProductName
  89. 	public DateTime CreationDate {get; set;}
  90. }
  91.  
  92.  
Success #stdin #stdout 0.07s 37528KB
comments (?)
stdin
copy 
Standard input is empty
stdout
copy 
First query:
{ ClientID = 1, ClientName = Alpha, ClientProductID = 101, ClientProductName = AAA }
Second query:
{ ClientID = 1, ClientName = Alpha, ClientProductID = 101, ClientProductName = AAA }
{ ClientID = -1, ClientName = Bravo, ClientProductID = -1, ClientProductName = BBB }
https://ideone.com/XEqf4
language:
C# (gmcs 5.20.1)
created:
12 years ago
visibility:
secret

Share or Embed source code

Discover > Sphere Engine API

The brand new service which powers Ideone!

Discover > IDE Widget

Widget for compiling and running the source code in a web browser!