How to join a constant in LINQ?

Question

I am trying to make a left join on a constant as follows:

SELECT 
    [t0].[DeviceId], 
    [t0].[DeviceTypeId], 
    [t0].[UnitId], 
    [t0].[UnitNum], 
    [t0].[ManPhone], 
    [t0].[Status], 
    [t2].[MaintDate] AS [ServiceExpiration]
FROM [dbo].[Devices] AS [t0]
    INNER JOIN [dbo].[CustomerDevices] AS [t1] 
        ON ([t0].[DeviceId]) = [t1].[DeviceId]
    LEFT JOIN [dbo].[Maintenance] AS [t2] 
        ON ([t0].[DeviceId]) = [t2].[DeviceId]
            AND 8 = [t2].ActionId

I get CS1941 when I try to do this in LINQ as follows:

var devices = from d in db.Devices
    join cd in db.CustomerDevices
        on d.DeviceId equals cd.DeviceId
    join serviceExpiration in db.Maintenances
        on 
           new { d.DeviceId, ActionId = 8 } // CS1941
               equals 
           new { serviceExpiration.DeviceId, ActionId = serviceExpiration.ActionId } into j1
    from deviceServiceExpiration in j1.DefaultIfEmpty()

Answer 1

First try in the "Support" section ActionId == 8, then use this to combine.

var expirations = from serviceExpiration in db.Maintenances
                  where serviceExpiration.ActionId == 8
                  select serviceExpiration;

var devices = from d in db.Devices
    join cd in db.CustomerDevices
        on d.DeviceId equals cd.DeviceId
    join e in expirations
        on d.DeviceId = e.DeviceId into j1
    from deviceServiceExpiration in j1.DefaultIfEmpty()
    select deviceServiceExpiration;

Answer 2

Try

var devices = from d in db.Devices
    join cd in db.CustomerDevices
        on d.DeviceId equals cd.DeviceId
    join serviceExpiration in db.Maintenances
        on d.DeviceId equals serviceExpiration.DeviceId into j1
    from deviceServiceExpiration in j1.Where(c=>c.ActionId ==8).DefaultIfEmpty()

Answer 3

Late to the party here, but I ran into the same problem and wanted to help someone in the future. The fix is ​​to join the subquery.

var devices = from d in db.Devices
    join cd in db.CustomerDevices
        on d.DeviceId equals cd.DeviceId
    join serviceExpiration in (from x in db.Maintenances where x.ActionId == 8 select x)
        on d.DeviceId equals serviceExpiration.DeviceId into j1
    from deviceServiceExpiration in j1.DefaultIfEmpty()

Hope this helps someone.

Answer 4

or inside the request:

var devices = from d in db.Devices
    join cd in db.CustomerDevices
        on d.DeviceId equals cd.DeviceId
    join serviceExpiration in db.Maintenances
        on 
           d.DeviceId
               equals 
           serviceExpiration.DeviceId into j1
    from deviceServiceExpiration in j1.DefaultIfEmpty()
        where serviceExpiration.ActionId  == 8
    select deviceServiceExpiration;