EF: bitwarden-api: Collections not shown to users - query can't be translated

[holow29]

Steps To Reproduce

1. Setup Bitwarden self-hosted without MSSQL and using MySQL as detailed here: https://github.com/bitwarden/server/issues/453#issuecomment-1049153649
2. Create organization
3. Go back to personal vault
4. Look for Collections under "FOLDERS" filter
5. Add an item to vault
6. Try to move item to organization collection by clicking the gear and then "Move to organization"
7. Add another item to vault and try to put ownership as the organization

Expected Result

• I should be able to see collections (that I have permission to see) under "FOLDERS" in the personal vault filters.
• I should be able to see collections (that I have permission to see) in the "Move to organization" flow.
• I should be able to see collections (that I have permissions to see) if I try to set the owner of a new item as the organization.

Actual Result

Collections do not appear in the personal vault filters, the "Move to organization" flow, or the new item flow when trying to create it from the personal vault and set the owner as the organization.

Additionally, at one point, I got an error message in bitwarden-api:

[Error] The LINQ expression 'DbSet()
    .Where(cc => cc.CipherId == __cipherId_0)
    .Select(cc => new { 
        CollectionId = cc.CollectionId, 
        CipherId = cc.CipherId
     })
    .LeftJoin(
        inner: __p_1
            .AsQueryable(), 
        outerKeySelector: t => t.CollectionId, 
        innerKeySelector: s => s.CollectionId, 
        resultSelector: (t, s) => new { 
            h__TransparentIdentifier0 = new { 
                t = t, 
                s_g = s_g
             }, 
            s = s
         })' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.
at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.Expand(Expression query)
   at Microsoft.EntityFrameworkCore.Query.QueryTranslationPreprocessor.Process(Expression query)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryTranslationPreprocessor.Process(Expression query)
   at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.c__DisplayClass9_0`1.b__0()
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.GetEnumerator()
   at Microsoft.EntityFrameworkCore.DbContext.AddRangeAsync(IEnumerable`1 entities, CancellationToken cancellationToken)
   at Bit.Infrastructure.EntityFramework.Repositories.CollectionCipherRepository.UpdateCollectionsForAdminAsync(Guid cipherId, Guid organizationId, IEnumerable`1 collectionIds) in /home/runner/work/server/server/src/Infrastructure.EntityFramework/Repositories/CollectionCipherRepository.cs:line 187
   at Bit.Core.Services.CipherService.SaveCollectionsAsync(Cipher cipher, IEnumerable`1 collectionIds, Guid savingUserId, Boolean orgAdmin)
   at Bit.Api.Controllers.CiphersController.PutCollectionsAdmin(String id, CipherCollectionsRequestModel model) in /home/runner/work/server/server/src/Api/Controllers/CiphersController.cs:line 357
   at lambda_method1572(Closure , Object )
   at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.AwaitableResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.g__Awaited|12_0(ControllerActionInvoker invoker, ValueTask`1 actionResultValueTask)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.g__Awaited|13_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g__Awaited|25_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)

Additional Context

Collections seem to appear normally in the Organization view (as opposed to the personal vault view). Also, I tried to reproduce the error, but I could not get the error message again for some reason. The collections still do not appear, however, in the correct Personal Vault views. I'm also getting some CorsPolicyService errors from the Identity module, but I'm not sure they are related. (Which might be because of misconfiguration on my end, but I don't think it affects the above.) Also, I should note that I am not running the bitwarden-admin module because of MSSQL reliance #1997.

Build Version

1.48.1

Environment

Self-Hosted

Environment Details

Docker on linux using MariaDB