Jan Kruse Jan Kruse - 2 months ago 5
C# Question

LINQ Join on multiple datasets with NULL elements

Datasets



Heartbeats



Computer IP
__________________________
PC 1 192.168.2.1
PC 2 192.168.2.2
PC 3 192.168.2.3





Protection



Computer Protection Status
__________________________
PC 1 Protected
PC 3 Not Protected





Performance



Computer CPU Percentage
__________________________
PC 1 52%
PC 3 23%





Updates



Computer Updates
__________________________
PC 2 206
PC 3 127





What I want:



Perform a LINQ query on the datasets returning the following output:

Computer IP Protection Status CPU Percentage Updates
____________________________________________________________________________
PC 1 192.168.2.1 Protected 52%
PC 2 192.168.2.2 206
PC 3 192.168.2.3 Not Protected 23% 127





What I tried:



var joined = from heartbeat in heartbeats
join protection in protections on heartbeat.Computer equals protection.Computer into protectionJoined
from p in protectionJoined.DefaultIfEmpty()
join perf in performance on p.Computer equals perf.Computer into performaceJoined
from x in performaceJoined.DefaultIfEmpty()
join update in updates on x.Computer equals update.Computer into final
from e in final.DefaultIfEmpty()
select new ServerOverview
{
ComputerName = heartbeat.Computer,
ComputerIP = heartbeat.ComputerIP,
ProtectionStatus = p == null ? null : p.ProtectionStatus,
CPUPercent = x == null ? -1 : x.CPUPercent,
Updates = e == null ? -1 : e.Updates
};


But this lacks the rows with missing columns returning:

Computer IP Protection Status CPU Percentage Updates
____________________________________________________________________________
PC 3 192.168.2.3 Not Protected 23% 127

Answer

This code will return what you want. The assumption is that the Computer name is not repeated in any of the data sets, i.e. it is a unique identifier.

var summary = heartbeats.Select(h =>
{
    var prot = protections.SingleOrDefault(p => p.Computer == h.Computer);
    var perf = performance.SingleOrDefault(p => p.Computer == h.Computer);
    var updt = updates.SingleOrDefault(u => u.Computer == h.Computer);

    return new
    {
        Computer = h.Computer,
        IP = h.IP,
        ProtectionStatus = prot?.ProtectionStatus,
        CpuPerformance = perf?.CpuPercentage,
        Updates = updt?.Updates
    };
}).ToList();

This will return the following collection:

enter image description here