The LINQ expression could not not be translated. Either rewrite the query in a form that can be tran-CodePudding

I want to maintain child-grandchildren relationship. I need to distinguish which children ‘ItemNo’ returned which grandchildren ‘ItemNos’. A parent can have N number of children and a children can have N number of grandchildren.

Tried the below query,

 public async Task<List<KeyValuePair<string, List<string>>>> GetChild(string parentItemNo)
       {
           var childDetails = await context.Items
            .Where(x => x.ItemNo == parentItemNo)
            .SelectMany(x => x.Children.Select(c => new 
           {
              c.ItemNo,
             GrandChildItemNos = c.Children.Select(gc => gc.itemNo).ToList()
           }).ToDictionary(c => c.ItemNo, c => c. GrandChildItemNos)).ToListAsync();

but the 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.

I am expecting a data structure similar to this:

result : [{
            children: "",
            grandChildren: []
         }]

CodePudding user response：

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: