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: PostgreSQL as alternate database provider #453 (comment)
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

[holow29]

Just wanted to say I still see this error. (I haven't yet tested the new unified self-host! This is on the "normal" self-host version 2022.11.1; otherwise same environment as above) I do see that over the last 1-2 months, some work has gone into fixing up EF queries and joins. I'm guessing these haven't landed in this version of self-host yet, but please lmk if I am wrong.
Also - if unified self-host will be the only way to officially use a different DB system, I suppose I should test that out soon.

[justindbaur]

This should be fixed in #2473 but you are right it's not in 2022.11.1 it will be in 2022.12.0 which you can keep an eye out for in a couple weeks.

[justindbaur]

@holow29, since this fix is on both the beta and dev tag of unified and will be release in a couple days for standard deployment I am going to close this now. Feel free to reopen if those releases don't fix your issue.