Get Collection (Products) of Objects for each item in a list

Question

I have orders collection that contains products collection.I'm passing some product ids as list to the method.I need to return a list of products matching with any of the id in the input list.

Do i need to use a foreach loop like this? Please advice ..

   public List < ProductOrderData > ListProductsByOrderId(List < Guid > input) {
      List < ProductOrderData > products = new List < ProductOrderData > ();
      foreach(var id in input) {
        var orders = this.Collection.AsQueryable().SelectMany(order => order.Products.Where(product => product.Id == id));
      }

    }

Update:

Update2:

Update 3: I pass product Ids and i need to get list of products from orders that match the product Ids.

Answer 1

I have orders collection that contains products collection.

Okay, so you have something like this:

var ordersWithTheirProducts = ...
    .Select(... => new Order
    {
         // some Order properties:
         Id = ...
         OrderDate = ...
         ClientId = ...

         // Products of this Order:
         Products = ...
            .Select( product => new Product
            {
                // Several Product properties
                Id = product.Id,
                Name = product.Name,
                Price = product.Price,
                ...
            },
    };

I'm passing some product ids as list

IEnumerable<Guid> productIds = ...

I need to return a list of products matching with any of the id in the input list.

So, you want to select all Products that were used in any of your Orders, and if that Product has an Id that is one of the values in ProductIds, then you want it in your result.

First we extract all Products that are used in your Orders, then we'l remove the Duplicates, and finally we keep only those Products that have an Id in your productIds:

var result = Orders.SelectMany(order => order.Products)  // extract all Products
    .Distinct()                                           // remove duplicate products
    .Where(product => productIds.Contains(product.Id))
          // keep only those products with an Id that is in productIds

Answer 2

iterating over the input guids will cause a query per each guid. instead issue a single command like this:

    var products = collection.AsQueryable()
                     .Where(o => o.Products.Any(p => input.Contains(p.Id)))
                     .SelectMany(o => o.Products)
                     .Where(p => input.Contains(p.Id))
                     .ToList();

first shortlist the orders with the first where clause. and then unwind the products with SelectMany. because, unwinding the whole collection is a bad idea. unwinding creates objects in server memory and there's a pipeline memory limit of 100mb in mongodb.

test program:

using MongoDB.Entities;
using MongoDB.Entities.Core;
using System;
using System.Linq;

namespace StackOverFlow
{
    public class OrderInfo : Entity
    {
        public Product[] Products { get; set; }
    }

    public class Product
    {
        public Guid Id { get; set; }
    }

    public static class Program
    {
        private static void Main()
        {
            new DB("test-db");

            var input = new[] { Guid.NewGuid(), Guid.NewGuid() };

            var orders = new[] {
                new OrderInfo {  Products = new[] { new Product { Id = input[0] } } },
                new OrderInfo {  Products = new[] { new Product { Id = input[1] } } }
            };

            orders.Save();

            var products = DB.Queryable<OrderInfo>() // collection.AsQueryable() for official driver
                             .Where(o => o.Products.Any(p => input.Contains(p.Id)))
                             .SelectMany(o => o.Products)
                             .Where(p => input.Contains(p.Id))
                             .ToList();
        }
    }
}