left join linq with a where clause-CodePudding

I am attempting to do this sql left join in linq.

SELECT 
    romm.rommid,
    wetx.target_data_entity_type_id AS busprocid 
FROM 
    romm 
LEFT OUTER JOIN 
    Work_Effort_Type_Xref wetx ON romm.busprocid = wetx.source_data_entity_type_id 
                               AND wetx.source_data_entity_name = ‘BusProc’ 
WHERE 
    romm.acttypeid = 1

And I have this Linq:

 var query = from romm in RoMM
             join wetx in WorkEfforTypeXRef on romm.BusProcId equals wetx.SourceDataEntityTypeId into Group
             from roup in Group.DefaultIfEmpty()
             where romm.ActTypeId == 1 && roup.SourceDataEntityTypeName == "BusProc"
             select new
                    {
                        romm.RoMmid,
                        roup.SourceDataEntityTypeName
                    };

When I execute this, I get an error:

Object reference not set to an instance of the object

on the roup side.

Any help would be amazing... I cannot seem to get it working.

CodePudding user response：

One way that would be closer to the SQL join clause would be to include that second condition in your Linq join clause:

join wetx in WorkEfforTypeXRef 
      on new {a = romm.BusProcId,              b = "BusProc"}
  equals new {a = wetx.SourceDataEntityTypeId, b = wetx.SourceDataEntityTypeName}
  into Group

which prevents the NRE in the Where since you don't neet to reference wetx in the Where at this point. To prevent it in select just use ?.:

select new
{
    romm.RoMmid,
    roup?.SourceDataEntityTypeName
};

CodePudding user response：

Sounds like the "roup" object is null try changing that to:

var query = from romm in RoMM
             join wetx in WorkEfforTypeXRef on romm.BusProcId equals wetx.SourceDataEntityTypeId into Group
             from roup in Group.DefaultIfEmpty()
             where romm.ActTypeId == 1 && roup.SourceDataEntityTypeName == "BusProc"
             select new
                    {
                        romm.RoMmid,
                        SourceDataEntityTypeName = roup?.SourceDataEntityTypeName
                    };

CodePudding user response：

Assuming you have Navigation Properties configured correctly then this should work.

var query = RoMM
            .Include(_ => _.WorkEfforTypeXRef)
            .Where(_ => _.ActTypeId == 1 &&
                        _.SourceDataEntityTypeName == "BusProc")
            .Select(x => new 
                   {
                     x.RoMmid,
                     SourceDataEntityTypeName = x.WorkEfforTypeXRef.SourceDataEntityTypeName
                   });