Linq query to return filtered data

Question

I have the following data structure (apologies in advance as I'm not sure of the best way to represent the data structure in SO). The following is a list of tables linked (represented by the one to many

1
|
8

relationship.

---------------------
|GlobalListTable:   |
|-------------------|
|Id                 |
|ProductGroupTableId|
|ProductListTypeId  |   
---------------------
         8
         |
         1
---------------------
|ProductGroupTable: |
|-------------------|
|Id                 |
|Name               |
---------------------
         1
         |
         8
---------------------
|ProductTable:      |
|-------------------|
|Id                 |
|Name               |
|ProductGroupTableId|
---------------------
         1
         |
         8
---------------------
|ComponentTable:    |
|-------------------|
|Id                 |
|Name               |
|ProductTableId     |
|ComponentTypeId    |
---------------------

The data in it's simplest form would look like this

GlobalListTable1
   ProductGroupTable
       ProductTable1
          ComponentTable ComponentTypeId1
          ComponentTable ComponentTypeId2
          ComponentTable ComponentTypeId3
          ComponentTable ComponentTypeId4
        ProductTable2
          ComponentTable ComponentTypeId1
          ComponentTable ComponentTypeId3
       ProductTable3   
          ComponentTable ComponentTypeId3
          ComponentTable ComponentTypeId4

What I want to do is to query (in lambda) the data and return the data but filtered by ProductListTypeId and ComponentTypeId

So for example I have for the first (easy) bit

var productListTypeId=1;    
var componentTypeId=4;
var _results=this.Context.GlobalListTable
.Where(i=>i.ProductListTypeId==productListTypeId);

I have tried adding

.Where(i=>i.ProductGroupTable.ProductTable.ComponentTable.ComponentTypeId == componentTypeId);

But that doesn't seem to work.

I would like to pass in (say) the above parameters and have the following returned:

GlobalListTable1
   ProductGroupTable
       ProductTable1
          ComponentTable4
       ProductTable3   
          ComponentTable4

Edit: Using EntityFramework to retrieve the data

Edit: I'm beginning to think this isn't possible with standard linq queries. The only way I seem to be able to get this working is to iterate through the query and manually remove the unwanted records.

Answer 1

This is how I ended up solving the problem.

It doesn't look like I can achieve it using linq alone so I need to iterate through the results and eliminate the unwanted.

My query to get the top level objects:

var query = this.Context.GlobalListTable
        .Where(i => i.ProductListTypeId == productListTypeId)
        .Select(i => i.ProductGroupTable)
        .SelectMany(i => i.ComponentTables)
        .Where(t => t.ComponentTypeId == componentTypeId)           
        .ToList();

Iterate through the results and exclude the unwanted:

foreach (var _globalListTable in query)
{
    foreach (var _productTable in _globalListTable.ProductGroupTable.ProductTables)
    {
        var _exclude = _productTable.ComponentTables.Where(i => i.ComponentTypeId != componentTypeId);
        _productTable.ComponentTables = _productTable.ComponentTables.Except(_exclude).ToList();
    }
}

return query;

Works perfectly, if not elegantly.

Answer 2

SelectMany'll do the trick, here is the query:

var global = new List<GlobalListTable>()
        {
            new GlobalListTable()
            {
                ProductListTypeId = 1,
                ProductGroupTable = new ProductGroupTable()
                {
                    ProductTables = new List<ProductTable>()
                    {
                        new ProductTable()
                        {
                            ComponentTables = new List<ComponentTable>()
                            {
                                new ComponentTable(){ComponentTypeId = 4, Name = "Sucess"}
                            }
                        }
                    }
                }
            }
        };

        var productListTypeId=1;    
        var componentTypeId=4;
        var query =
            global.Where(t => t.ProductListTypeId == productListTypeId)
                .Select(t => t.ProductGroupTable)
                .SelectMany(t => t.ProductTables)
                .SelectMany(t => t.ComponentTables)
                .Where(t => t.ComponentTypeId == componentTypeId);

EDIT if you need globalListTable, then query'll look like follow:

var query =
            global.Where(t => t.ProductListTypeId == productListTypeId).Where(t1=>t1
                .ProductGroupTable
                .ProductTables
                .SelectMany(t => t.ComponentTables)
                .Any(t => t.ComponentTypeId == componentTypeId));

EDIT2

var filterComp =
                global.Select(t => t.ProductGroupTable)
                    .SelectMany(t => t.ProductTables)
                    .SelectMany(t => t.ComponentTables)
                    .Where(t => t.ComponentTypeId == componentTypeId);

I used the poco classes defined like this:

internal class GlobalListTable
{
    public ProductGroupTable ProductGroupTable { get; set; }
    public int ProductListTypeId { get; set; }
}

internal class ProductGroupTable
{
    public List<ProductTable> ProductTables { get; set; }
}

internal class ProductTable
{
    public List<ComponentTable> ComponentTables { get; set; }
}

internal class ComponentTable
{
    public string Name { get; set; }
    public int ComponentTypeId { get; set; }
}