C# LINQ nested select query

Question

I have a scenario in following nested

--Orders (List) 
 ----Products (List)
 ------Manufacturers (List) 
       FIELDS 
        -Name
        -Address
        -City 

In this scenario, I would need to execute query which will filter on City of Manufacturers and returns Orders, Products & only matching city manufacturers

I tried to put following query, however I am getting all list of Products even though city doesn't match to Manufacturers.

var filteredOrders = from o in Orders
                    from t in o.Products                           
                    where t.Manufacturers.Any(v => v.City == "Hartford")
                    select o;

Or even if I change from select o to 'select t.Manufacturers' I am getting all list of Manufacturers irrespective of city filter.

Luckily I got W3school SQL sample which matches to my scenario. https://www.w3schools.com/sql/trysql.asp?filename=trysql_op_or

SQL Query:

SELECT o.OrderId, p.ProductName, s.* 
FROM [Orders] o 
JOIN OrderDetails od ON o.OrderId = od.OrderId AND o.orderId = 10248 
JOIN Products p ON od.ProductId = p.ProductId
JOIN Suppliers s ON p.SupplierId = s.SupplierId and s.City ='Singapore'    

Answer 1

I finally tried to put everything together and got the expected output.

var fp = orders.Select(o =>
            {
                o.products = o.products.Select(p =>
                {
                    p.manufacturers.RemoveAll(m => m.City != "Hartford");
                    return p;
                }).ToList();

                return o;
            });

Please suggest if anyone has better solution

Answer 2

I cannot think of a way which can completely avoid creating new objects, as the parent object's list property cannot be filtered directly. You can make use of the same class though.

Also I use two separate queries in order to create a new list in parent / grandparent object.

I have made a small demo to demonstrate the idea (below has equivalent code): http://ideone.com/MO6M6t

The city I try to select is "tmp" which only under parent p3, which only belongs to grand parent g1, g3

The expected output is:

g1
    p3
        tmp

g3
    p3
        tmp

using System;
using System.Collections.Generic;
using System.Linq;

public class Test
{
    public class GrandParent{
        public List<Parent> parentList{ get; set; }
        public string name{ get; set; }
        public GrandParent(string name){
            this.name = name;
            this.parentList = new List<Parent>();
        }
    }
    public class Parent{
        public List<Child> childList{ get; set;}
        public string name{ get; set; }
        public Parent(string name){
            this.name = name;
            this.childList = new List<Child>();
        }
    }
    public class Child{
        public string city{ get; set;}
        public Child(string city){
            this.city = city;
        }
    }
    public static void Main()
    {
        Child c1 = new Child("ABC"), c2 = new Child("123"), c3 = new Child("tmp");
        Parent p1 = new Parent("p1"), p2 = new Parent("p2"), p3 = new Parent("p3");
        GrandParent g1 = new GrandParent("g1"), g2 = new GrandParent("g2"), g3 = new GrandParent("g3");

        p1.childList.Add(c1); p1.childList.Add(c2); 
        p2.childList.Add(c2); 
        p3.childList.Add(c3);

        g1.parentList.Add(p1); g1.parentList.Add(p2); g1.parentList.Add(p3);
        g2.parentList.Add(p2);
        g3.parentList.Add(p3);

        List<GrandParent> repo = new List<GrandParent>{g1, g2, g3};

        var filteredParents = from g in repo
                              from p in g.parentList
                              where p.childList.Any(c => c.city == "tmp")
                              select new Parent(p.name){
                                 childList = p.childList.Where(c => c.city == "tmp").ToList()
                              };

        var filteredGrandParents = from g in repo
                                   from p in g.parentList
                                   where filteredParents.Any(fp => fp.name == p.name)
                                   select new GrandParent(g.name){
                                       parentList = g.parentList.Where(pp => filteredParents.Any(fp => fp.name == pp.name)).ToList()
                                   };

        foreach(var g in filteredGrandParents){
            Console.WriteLine(g.name);
            foreach(var p in g.parentList){
                Console.WriteLine("\t" + p.name);
                foreach(var c in p.childList){
                    Console.WriteLine("\t\t" + c.city);
                }
            }
            Console.WriteLine();
        }
    }
}

Answer 3

I would flatten everything and then only filter on cities you want:

class Manufacturer
{
    public string Name;
    public string Address;
    public string City;
}

class Product
{
    public Manufacturer[] Manufacturers;
}

class Order
{
    public Product[] Products;
}

static void Main(string[] args)
{
    var cities = new string[] { "a", "b" };
    Order[] orders = null;
    orders.SelectMany(o => o.Products.SelectMany(p => p.Manufacturers.Select(m => new { o, p, m })))
        .Where(g => cities.Contains(g.m.City))
        .ToList();
    }

Alternatively, if you want to return new Orders (because they have a different Products, it MUST point to a newly allocated Object) you could have this instead:

var newOrders = orders.Select(o => new Order()
{
    Products = o.Products
    .Select(p => new Product()
    {
        Manufacturers = p.Manufacturers.Where(m => cities.Contains(m.City)).ToArray()
    })
    .Where(m => m.Manufacturers.Length > 0).ToArray()
}).Where(p => p.Products.Length > 0).ToArray();

Answer 4

You are applying your City filter wrong. It is this line.

where t.Manufacturers.Any(v => v.City == "Hartford")

Any return true, at least one of the manufacturers has City property as "Hartford" so basically your query is something like this

var filteredOrders = from o in Orders
                from t in o.Products                           
                where true//←This is the problem
                select o;

What you need to do is in fact

where t.Manufacturers.City == "Hartford"

I hope this helps

Example:

var cityNames = new List<string> {"New York",
                                  "Atlanta",
                                  "Hartford",
                                  "Chicago"
                                  };
var anyResult = cityNames.Any(x=>x== "Hartford"); //TRUE
var whereResult = cityNames.Where(x => x == "Hartford"); //IEnumerable<string>, in this case only one element