'EF Core : expression trees - GroupJoin transform to SelectMany dynamically

I would like to transform the below query:

(from documentType in entitySet
 join userGroupId in Repository.ConvertToBigIntTable(userGroupIds, "userGroupId")
      on documentType.Id equals userGroupId.Id into UserGroupIds
 from userGroupId in UserGroupIds.DefaultIfEmpty()
 join documentTypePermission in Repository.DocumentTypePermissions
      on documentType.Id equals documentTypePermission.DocumentTypeId
 join userSelectionParam in Repository.UserSelectionParams
      on documentTypePermission.UserSelectionId equals userSelectionParam.Id
 where documentTypePermission.IsActive && 
       ((userSelectionParam.UserGroupId != null && userGroupId.Id != null)
        || (userSelectionParam.UserId != null && userSelectionParam.UserId == CurrentUserId))
 select documentTypePermission);

to something like this:


var query = 
    from documentType in entitySet
    from userGroupId in Repository.ConvertToBigIntTable(userGroupIds, "userGroupId")
        .Where(userGroupId => documentType.Id == userGroupId.Id)
        .DefaultIfEmpty()
    join documentTypePermission in Repository.DocumentTypePermissions
        on documentType.Id equals documentTypePermission.DocumentTypeId
    join userSelectionParam in Repository.UserSelectionParams
        on documentTypePermission.UserSelectionId equals userSelectionParam.Id

    ....

Note:

  1. I have the setup ready for intercepting the expression tree evaluation using QueryTranslationPreprocessor

  2. I need the logic to generate the output of .Call, .SelectMany etc by using the first linq and then translating the same to second linq query shown above

  3. The caveats are that internally the expression engine generates so many Anonymous types that I am unable to write a generic code that would suffice different cases of linq with such groupjoins

Reason to do this:

  1. There are several kinds of GroupJoin issues in EF Core 6 which the community isn’t ready to resolve

  2. I can't make the change to Linq query directly as there are 1000s of them and in several places



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source