EF Core Migration Error: The query contains a projection '<>h_ Transparent Identifier 0 Collections in the final projection must be an 'IEnumerable<T>' type such as 'List<T>'.

Problem

I am migrating my code from EF Core 3.X to EF Core 5.X. I am using the following code to get records from the database

    RolePrivilege = (from T in _db.Privilege
                               join RT in _db.RolePrivilege on T.PrivilegeID equals RT.PrivilegeID
                               let T2 = from UR in _db.URole
                                        where UR.UserID == user.UserId
                                        select UR.RoleID
                               where T2.Contains(RT.RoleID)
                               select new Privilege
                               {
                                   PrivilegeName = T.PrivilegeName.Trim(),
                                   PrivilegeType = T.PrivilegeType.Trim()

                               }).ToList<Privilege>();

I got the following error after migration.

The query contains a projection '<>h__TransparentIdentifier0 => DbSet<URole>()
.Where(UR => UR.UserID == __user_UserId_0)
.Select(UR => UR.RoleID)' of type 'IQueryable<long>'. Collections in the final projection must be an 
'IEnumerable<T>' type such as 'List<T>'. Consider using 'ToList' or some other mechanism to convert 
the 'IQueryable<T>' or 'IOrderedEnumerable<T>' into an 'IEnumerable<T>'.

The error seems to be something related to the "let" keyword

Solution

This is a known issue when the code is migrated from EF Core 3.X to EF Core 5.X. A workaround to fix this issue is adding ToList() at the end of the subquery defined in the "let" as follows:

    RolePrivilege = (from T in _db.Privilege
                               join RT in _db.RolePrivilege on T.PrivilegeID equals RT.PrivilegeID
                               let T2 = (from UR in _db.URole
                                        where UR.UserID == user.UserId
                                        select UR.RoleID).ToList()
                               where T2.Contains(RT.RoleID)
                               select new Privilege
                               {
                                   PrivilegeName = T.PrivilegeName.Trim(),
                                   PrivilegeType = T.PrivilegeType.Trim()

                               }).ToList<Privilege>();

Happy Coding !!