EF Core ToDictionary throw expression could not be translated-CodePudding

I have this query:

        Project = await Context.Projects.Where(x => x.Id == project.Id)
        .Select(x => new ProjectModel
        {
            Id = project.Id,
            Name = project.Name,                
            TreeDataDict = x.Tasks.Select(y => new TreeItemModel()
            {
                NodeId = y.Id,
                ParentId = SetTaskParentId(y, y.Type),
                NodeTitle = y.Name,
                Expanded = false,
                Object = new TaskBaseModel
                {
                    Milestone = y.Milestone,
                    StartDate = y.StartDate,
                    CurrentEndDate = y.CurrentEndDate,
                    EndDate = y.EndDate,
                },
                Icon = TaskHelper.GetTaskIcon(y.Type),
                Children = new List<TreeItemModel>()
            }).ToDictionary(y => y.NodeId, y => y)                              
        }).SingleOrDefaultAsync();

and also tried like this:

        Project = await Context.Projects.Where(x => x.Id == project.Id)
        .Select(x => new ProjectModel
        {
            Id = project.Id,
            Name = project.Name,                
            TreeDataDict = x.Tasks.ToDictionary(y => y.Id, y => new TreeItemModel(
            {
                NodeId = y.Id,
                ParentId = SetTaskParentId(y, y.Type),
                NodeTitle = y.Name,
                Expanded = false,
                Object = new TaskBaseModel
                {
                    Milestone = y.Milestone,
                    StartDate = y.StartDate,
                    CurrentEndDate = y.CurrentEndDate,
                    EndDate = y.EndDate,
                },
                Icon = TaskHelper.GetTaskIcon(y.Type),
                Children = new List<TreeItemModel>()
            })                              
        }).SingleOrDefaultAsync();

Both ways I got this exception:

What could be causing this? and is there a way I could make this work without fetching as a list and then covert it to dictionary? What could be the most efficient way to achieve this?

Regards

CodePudding user response：

What could be causing this?

Translation of the nested ToDictionary call (none of the available overloads) is not supported. Consider it one of the (many) current (latest at the time of writing official v5.0.11) EF Core shortcomings.

Interestingly though, the Dictionary<TKey, TValue> constructor overload with IEnumerable<KeyValuePair<TKey, TValue>> argument as well as projection (Select) to KeyValuePair<TKey, TValue> is supported, which gives the workaround - replace the ToDictionary call with the aforementioned constructor and Select, e.g. (replace int with the type of the Id)

TreeDataDict = new Dictionary<int, TreeItemModel>(
x.Tasks.Select(y => new TreeItemModel()
{
    NodeId = y.Id,
    ParentId = SetTaskParentId(y, y.Type),
    NodeTitle = y.Name,
    Expanded = false,
    Object = new TaskBaseModel
    {
        Milestone = y.Milestone,
        StartDate = y.StartDate,
        CurrentEndDate = y.CurrentEndDate,
        EndDate = y.EndDate,
    },
    Icon = TaskHelper.GetTaskIcon(y.Type),
    Children = new List<TreeItemModel>()
})
.Select(e => new KeyValuePair<int, TreeItemModel>(e.NodeId, e)));

This will fix the current error. But note that you are using other non-translatable constructs (custom method calls like SetTaskParentId(y, y.Type) and TaskHelper.GetTaskIcon(y.Type)) which are supported only in the final Select, so make sure to not add LINQ operator like Where, OrderBy etc. after the root query Select(x => new ProjectModel { ... }, otherwise you'll get other runtime errors. ToList, `First