score:3

Accepted answer

I noticed that your DefaultIfEmpty statements were missing parenthesis and also I think the problem is how it's handling joins to null data. I think you definitely need the where j1.Any() call and you might also need the where list_client_join.Any().

(from list in destinctList 
join ca in someContext.ClientAlias on list.ClientName equals ca.Name into list_client_join 
//where list_client_join.Any ()
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(); 

Update:

// alternate query
(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();     

Mock up using data: http://ideone.com/XEqf4


Related Articles