Flip a LINQ object

Question

I have a LINQ object and i want to be able to flip it so that instead of the hierarchy Orders -> Articles -> Labels, i get Articles -> Labels -> Orders.

This simply means that i get an Article, and all its underlying labels. Then i check which ORDERS does this article belong to, then i add them as a collection.

var result = await _unitOfWork.Repository<OrderGroups>().FindByIncludeAsync(
               x => GroupIdsClaim.Contains(x.GroupId), b => 
               b.Include(v => v.Order)
                    .ThenInclude(h => h.OrderArticles)
                    .ThenInclude(t => t.Article)
                    .ThenInclude(q => q.LabelArticles)
                    .ThenInclude(p => p.Label));

var orderArticlesLabels = result.Select(x => new
{
    x.OrderId,
    x.Order.OrderNumber,
    Articles = x.Order.OrderArticles.Select(v => new
    {
        v.ArticleId,
        v.Article.Name,
        Labels = v.Article.LabelArticles.Select(k => new
        {
            k.LabelId,
            k.Label.Name
         })
    })
});

I expect the output of

  {
    "articleId" = xx,
    "name" = "yyy"
    "labels" : [],
    "orders" : []
  }

But am getting

[
  {
    "orderId": 1,
    "orderNumber": "42253",
    "articles": [
      {
        "articleId": 4,
        "name": "Truck",
        "labels": [{"labelId": 1, "name": "label1"}]
      }
    ]
  },
  {
    "orderId": 2,
    "orderNumber": "42254",
    "articles": [{
        "articleId": 4,
         "name": "Truck",
        "labels": [{"labelId": 1, "name": "label1"}]
      }
    ]
  },
  {
    "orderId": 3,
    "orderNumber": "42255",
    "articles": [
      {
        "articleId": 2,
        "name": "Roads",
        "labels": [{"labelId": 1,"name": "label1"}]
      },
      {
        "articleId": 4,
        "name": "Truck",
        "labels": [{"labelId": 1,"name": "label1"}]
      }
    ]
  }]
````

Answer 1

You shouldn't rely on client-side relationship fixups to achieve this. That imposes severe limitations such as an inability to paginate or filter results. The general rule is to start navigating the relationship at the primary entity you would like to retrieve. In this case that would be Articles.

var result = await dbContext.Articles.Where(a => 
                     a.OrderArticles.Any(oa => 
                       oa.Order.OrderGroups.Any(og => GroupIdsClaim.Contains(og.GroupId))))
                  .Include(a => a.LabelArticles)
                  .ThenInclude(la => la.Label)
                  .Select(a => new {
                      ArticleId = a.Id,
                      a.Name
                      Labels = a.LabelArticles.Select(la => la.Label)
                      Orders = a.OrderArticles.Select(oa => oa.Order)         
                  }).Skip().Take().DoAnythingYouWant().ToListAsync();

Note: I cannot guarantee that this will translate to efficient SQL or whether it will translate at all. This could even lead to n + 1 queries. If it does, you can start navigating closer GroupIds, and perform a select through a different entity to get articles.

Answer 2

If you want to achieve this :

   {
    "articleId" = xx,
    "name" = "yyy"
    "labels" : [],
    "orders" : []
   }  

then why don't you use DTO?

public class SomeDTO
{
    public int ArticleId { get; set; }
    public string Name { get; set; }
    public List<LabelArticles> Labels { get; set; }
    public List<Orders> Orders { get; set; }
}

var orderArticlesLabels = result.Select(x => new SomeDTO()
{     

     x.Order.OrderArticles.Select(v => new
     {
       ArticleId = v.ArticleId,
       Name = v.Article.Name,
       Labels = v.Article.LabelArticles.Select(k => new
        {
           LabelId= k.LabelId,
           Name= k.Label.Name
         }),             
        Orders = new List<Order>() {new Order() {Id = x.Order.Id,OrderNumber=x.Order.OrderNumber }}
    })
});