LINQ - LEFT join

Question

This is my old code:

var destinctList = HoldingsList.DistinctBy(x => new { x.ClientName, x.ProductName }).Select(x => new { ClientName = x.ClientName, ClientProductName = x.ProductName }).ToList();

foreach (var item in destinctList)
{

    var clientAlias = (from ca in someContext.ClientAlias
                       where ca.Name == item.ClientName
                       orderby ca.CreationDate descending
                       select ca).FirstOrDefault();

    if (clientAlias != null)
    {
        destinctList.Where(x => x.ClientName == item.ClientName).ForEach(x => x.ClientID = clientAlias.ClientID);
    }

    ClientProductAlias clientProductAlias = null;

    if (clientAlias != null)
    {
        clientProductAlias = (from ca in someContext.ClientProductAlias
                              where ca.Name == item.ClientProductName
                              && ca.ClientID == clientAlias.ClientID
                              orderby ca.CreationDate descending
                              select ca).FirstOrDefault();
    }

    if (clientProductAlias != null)
    {
        destinctList.Where(x => x.ClientProductName == item.ClientProductName).ForEach(x => x.ClientProductID = clientProductAlias.ClientProductID);
    }
}

This worked perfectly, but it took to long.

Now i want to make it all work with joins. This is what i have so far:

(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
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();

This is what i got so far, but is not returning anything. What am i doing wrong?

Answer 1

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