How to retain the relationship while returning children and grandchildren

Sarah 161 Reputation points
2022-02-03T10:19:53.697+00:00

I want to maintain child-grandchildren relationship. I need to distinguish which children ‘ItemNo’ returned which grandchildren ‘ItemNos’. I 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 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."

May I know where I am going wrong. Thank you.

Entity Framework Core
Entity Framework Core
A lightweight, extensible, open-source, and cross-platform version of the Entity Framework data access technology.
696 questions
ASP.NET Core
ASP.NET Core
A set of technologies in the .NET Framework for building web applications and XML web services.
4,148 questions
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,665 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Ronen Ariely 15,096 Reputation points
    2022-02-03T22:41:10.81+00:00

    Hi,

    Hope this is what was needed.

    yes this is what I asked for +1

    But in the meantime after I wrote (actually in the same time since when I submitted my respond I saw the change), you change the question and added the .Net entity framework elaboration :-)

    Something like var Item = db.Item.Include(e => e.Children).ToList();

    Check this solution and read the comments for more info:

    https://stackoverflow.com/questions/41827918/loading-a-full-hierarchy-from-a-self-referencing-table-with-entityframework-core

    2 people found this answer helpful.
    0 comments No comments