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 !!