The LINQ expression could not not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly

Question

I want to maintain child-grandchildren relationship. I need to distinguish which children returned which grandchildren.

Query returns

"InvalidOperationException: The LINQ expression 
'x =>x.Children.AsQueryable().Select(c => new 
 {
 ItemNo= c.ItemNo,
 GrandChildItemNos = c.Children.AsQueryable().Select(gc => gc.ItemNo).ToList()
 })
.ToDictionary(keySelector: c => c.ItemNo, elementSelector: c => c.GrandChildItemNos)' could 
 not be translated. Either rewrite the query in a form that can be translated, or switch to 
 client evaluation explicitly by inserting a call to 'AsEnumerable', 
'AsAsyncEnumerable','ToList', or 'ToListAsync'. See 
 https://go.microsoft.com/fwlink/?linkid=2101038 for more information."

I think EF has problem translating .ToDictionary() part to SQL. Can anyone help on this.

Answer 1

You're close!

When you move the ToDictionary() beyond the point of materializing (the ToListAsync()) it will work. This is in fact what the error message also says: "or switch to client evaluation explicitly". The ToDictionary call cannot be translated into a query.

var childDetails = await context.Items.Where(w => w.ItemNo == parentItemNo)
    .SelectMany(x => x.Children
        .Select(c => new
        {
            c.ItemNo,
            GrandChildItemNos = c.Children.Select(gc => gc.ItemNo)
        })
    )
    .ToListAsync();
var dictionary = childDetails.ToDictionary(d => d.ItemNo, d => d.GrandChildItemNos);

This is my result: