DataService query get NULL entries form left join

Question

I have a simple SQL Query which I'm trying to translate to linq. But I keep getting

"...is not supported"

The Query is:

SELECT HARDWARE_ID, HWName, HWTyp_FK from T_HARDWARE
left join T_SWInstalled on T_HARDWARE.HARDWARE_ID = T_SWInstalled.SIHardware_FK
and SISWVerzeichnis_FK = 213
where SWInstalled_id is null

My try with linq:

var cni = from hardware in _context.T_HARDWARE
          where hardware.T_SWInstalled.Where(si => si.SISWVerzeichnis_FK == _softwareId) == null
          select new InstalledOnListItem(hardware.HARDWARE_ID, hardware.HWName);

Is this actually only possible with raw SQL? Thanks

Answer 1

Use join syntax:

var query =
   from hardware in _context.T_HARDWARE
   join sw in  _context.T_SWInstalled
   on new {
        JoinProperty1 = hardware.HARDWARE_ID,
        JoinProperty2 = sw.SISWVerzeichnis_FK
          }
   equals
      new { 
        JoinProperty1 = sw.SIHardware_FK,
        JoinProperty2 = 213
          }
   where sw.SWInstalled_id == null
   select new InstalledOnListItem(hardware.HARDWARE_ID, hardware.HWName);