LINQ Join on multiple datasets with NULL elements

Question

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

[Left outer join in LINQ](http://stackoverflow.com/questions/3404975/left-outer-join-in-linq) — stuartd, Sep 21 '16 at 11:28
it is showing the PC name and values which contains all the values like computer,IP, protection status, cpu percentage and update. you can see PC 3 contains all the values so its showing only pc 3 and the values. — JeetDaloneboy, Sep 21 '16 at 11:29
This is exactly what I tried. I have a solution using 3 left outer join LINQ query, but I want to pack it all in a single simplified version. — Jan Kruse, Sep 21 '16 at 11:30
@JettDaloneboy I know why it only returns this, my question is how to make it return all rows — Jan Kruse, Sep 21 '16 at 11:31
See msdn samples for Linq left outer join : https://code.msdn.microsoft.com/101-LINQ-Samples-3fb9811b — jdweng, Sep 21 '16 at 11:53

score 1 · Accepted Answer · answered Sep 21 '16 at 11:53

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: