using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;

public class Test
{
	public static void Main()
	{
		var destinctList = new List<DestinctItem>()
		{
		new DestinctItem() { ClientName = "Alpha", ClientID = 1, ClientProductName="AAA" },
		new DestinctItem() { ClientName = "Bravo", ClientID = 2, ClientProductName="BBB" }
		};
		
		var someContext_ClientAlias = new List<ClientAlias>()
		{
			new ClientAlias() { Name = "Alpha", ClientID = 1, CreationDate = DateTime.Now.AddDays(-7) }
		};
		
		var someContext_ClientProductAlias = new List<ClientProductAlias>()
		{
			new ClientProductAlias() { Name = "AAA", ClientID = 1, ClientProductID = 101, CreationDate = DateTime.Now.AddDays(-7) }
		};
	
		var data = (from list in destinctList  
		join ca in someContext_ClientAlias on list.ClientName equals ca.Name into list_client_join  
		from list_client in list_client_join.DefaultIfEmpty()  
		join cpa in someContext_ClientProductAlias on new { ClientID = (long)list.ClientID, Name = list.ClientProductName } equals  
		new { cpa.ClientID, cpa.Name } into j1  
		// maybe needs the following: 
		where j1.Any () 
		from j2 in j1.DefaultIfEmpty()  
		orderby list_client.CreationDate descending  
		orderby j2.CreationDate descending  
		select new { ClientID = list_client.ClientID,   
			ClientName = list.ClientName,   
			ClientProductID = j2.ClientProductID,   
			ClientProductName = list.ClientProductName }
			).ToList();  	
	
		Console.WriteLine("First query:");
		foreach(var result in data) Console.WriteLine(result);
		
		// alternate query
		var data2 = (from list in destinctList  
		let ca = someContext_ClientAlias
			.OrderByDescending (cca => cca.CreationDate)
			.FirstOrDefault (cca => cca.Name == list.ClientName)
		let cca = someContext_ClientProductAlias
			.OrderByDescending (ccpa => ccpa.CreationDate)
			.FirstOrDefault (ccpa => int.Equals(ccpa.ClientID,
				ca == null ? -1 : ca.ClientID) && 
				string.Equals(ccpa.Name,list.ClientProductName))
		select new
		{
			ClientID = ca != null ? ca.ClientID : -1,
			ClientName = list.ClientName,
			ClientProductID = cca != null ? cca.ClientProductID : -1,
			ClientProductName = list.ClientProductName 
		}
		).ToList();  	
	
		Console.WriteLine("Second query:");
		foreach(var result in data2) Console.WriteLine(result);
	}	
}


public class DestinctItem
{
	public long ClientID { get; set; } // matches CPA.ClientID
	public string ClientName { get; set; } // matches ClieantAlias.Name
	public string ClientProductName { get; set; } // matches CPA.Name
}

public class ClientAlias
{
	public long ClientID { get; set; } 
	public string Name { get; set; } // matches DistinctItem.Name
	public DateTime CreationDate {get; set;}
}

public class ClientProductAlias
{
	public long ClientID { get; set; } // matches DestinctItem.ClientID
	public long ClientProductID { get; set; } 
	public string Name { get; set; } // matches Destinct.ClientProductName
	public DateTime CreationDate {get; set;}
}