SQL to Linq Lambda with Multiple Tables, Cases, and Aggregate (Group By, Count)

Question

Given the following tables:

UnitAlertStatus
ID
AlertedUnitID
UnitAlertStatus
EpochStamp

Unit
ID
HardwareID
Name
UnitTypeID

UnitType
ID
Name

What would the equivalent LINQ Lambda be for the following SQL?

Select [AlertedUnitID]
, Count(case when [AlertStatus] = 1 then [UnitAlertStatus].[ID] end) as Count_Caution
, Count(case when [AlertStatus] = 3 then [UnitAlertStatus].[ID] end) as Count_Alarm
, Count(case when [AlertStatus] = 7 then [UnitAlertStatus].[ID] end) as Count_Critical
, [Unit].[HardwareID] as Unit_HardwareID, [Unit].[Name] as Unit_Name, [UnitType].[Name] As UnitType_Name
From [UnitAlertStatus], [Unit], [UnitType]
Where [UnitAlertStatus].[AlertedUnitID] = [Unit].[ID] And [UnitType].[ID] = [Unit].[UnitTypeID] And [EpochStamp] > 1698150366500
Group By [AlertedUnitID], [Unit.HardwareID], [Unit].[Name], [UnitType].[Name]

Answer 1

var result = context.UnitAlertStatus
    .Join(context.Unit, uas => uas.AlertedUnitID, u => e.ID, (uas, u) => new { uas, u })
    .Join(context.UnitType, temp => temp.u.UnitTypeID, ut => ut.ID, (temp, ut) => new { temp.uas, temp.u, ut })
    .Where(joined => joined.uas.EpochStamp > 1698150366500)
    .GroupBy(joined => new
    {
        AlertedUnitID = joined.uas.AlertedUnitID,
        Unit_HardwareID = joined.u.HardwareID,
        Unit_Name = joined.u.Name,
        UnitType_Name = joined.ut.Name
    })
    .Select(group => new
    {
        AlertedUnitID = group.Key.AlertedUnitID,
        Count_Caution = group.Count(g => g.uas.AlertStatus == 1),
        Count_Alarm = group.Count(g => g.uas.AlertStatus == 3),
        Count_Critical = group.Count(g => g.uas.AlertStatus == 7),
        Unit_HardwareID = group.Key.Unit_HardwareID,
        Unit_Name = group.Key.Unit_Name,
        UnitType_Name = group.Key.UnitType_Name
    });

In the above, we first join the UnitAlertStatus, Unit, and UnitType tables, apply the Where filter, group the results by the specified columns, and then use Count to calculate the counts for different AlertStatus values.