Convert IQueryable to IList in nested collection

Question

I have this entities from Entity Framework: Parent, Child, GrandChild and equivalent entities ParentModel and ChildModel.

Simplified:

class ParentModel
{
    public IList<ChildModel> Children { get; set; }
}

class ChildModel
{
    public string GrandChild { get; set; }
}

On Parent extension i have method "ToModel"

public static IEnumerable<ProductCategoryModel> ToModel(
    this IQueryable<ProductCategory> query)
{
    IList<ParentModel> model = 
       query.Select(p => new ParentModel { 
                            Childs = p.Childs.Select(ch => new ChildModel { 
                                                 Grandchild = ch.Grandchild.Code
                                              }).ToList() 
                    }).ToList();

    return model;
}

Problem is that it does not work. I know why - nested ToList() method can't be run on DB side.

Is any easy solution how write correct equivalent code which will work OK, will be simple? I see some solution in foreach, but in my opinion, it will not be nice.

Answer 1

You would work in two steps:

1. Get the data you want
2. Bring it into the form you want

public static IEnumerable<ProductCategoryModel> ToModel(
    this IQueryable<ProductCategory> query)
{
    return query.Select(p => new
                        {
                          Children = p.Childs
                                      .Select(ch => new ChildModel()
                                              { 
                                                Grandchild = ch.Grandchild.Code
                                              })
                        })
                .AsEnumerable()
                .Select(x => new ParentModel { Children = x.Children.ToList() })
                .ToList();
}