Cannot perform SelectMany – Unable to materialize collection

query :

    var nodes = (from n in _db.TreeNodes.Where(n => passwordCycles.Select(c => c.HorusId).Contains(n.HorusId))
                     from path in _db.TreeNodes.Where(p => (EF.Functions.Like(p.Path, n.Path + "%")) && p.Type == TreeNodeType.User)
                     select new { path.HorusId, path.HorusParentPathList });

    var usersNodes = nodes
                     .SelectMany(e => e.HorusParentPathList.Select(l => new { HorusParentRow = l, HorusId = e.HorusId }))
                     .Distinct();

class TreeNode :

public class TreeNode : IEntity
{
    public TreeNode()
    {
        DateCreated = DateTime.Now;
        DateModified = DateCreated;
    }

    [Key]
    public Guid TreeNodeId { get; set; }

    public Guid? ParentId { get; set; }

    public virtual TreeNode Parent { get; set; }

    public virtual ICollection<TreeNode> Children { get; set; } = new HashSet<TreeNode>();

    public Guid RootId { get; set; }

    public virtual TreeNode Root { get; set; }

    [StringLength(255)]
    public string Name { get; set; }

    [StringLength(255)]
    public string DisplayName { get; set; }

    public Guid HorusId { get; set; }

    public Guid? ParentHorusId { get; set; }

    public TreeNodeType Type { get; set; }

    public int? TypeOrder { get; set; }

    public int Level { get; set; }

    [StringLength(450)]
    public string Path { get; set; }

    [NotMapped]
    public List<Guid> PathList => GetPathList(Path);

    [StringLength(450)]
    public string ParentPath { get; set; }

    [NotMapped]
    public List<Guid> ParentPathList => GetPathList(ParentPath);

    [StringLength(450)]
    public string HorusPath { get; set; }

    [NotMapped]
    public List<Guid> HorusPathList => GetPathList(HorusPath);

    [StringLength(450)]
    public string HorusParentPath { get; set; }

    [NotMapped]
    public List<Guid> HorusParentPathList => GetPathList(HorusParentPath);

    public string FullName { get; set; }

    [NotMapped]
    public List<string> FullNameList => string.IsNullOrEmpty(FullName) ? null : FullName.Split(new[] { " -> " }, StringSplitOptions.RemoveEmptyEntries).ToList();

    public string VisualizationName { get; set; }

    public int? NbRetentionDoc { get; set; }

    public int? NbRetentionDocInherit { get; set; }

    public DateTime? ActivationDate { get; set; }

    public DateTime? DeactivationDate { get; set; }

    public DateTime DateCreated { get; set; }

    public DateTime DateModified { get; set; }

    [NotMapped]
    private string DebuggerDisplay => $"{nameof(TreeNode)}: {Type} - {Name}";

    private static List<Guid> GetPathList(string path)
    {
        return string.IsNullOrEmpty(path) ? null : path.Split(new[] { '\\' }, StringSplitOptions.RemoveEmptyEntries).Select(Guid.Parse).ToList();
    }
}  

It want to perform a mapping with the HorusParentPathList and the HorusId.I also want to retrieve an iqueryable. Howewer I am force to use AsEnumerable since the HorusParentPathList cannot be loop in database. Is there any solution to keep usersNodes to Iqueryable since I don’t want to pull the data in memory ?

EF Core cannot translate custom functions, unless you jump through a bunch of hoops involving Expressions and Visitors.

One option is maybe to create a function returning IQueryable, but I don’t think that will work here because it’s used in a nested fashion.

Instead just combine the code into one

var usersNodes = (
    from e in nodes
    from l in path.Split("\\").Where(s => s != "").Select(Guid.Parse)
    select new { HorusParentRow = l, HorusId = e.HorusId }
    ).Distinct();

Whether or not Split or Guid.Parse can be translated is another question, and dependent on your DBMS and EF provider