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'

Any() return true or false, so if any of the city is "Hartford", it return true, means you filter nothing. Try simplest exact where clause: where t.Manufacturers.City == "Hartford" — shole
– shole, Commented May 9, 2017 at 2:35
Yes, I have tried it and it still returns all Manufacturers. What I am observing is, once condition satisfied, it selects parent and then automatically fetch its children & children's children. I am looking for something which will return (if we look in reverse order) matching children(Manufacturers), then their products and orders where these products added. — Avi Kenjale
– Avi Kenjale, Commented May 9, 2017 at 2:40
@EmrahSüngü I'm pretty sure Manufactures is a collection so it doesn't have a City property to filter on. — juharr
– juharr, Commented May 9, 2017 at 3:33

tafia · Accepted Answer · 2017-05-09 03:33:13Z

5

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();

edited May 9, 2017 at 3:33

answered May 9, 2017 at 2:40

tafia

1,5929 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Avi Kenjale Over a year ago

I am partially agreed to your suggestion. However, I don't want to create new object. I would like to fetch only those orders which has products from those manufacturers from matching cities. I mean if I relate it SQL statement, I can get easily matching records. Also in your suggestion, if I expand 'o' & 'p' those are as is from default list(not filtered).

tafia Over a year ago

@AviKenjale I've edited the post. You cannot expect the Order object to change because you're filtering it. You must either "remove" items or create a new one. This is kind of like byref vs byval

Avi Kenjale · Accepted Answer · 2017-05-09 04:45:38Z

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

answered May 9, 2017 at 4:45

Avi Kenjale

2,8446 gold badges32 silver badges48 bronze badges

Comments

Hasan Emrah Süngü · Accepted Answer · 2017-05-09 03:13:43Z

0

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

edited May 9, 2017 at 3:13

answered May 9, 2017 at 3:03

Hasan Emrah Süngü

3,7011 gold badge17 silver badges38 bronze badges

3 Comments

Hasan Emrah Süngü Over a year ago

@AviKenjale, unfortunately you are wrong. Where does not return a bool. What it does is to filter out elements based on the predicate you supply, in this case where t.Manufacturers.City == "Hartford"

Avi Kenjale Over a year ago

I mean, Where returns collection for matching bool condition.

Hasan Emrah Süngü Over a year ago

@AviKenjale,Yes exactly whereas Any does not that. What any does is to return true or false, if at least one elements matches the condition see my answer for a better understanding or use it as it is, for a working code

shole · Accepted Answer · 2017-05-09 04:07:36Z

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();
        }
    }
}

Collectives™ on Stack Overflow

C# LINQ nested select query

4 Answers 4

2 Comments

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related