LEFT OUTER JOIN with Condition in EF-CodePudding

I want the following query in EF (LINQ or Lamda).

SELECT S.Id, 
       S.Name, 
       S.Description, 
       S.Active, 
       R.Id AS RoleFunctionId, 
       R.SystemRoleId, 
       ISNULL(R.AllowView, 0) AS AllowView, 
       ISNULL(R.AllowAdd, 0) AS AllowAdd, 
       ISNULL(R.AllowEdit, 0) AS AllowEdit, 
       ISNULL(R.AllowDelete, 0) AS AllowDelete 
FROM SystemRoleFunction AS S 
  LEFT OUTER JOIN RoleFunction AS R 
    ON S.Id = R.SystemRoleFunctionId 
   AND R.SystemRoleId = 21 
ORDER BY S.Id

But I'm confused since the query comes with LEFT OUTER JOIN and a AND expression. That is the tricky part here. When I put the where condition or the AND clause at the end, I'm not getting the expected result when I do it in EF.

I want to see all records from Table S even though Table R doesn't have any respected records.

When I tried the following way, I'm getting "The cast to value type 'System.Guid' failed because the materialized value is null. Either the result type's generic parameter or the query must use a nullable type." error. Because some records return null from Table R.

var b = await (from S in _db.SystemRoleFunctions
                        join z in _db.RoleFunctions on S.Id equals z.SystemRoleFunctionId into fg
                        from R in fg.Where(x => x.SystemRoleId == 21).DefaultIfEmpty()
                               select new
                               {
                                   S.Name,
                                   S.Description,
                                   S.Active,
                                   S.Tstamp,
                                   R.Id,
                                   R.SystemRoleId,
                                   R.SystemRoleFunctionId,
                                   AllowView = R == null ? false : R.AllowView,
                                   AllowAdd = R == null ? false : R.AllowAdd,
                                   AllowEdit = R == null ? false : R.AllowEdit,
                                   AllowDelete = R == null ? false : R.AllowDelete,
                               }).ToListAsync();

CodePudding user response：

I managed to fix the issue in the following way. Thanks to @GuidoG to pointing me out.

                var a = await (from S in _db.SystemRoleFunctions
                           join z in _db.RoleFunctions.Where(x => x.SystemRoleId == 21) on S.Id equals z.SystemRoleFunctionId into jointable
                           from R in jointable.DefaultIfEmpty()
                           select new
                           {
                               S.Name,
                               S.Description,
                               S.Active,
                               S.Tstamp,
                               SystemRoleId = R == null ? 21: R.SystemRoleId,
                               RoleFunctionId = R == null ? Guid.NewGuid() : R.Id,
                               SystemRoleFunctionId = R == null ? S.Id : R.SystemRoleFunctionId,
                               AllowView = R == null ? false : R.AllowView,
                               AllowAdd = R == null ? false : R.AllowAdd,
                               AllowEdit = R == null ? false : R.AllowEdit,
                               AllowDelete = R == null ? false : R.AllowDelete,
                           }).ToListAsync();

CodePudding user response：

This is simplified version and documentation: Collection selector references outer in a where clause

var a = await (
    from S in _db.SystemRoleFunctions
    from R in _db.RoleFunctions
        .Where(x => x.SystemRoleId == 21 && S.Id == x.SystemRoleFunctionId)
        .DefaultIfEmpty()
    select new
    {
        S.Name,
        S.Description,
        S.Active,
        S.Tstamp,
        SystemRoleId = R == null ? 21: R.SystemRoleId,
        RoleFunctionId = R == null ? Guid.NewGuid() : R.Id,
        SystemRoleFunctionId = R == null ? S.Id : R.SystemRoleFunctionId,
        AllowView = R == null ? false : R.AllowView,
        AllowAdd = R == null ? false : R.AllowAdd,
        AllowEdit = R == null ? false : R.AllowEdit,
        AllowDelete = R == null ? false : R.AllowDelete,
    }
    ).ToListAsync();