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;}
}